Logging PreparedStatements in Java

Question

One thing that always been a pain is to log SQL (JDBC) errors when you have a PreparedStatement instead of the query itself.

You always end up with messages like:

2008-10-20 09:19:48,114 ERROR LoggingQueueConsumer-52 [Logger.error:168] Error 
executing SQL: [INSERT INTO private_rooms_bans (room_id, name, user_id, msisdn, 
nickname) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE room_id = ?, name = ?, 
user_id = ?, msisdn = ?, nickname = ?]

Of course I could write a helper method for retrieving the values and parsing/substitute the question marks with real values (and probably will go down that path if I don't get an outcome of this question), but I just wanted to know if this problem was resolved before by someone else and/or if is there any generic logging helper that would do that automagically for me.

Edited after a few answers:

The libraries provided so far seems to be suitable to logging the statements for debugging, which no doubt is useful. However, I am looking to a way of taking a PreparedStatement itself (not some subclass) and logging its SQL statement whenever an error occur. I wouldn't like to deploy a production app with an alternate implementation of PreparedStatement.

I guess what I am looking for an utility class, not a PreparedStatement specialization.

Thanks!

Answer 1

This looks good: http://www.ibm.com/developerworks/java/library/j-loggable.html

Answer 2

we use p6spy to log prepared statements http://www.p6spy.com/

Answer 3

I still can't find a way to log the SQL statement only when an exception is thrown.

All the suggestions so far uses replacements of PreparedStatement class, like a proxy class that logs the SQL statements and dispatches it to the JDBC driver. Those are excellent replacements for debugging, but not for error logging.

Does anyone have other idea on we could log the exact offending SQL statement, whenever an exception is thrown?

Answer 4

1. If you are using MySQL, MySQL Connector's PreparedStatement.toString() does include the bound parameters. Though third-party connection pools may break this.

2. Sub-class PreparedStatement to build up the query string as parameters are added. There's no way to extract the SQL from a PreparedStatement, as it uses a compiled binary form.

LoggedPreparedStatement looks promising, though I haven't tried it.

One advantage of these over a proxy driver that logs all queries is that you can modify the query string before logging it. For example in a PCI environment you might want to mask card numbers.

Answer 5

This is very database-dependent. For example, I understand that some JDBC drivers (e.g. sybase, maybe ms-sql) handle prepared statements by create a temporary stored procedure on the server, and then invoking that procedure with the supplied arguments. So the complete SQL is never actually passed from the client.

As a result, the JDBC API does not expose the information you are after. You may be able to cast your statement objects the internal driver implementation, but probably not - your appserver may well wrap the statements in its own implementation.

I think you may just have to bite the bullet and write your own class which interpolates the arguments into the placeholder SQL. This will be awkward, because you can't ask PreparedStatement for the parameters that have been set, so you'll have to remember them in a helper object, before passing them to the statement.

It seems to me that one of the utility libraries which wrap your driver's implementation objects is the most practical way of doing what you're trying to achieve, but it's going to be unpleasant either way.

Answer 6

I tried log4jdbc ( http://code.google.com/p/log4jdbc/ ) and it did the job for me.

Exemple of log generated by logjdbc :

2010/08/12 16:30:56 jdbc.sqlonly org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105) 8. INSERT INTO A_TABLE (ID_FILE,CODE1,ID_G,ID_SEQUENCE,REF,NAME,BAR,DRINK_ID,AMOUNT,DESCRIPTION,STATUS,CODE2,REJECT_DESCR,ID_CUST_REJ) VALUES (2,'123',1,'2','aa','awe',null,'0123',4317.95,'Rccc','0',null,null,null)

The library is very easy to setup:

---

My conf with HSQLDB :

jdbc.url=jdbc:log4jdbc:hsqldb:mem:sample

With Oracle :

jdbc.url=jdbc:log4jdbc:oracle:thin:@mybdd:1521:smt
jdbc.driverClass=net.sf.log4jdbc.DriverSpy

logback.xml :

<logger name="jdbc.sqlonly" level="DEBUG"/>

Too bad it wasn't on a maven repository, but still usefull. From what I tried, if you set

You will only get the statements in error, however, I don't know if this library has an impact on performances..