spring

How to enable logging Hibernate sql and parameters binding

Today i got a question about How to logging hibernate sql, include parameters binging for that sql statement. In this post i will guide you how to do that using hibernate config and log4j setting.

I will use code on this link (spring4+JPA+Hibernate integration) to demo. We will modify it and make it works.

1.Project Import
First, you need download source code on above link and import to Eclipse Kepler IDE
2.Dependencies
We add following dependency to pom.xml file

<dependency>
	<groupId>log4j</groupId>
	<artifactId>log4j</artifactId>
	<version>1.2.17</version>
</dependency>

3.Log4j and hibernate configuration

Make sure hibernate property hibernate.show_sql must be setting. In this source code we setting on file servlet-context.xml through LocalSessionFactoryBean

<beans:bean id="sessionFactory"
		class="org.springframework.orm.hibernate4.LocalSessionFactoryBean">
		<beans:property name="dataSource" ref="datasource" />
		<beans:property name="annotatedClasses">
			<beans:list>
				<beans:value>com.devjav.domain.Employee</beans:value>
			</beans:list>
		</beans:property>
		<beans:property name="hibernateProperties">
			<beans:props>
				<beans:prop key="hibernate.dialect">org.hibernate.dialect.MySQL5Dialect</beans:prop>
				<beans:prop key="hibernate.show_sql">true</beans:prop>
				<beans:prop key="hibernate.hbm2ddl.auto">update</beans:prop>
			</beans:props>
		</beans:property>
	</beans:bean>

We create file log4j.properties in resource folder and put following content

log4j.rootLogger=DEBUG,stdout
log4j.appender.stdout=org.apache.log4j.ConsoleAppender
log4j.appender.stdout.Target=System.out
log4j.appender.stdout.layout=org.apache.log4j.PatternLayout
log4j.appender.stdout.layout.ConversionPattern=%d{DATE}(%F) %5p %c{1}:%L - %m%n
#hibernate loggin configuration
log4j.logger.org.hibernate.format_sql=debug
log4j.logger.org.hibernate.SQL=debug
log4j.logger.org.hibernate.type.descriptor.sql.BasicBinder=trace
log4j.logger.org.hibernate.type.descriptor.sql.BasicExtractor=error
log4j.logger.org.hibernate.type.CollectionType=error

4.Run application

Run application and insert employee we get log as below

14 May 2014 17:20:00,655(SqlStatementLogger.java) DEBUG SQL:109 - insert into Employee (dob, email, first_name, join_date, last_name, phone) values (?, ?, ?, ?, ?, ?)
Hibernate: insert into Employee (dob, email, first_name, join_date, last_name, phone) values (?, ?, ?, ?, ?, ?)
14 May 2014 17:20:00,662(BasicBinder.java) TRACE BasicBinder:69 - binding parameter [1] as [VARCHAR] - [null]
14 May 2014 17:20:00,662(BasicBinder.java) TRACE BasicBinder:81 - binding parameter [2] as [VARCHAR] - [phamthaithinh@yahoo.com]
14 May 2014 17:20:00,662(BasicBinder.java) TRACE BasicBinder:81 - binding parameter [3] as [VARCHAR] - [dsada]
14 May 2014 17:20:00,664(BasicBinder.java) TRACE BasicBinder:69 - binding parameter [4] as [TIMESTAMP] - [null]
14 May 2014 17:20:00,664(BasicBinder.java) TRACE BasicBinder:81 - binding parameter [5] as [VARCHAR] - [dsadsa]
14 May 2014 17:20:00,664(BasicBinder.java) TRACE BasicBinder:81 - binding parameter [6] as [VARCHAR] - [dsada]

5.Source code
You can download complete source code here

Leave a Reply