How to get exec/fetch time of a JDBC query?

Question

Tools like the DbVisualizer typically display an exec/fetch time after executing a database query. Is it possible to access such performance metrics after executing a query through JDBC?

ResultSet rs = stmt.executeQuery("SELECT a, b, c FROM TABLE2");
ResultSetMetaData rsmd = rs.getMetaData();

// missing functionality:
float executionTime = rsmd.getExecTime();
float fetchTime = rsmd.getFetchTime();

I did not find any such performance measure functionality in either the ResultSet or ResultSetMetaData classes. Is there no better way than 'manually' measuring the elapsed time using the system clock as discussed for example in How do I calculate the elapsed time of an event in java?

Answer 1

You have to measure the timing. Usually you would time things in milli-seconds with System.currentTimeMillis() or use System.nanoTime() for greater resolution/accuracy.

It would be a waste for it to time this automatically when it is rarely used.

Answer 2

Sormula ORM can log timing information.

description=SELECT id, firstName, lastName, graduationDate FROM Student WHERE id IN (?, ?)
prepare    = 50% 00:00:00.000382423 n=  1 avg=00:00:00.000382423
write      =  7% 00:00:00.000054496 n=  1 avg=00:00:00.000054496
execute    = 25% 00:00:00.000190143 n=  1 avg=00:00:00.000190143
read       = 19% 00:00:00.000144009 n=  2 avg=00:00:00.000072005
total      =100% 00:00:00.000771071

See timing example.

Answer 3

You can use the log4jdbc library to log all JDBC connections, please refer the answer and log4jdbc repository for more information