When you’re using Hibernate you can either set the property hibernate.show_sql to true or enable the logger org.hibernate.SQL to display the generated SQL statements. But the generated log output does not include the (named) parameter values or – rather more interesting – the execution time.

This is where jdbcdslog comes in, it enables logging at the JDBC level and has some quite nice features:

  • Simple configuration with Spring as a proxy data source that wraps the actual data source
  • Outputs the execution time
  • Comes with a special logger for “slow” statements with a configurable threshold time
  • “Inlines” the parameter values, hence you can simply copy the outputted SQL into your DB administration tool and execute it manually

Here the few steps necessary to enable a slow statement logger in your application:

Add the maven dependency:

<dependency>
	<groupId>com.googlecode.usc>/groupId>
	<artifactId>jdbcdslog</artifactId>
	<version>1.0.6.2</version>
<dependency>

Use the jdbcdslog proxy datasource in your Spring context:

<bean id="dataSource" class="org.jdbcdslog.ConnectionPoolDataSourceProxy">
    	<property name="targetDSDirect">
    	  <jee:jndi-lookup resource-ref="true" jndi-name="jdbc/myDatasource">
    	</property>
</bean>

Add a property file jdbcdslog.properties to /src/main/resources with the following content:

jdbcdslog.showTime=true
jdbcdslog.logText=true
jdbcdslog.slowQueryThreshold=500</pre> 

With this properties all SQL statements that last longer than 500 ms are outputted to the logger org.jdbcdslog.SlowQueryLogger at info level.

When you’re using logback as logging facility you would enable the slow statements like below:

<logger name="org.jdbcdslog.SlowQueryLogger">
	<level value="info" />
	<appender-ref ref="STDOUT" />
	<appender-ref ref="FILE_SLOW_SQL" />
</logger>

And now you’ll see all slow SQL statements in the console during development. Pretty cool, isn’t it?

Leave a Reply

Your email address will not be published. Required fields are marked *


*