Intercept JDBC Connection

Question

I've a superclass that have a java.sql.Connection. There are a lot of subclasses that uses the connection to execute selects.

I need to intercept all statements executed through the connection, and log the executed SQL.

How can I do it?

Answer 1

Use log4jdbc. It's a proxying JDBC driver that logs all your SQL and passes the SQL and parameters on to the underlying driver. That way your code doesn't even have to be aware of the logging, it's all contained within the third-party library.

The configuration consists mostly of changing your JDBC connection string to use the proxy instead of the real driver, and telling log4jdbc what the real JDBC driver is. Then you pick logging categories in your log4j configuration to tell what kind of things you want to log. You can get the SQL, timing information, connection opening and closing events, and result set contents.

If you want an in-depth explanation of how it works see Jack Shirazi's book Java Performance Tuning. There's an extended example of building a proxying driver in the chapter on JDBC.