How To Log Hibernate Queries with Bound Parameters

Issue

Hibernate queries are logged to the jasperserver.log file using the following logger in log4j.properties.

log4j.logger.org.hibernate.SQL=DEBUG

This will log the Hibernate queries in the following manner, with question marks as place holders for bound parameters.

select this_.id as id5_1_, this_.version as version5_1_, this_.uri as uri5_1_, this_.hidden as hidden5_1_, this_.name as name5_1_, this_.label as label5_1_, this_.description as descript7_5_1_, this_.parent_folder as parent8_5_1_, this_.creation_date as creation9_5_1_, this_.update_date as update10_5_1_, parent1_.id as id5_0_, parent1_.version as version5_0_, parent1_.uri as uri5_0_, parent1_.hidden as hidden5_0_, parent1_.name as name5_0_, parent1_.label as label5_0_, parent1_.description as descript7_5_0_, parent1_.parent_folder as parent8_5_0_, parent1_.creation_date as creation9_5_0_, parent1_.update_date as update10_5_0_ from JIResourceFolder this_ inner join JIResourceFolder parent1_ on this_.parent_folder=parent1_.id where parent1_.hidden=? and ((this_.uri like ? or this_.uri like ?) and not this_.uri like ?)

But what if you also want to know the value of the parameters so that you can debug the sql statement?

Resolution

Add the following directives to log4j.properties.

### To log bound hibernate queries with their bound parameters
log4j.logger.org.hibernate.SQL=DEBUG
log4j.logger.org.hibernate.type=TRACE
log4j.appender.hb=org.apache.log4j.ConsoleAppender
log4j.appender.hb.layout=org.apache.log4j.PatternLayout

select this_.id as id5_1_, this_.version as version5_1_, this_.uri as uri5_1_, this_.hidden as hidden5_1_, this_.name as name5_1_, this_.label as label5_1_, this_.description as descript7_5_1_, this_.parent_folder as parent8_5_1_, this_.creation_date as creation9_5_1_, this_.update_date as update10_5_1_, parent1_.id as id5_0_, parent1_.version as version5_0_, parent1_.uri as uri5_0_, parent1_.hidden as hidden5_0_, parent1_.name as name5_0_, parent1_.label as label5_0_, parent1_.description as descript7_5_0_, parent1_.parent_folder as parent8_5_0_, parent1_.creation_date as creation9_5_0_, parent1_.update_date as update10_5_0_ from JIResourceFolder this_ inner join JIResourceFolder parent1_ on this_.parent_folder=parent1_.id where parent1_.hidden=? and ((this_.uri like ? or this_.uri like ?) and not this_.uri like ?)
2014-03-25 15:53:06,127 TRACE BooleanType,localhost-startStop-1:151 - binding 'false' to parameter: 1
2014-03-25 15:53:06,128 TRACE StringType,localhost-startStop-1:151 - binding '/themes/default%' to parameter: 2
2014-03-25 15:53:06,128 TRACE StringType,localhost-startStop-1:151 - binding '/organizations/%/themes/default%' to parameter: 3
2014-03-25 15:53:06,129 TRACE StringType,localhost-startStop-1:151 - binding '%org_template%' to parameter: 4
2014-03-25 15:53:06,132 TRACE LongType,localhost-startStop-1:193 - returning '22' as column: id5_0_
2014-03-25 15:53:06,133 TRACE LongType,localhost-startStop-1:193 - returning '39' as column: id5_1_
2014-03-25 15:53:06,133 TRACE LongType,localhost-startStop-1:193 - returning '39' as column: id5_0_
2014-03-25 15:53:06,134 TRACE LongType,localhost-startStop-1:193 - returning '75' as column: id5_1_
2014-03-25 15:53:06,134 TRACE IntegerType,localhost-startStop-1:193 - returning '0' as column: version5_1_
...