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 know how the generated statements look like. Of-course, a lot of the frameworks 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 that is 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 a file in the root of your application server classpath: jdbclogger.properties
- add your desired jdbc driver which it should wrap. 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.
