Hibernate sql logging with values

As you might know already, Hibernate supports logging for sql-statements. Your Spring-configuration (e.g. spring-database.xml) must contain this:

<bean id="entityManagerFactory"
 class="org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean">
<property name="dataSource" ref="dataSource" />
<property name="persistenceUnitName" value="logging-test" />
<property name="jpaVendorAdapter">
 <bean>
<property name="showSql" value="true" />
<property name="generateDdl" value="true" />
<property name="databasePlatform" value="${jpa.hibernate.dialect}" />
 </bean>
 </property>
<property name="jpaProperties" value="hibernate.dialect=${jpa.hibernate.dialect}" />
 </bean>

With showSql=true hibernate will show you logmessages like this:

Hibernate:
   delete
   from
      SoftwareCategory_SoftwareCategory
   where
      SoftwareCategory_id=?
Hibernate:
   update
      SoftwareCategory
   set
      concurrentVersion=?,
      CATEGORY_NAME=?,
      PARENT_ID=?
   where
      id=?
      and concurrentVersion=?
Hibernate:
   insert
   into
      SoftwareCategory_SoftwareCategory
      (SoftwareCategory_id, POSITION, children_id)
   values
      (?, ?, ?)

To see the values and not the placeholders (?) you must define the loglevel trace for hibernate-types in the log4j.xml:

<category name="org.hibernate.type">
<priority value="trace" />
 </category>

Then you will see logmessages like this:

Hibernate:
   insert
   into
      SoftwareCategory_SoftwareCategory
      (SoftwareCategory_id, POSITION, children_id)
   values
      (?, ?, ?)
NullableType:151 - binding '68' to parameter: 1
NullableType:151 - binding '0' to parameter: 2
NullableType:151 - binding '69' to parameter: 3

Not bad but there is a better way for this🙂

Yesterday i found the project JDBCLogger.  They have implemented a „JDBC-Proxy“ with logging support. Its Maven-ready and comes with a easy Spring-integration. So how can we use this cool stuff … it is so easy:

1. Define the JDBCLogger dependencies in the pom.xml:

<dependencies>
<dependency>
 <groupId>net.sourceforge.jdbclogger</groupId>
 <artifactId>jdbclogger-core</artifactId>
 <version>0.7-SNAPSHOT</version>
 </dependency>
 <dependency>
 <groupId>net.sourceforge.jdbclogger</groupId>
 <artifactId>jdbclogger-spring</artifactId>
 <version>0.7-SNAPSHOT</version>
 </dependency>
 </dependencies>

The informations from the installation-guide on the project-site is a little too old, so the maven-repository http://jdbclogger.sourceforge.net/m2-repo/ doesn’t exists. No problem we can build jdbclogger-core and jdbclogger-spring by ourself (read more). The simplest way to build the two artifacts is:

• checkout from  svn
• edit master-pom, comment out all modules, leave only core and spring active
• run mvn clean install from root-dir

2. Change your Spring-configuration, switch showSql off and add the following bean:

<bean id="jdbcLoggerConfig"
 class="net.sourceforge.jdbclogger.spring.JdbcLoggerBeanPostProcessor">
<property name="enabled" value="true" />
<property name="dataSourceConfigurations">
	<list>
 <bean class="net.sourceforge.jdbclogger.spring.JdbcLoggerDataSourceConfiguration">
<property name="dataSourceBeanName" value="dataSource" />
<property name="driverClassNamePropertyName" value="driverClassName" />
 </bean>
 </list>
 </property>
<property name="targetDriverClassNames">
	<list>
 <value>${jpa.driver}</value>
 </list>
 </property>
 </bean>

3.  Activate logging in the log4j.xml:

<category name="net.sourceforge.jdbclogger">
<priority value="debug" />
 </category>
<category name="net.sourceforge.jdbclogger.spring">
<priority value="error" />
 </category>

Thats it🙂 Now you will see logmessages like this:

JdbcLoggerDriver:65 – Wrapper ’net.sourceforge.jdbclogger.JdbcLoggerDriver‘ successfully registed for driver ‚com.mysql.jdbc.Driver‘
PreparedStatementWrapper:156 – Prepared Statement : select softwareca0_.id as id0_, softwareca0_.concurrentVersion as concurre2_0_, softwareca0_.CATEGORY_NAME as CATEGORY3_0_, softwareca0_.PARENT_ID as PARENT4_0_ from SoftwareCategory softwareca0_ where softwareca0_.PARENT_ID is null
StatementWrapper:45 – Statement : delete from softwarecategory_softwareobject
PreparedStatementWrapper:156 – Prepared Statement : insert into SoftwareCategory_SoftwareCategory (SoftwareCategory_id, POSITION, children_id) values (’92‘, ‚3‘, ’96‘)