How to print a query string with parameter values when using Hibernate

Question

Is it possible in Hibernate to print generated SQL queries with real values instead of question marks?

How would you suggest to print queries with real values if it is not possible with Hibernate API?

Answer 1

You need to enable logging for the the following categories:

• org.hibernate.SQL   - set to debug to log all SQL DML statements as they are executed
• org.hibernate.type - set to trace to log all JDBC parameters

So a log4j configuration could look like:

# logs the SQL statements log4j.logger.org.hibernate.SQL=debug   # Logs the JDBC parameters passed to a query log4j.logger.org.hibernate.type=trace  

The first is equivalent to hibernate.show_sql=true legacy property, the second prints the bound parameters among other things.

Another solution (non hibernate based) would be to use a JDBC proxy driver like P6Spy.

Answer 2

Just for convenience, here is the same configuration example for Logback (SLF4J)

<appender name="SQLROLLINGFILE">  <File>/tmp/sql.log</File>  <rollingPolicy>   <FileNamePattern>logFile.%d{yyyy-MM-dd}.log</FileNamePattern>  </rollingPolicy>  <layout>   <Pattern>%-4date | %msg %n</Pattern>  </layout> </appender>  <logger name="org.hibernate.SQL" additivity="false" >     <level value="DEBUG" />      <appender-ref ref="SQLROLLINGFILE" /> </logger>  <logger name="org.hibernate.type" additivity="false" >  <level value="TRACE" />  <appender-ref ref="SQLROLLINGFILE" /> </logger> 

The output in your sql.log (example) then looks like this:

2013-08-30 18:01:15,083 | update stepprovider set created_at=?, lastupdated_at=?, version=?, bundlelocation=?, category_id=?, customer_id=?, description=?, icon_file_id=?, name=?, shareStatus=?, spversion=?, status=?, title=?, type=?, num_used=? where id=? 2013-08-30 18:01:15,084 | binding parameter [1] as [TIMESTAMP] - 2012-07-11 09:57:32.0 2013-08-30 18:01:15,085 | binding parameter [2] as [TIMESTAMP] - Fri Aug 30 18:01:15 CEST 2013 2013-08-30 18:01:15,086 | binding parameter [3] as [INTEGER] - 2013-08-30 18:01:15,086 | binding parameter [4] as [VARCHAR] - com.mypackage.foo 2013-08-30 18:01:15,087 | binding parameter [5] as [VARCHAR] - 2013-08-30 18:01:15,087 | binding parameter [6] as [VARCHAR] - 2013-08-30 18:01:15,087 | binding parameter [7] as [VARCHAR] - TODO 2013-08-30 18:01:15,087 | binding parameter [8] as [VARCHAR] - 2013-08-30 18:01:15,088 | binding parameter [9] as [VARCHAR] - [email protected] 2013-08-30 18:01:15,088 | binding parameter [10] as [VARCHAR] - PRIVATE 2013-08-30 18:01:15,088 | binding parameter [11] as [VARCHAR] - 1.0 2013-08-30 18:01:15,088 | binding parameter [12] as [VARCHAR] - 32 2013-08-30 18:01:15,088 | binding parameter [13] as [VARCHAR] - MatchingStep 2013-08-30 18:01:15,089 | binding parameter [14] as [VARCHAR] - 2013-08-30 18:01:15,089 | binding parameter [15] as [INTEGER] - 0 2013-08-30 18:01:15,089 | binding parameter [16] as [VARCHAR] - 053c2e65-5d51-4c09-85f3-2281a1024f64