JDBC - SQL statements execution time logging

Question

I need to log the SQL execution times in my Java EE application (Any further statistics would be an optional bonus).

Things are setup in a more-less standard way: Datasource on Application server serving pooled JDBC connections.

Application uses for DB access mix of:

Hibernate and
Spring JDBCTemplate

It runs on:

Glassfish OSE and
Oracle DBS

I know about: Anything better than P6Spy? however the question/answers are outdated, from my point of view.

What I've found so far:

I could go for the pure Hibernate approach (hibernate show query execution time)
- but it's not feasible due to mixed DB access in my case
I could use some of the custom JDBC drivers
- p6spy - however project seems couple years dead (last commit 3 years ago: http://sourceforge.net/p/p6spy/code/23/tree/trunk/)
- log4jdbc - however no release for more than 1 year, and source activity seems to be cca 6 months not touched (http://code.google.com/p/log4jdbc/source/list)
- another 2: log4jdbc-log4j2 and log4jdbc-remix - which seem alive, but I'm not sure about stability and broad usage

Recommendations based on experiences are very welcome.

Please note, I'm interested in kind of answers like: We're using XYZ and this is our experience, rather than I googled just now and feel like...

score 2 · Answer 1 · edited Aug 19 '13 at 08:26

If you want dead accurate SQL execution time then best option is sql trace. But you want to put it in your Java EE application, so obviously want a somewhat accurate execution time.

Following are the things i would suggest [I have implemented them in my code]:

If you just want it for loggin purpose then have appropriate Log4j debug messages which wil l print time along with log entry.
I implemented a BatchLog table for my application which use to record start and end time of the operation. So in your case it will be start and end time of your query. Now if it is just a single query then probably triggers might help here or else you can update log table just before and after running query. Or even better will be a stored procedure which can take care of whole thing and give more accurate data.

score 1 · Answer 2 · answered Aug 16 '13 at 10:18

1

Measuring time and logging it seems like a job for AOP. If you are using EJB, a simple interceptor should solve your problem(for example http://www.javacodegeeks.com/2013/07/java-ee-ejb-interceptors-tutorial-and-example.html). If its Spring(judging from JDBCTemplate), try Aspectj.

answered Aug 16 '13 at 10:18

awb

324
2
9

thanks for idea, I was rather looking for some reusable component, that I could just "plug&play" into my infrastructure. let's see if some other ideas come up – Peter Butkovic Aug 16 '13 at 11:21
well, I checked deeper, but EJB interceptors seem not feasable, I'd like to log SQL execution times, EJB if far higher than that. On the other hand AspectJ might work for me. – Peter Butkovic Aug 22 '13 at 13:39

score 0 · Accepted Answer · answered Aug 30 '13 at 05:26

OK, thanks a lot for your answers. Finally I decided to go for the P6Spy with patches specific to our scenario.

Moreover, as I believe that the gap P6spy is filling still exists these days, I decided to participate in it's development (https://github.com/p6spy/p6spy). Feel free to report your issues or feature requests to: https://github.com/p6spy/p6spy/issues to make it fit your needs.

JDBC - SQL statements execution time logging

3 Answers3