Logging PreparedStatements in Java
I tried log4jdbc and it did the job for me.
SECURITY NOTE: As of today August 2011, the logged results of a log4jdbc prepared statement are NOT SAFE to execute. They can be used for analysis, but should NEVER be fed back into a DBMS.
Example 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 configuration 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 useful.
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 performance.
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.