Finding your SQL statements run on the database through JDBC

Note: The article was written back in 2009. I recommend log4jdbc instead of jdbclogger now a days.

Why is it of any interest at all?

The application may not log all the SQL statements it is executing. If you are using frameworks/tools that generate SQL statements, then it can be very handy to find out what the generated statements look like (specially if you looking forward to optimize you database performance using indexes and such).


Of-course, a lot of the frameworks (such as Hibernate, iBatis) out there do allow you to configure their logging so that you can see what SQLs are being generated. However for prepared statements, most of them fail to display the final SQL executed on your database.

Tools:

One interesting tool to get this job done is: JDBC Logger. Its easy to configure specially if you are already using a connection pool.

Configuration:

Follow the steps below to configure your connection pool (Tomcat 6.0) to use JDBC Logger:

• download the binaries and put the jar files in your classpath
• configure jdbc to use JDBC Logger's driver

  <Resource
        name="jdbc/mydb"
        auth="Container"
        type="javax.sql.DataSource"
        ...
        driverClassName="net.sourceforge.jdbclogger.JdbcLoggerDriver"
        url="jdbc:oracle:thin:@localhost:1521:orcl"
     />

• create  jdbclogger.properties in your application server classpath (on Tomcat 6, you can add it to your $CATALINA_HOME/lib directory)
• add your desired jdbc driver configuration into the file created above. Example entry: jdbclogger.driver=oracle.jdbc.driver.OracleDriver
• set logging level for JDBC Logger to DEBUG: log4j.logger.net.sourceforge.jdbclogger=DEBUG

Done. Note that, the amount of SQL statements logged may be a bit too much to have in one log file. In that case, you can configure log4j to let JDBC Logger to log the SQL statements into a separate file.