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

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 statementslog4j.logger.org.hibernate.SQL=debug # Logs the JDBC parameters passed to a querylog4j.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.

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.02013-08-30 18:01:15,085 | binding parameter [2] as [TIMESTAMP] - Fri Aug 30 18:01:15 CEST 20132013-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.foo2013-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] - TODO2013-08-30 18:01:15,087 | binding parameter [8] as [VARCHAR] -2013-08-30 18:01:15,088 | binding parameter [9] as [VARCHAR] - MatchingStep@com.mypackage.foo2013-08-30 18:01:15,088 | binding parameter [10] as [VARCHAR] - PRIVATE2013-08-30 18:01:15,088 | binding parameter [11] as [VARCHAR] - 1.02013-08-30 18:01:15,088 | binding parameter [12] as [VARCHAR] - 322013-08-30 18:01:15,088 | binding parameter [13] as [VARCHAR] - MatchingStep2013-08-30 18:01:15,089 | binding parameter [14] as [VARCHAR] -2013-08-30 18:01:15,089 | binding parameter [15] as [INTEGER] - 02013-08-30 18:01:15,089 | binding parameter [16] as [VARCHAR] - 053c2e65-5d51-4c09-85f3-2281a1024f64

In case of spring boot is being used , just config this :

aplication.yml

logging:  level:    org.hibernate.SQL: DEBUG    org.hibernate.type: TRACE

aplication.properties

logging.level.org.hibernate.SQL=DEBUGlogging.level.org.hibernate.type=TRACE

and nothing more.

Your log will be something like this:

2020-12-07 | DEBUG | o.h.SQL:127 - insert into Employee (id, name, title, id) values (?, ?, ?, ?)2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:64 - binding parameter [1] as [VARCHAR] - [001]2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:64 - binding parameter [2] as [VARCHAR] - [John Smith]2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:52 - binding parameter [3] as [VARCHAR] - [null]2020-12-07 | TRACE | o.h.t.d.s.BasicBinder:64 - binding parameter [4] as [BIGINT] - [1]

HTH