How To Debug Hibernate SQL Queries With Parameters

I bet everyone knows how to enable SQL logging for Hibernate. If you add this parametr to Hibernate configuration:

<property name="hibernate.show_sql">true</property>

you will see queries like this in log file:

select roles0_.EntityKey as Entity9_1_, roles0_.ENTITYKEY as ENTITY9_168_0_
from USERROLE roles0_
where roles0_.EntityKey=?

but wait: what value is passed as parameter by Hibernate? (parameters are marked as "?" in JDBC) You have configure TRACE level for some log categories, here's example for simplelog.properties:

org.apache.commons.logging.simplelog.log.org.hibernate.SQL=trace
org.apache.commons.logging.simplelog.log.org.hibernate.engine.query=trace
org.apache.commons.logging.simplelog.log.org.hibernate.type=trace
org.apache.commons.logging.simplelog.log.org.hibernate.jdbc=trace

For your convenience here's log4j configuration:

log4j.logger.org.hibernate.SQL=trace
log4j.logger.org.hibernate.engine.query=trace
log4j.logger.org.hibernate.type=trace
log4j.logger.org.hibernate.jdbc=trace

Then you will see all passed parameters and results in logs:

[DEBUG] (generated SQL here with parameter placeholders)
[TRACE] preparing statement
[TRACE] binding '1002' to parameter: 1
[TRACE] binding '1002' to parameter: 2
[TRACE] binding '1002' to parameter: 3
[DEBUG] about to open ResultSet (open ResultSets: 0, globally: 0)
[DEBUG] about to close ResultSet (open ResultSets: 1, globally: 1)
[DEBUG] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
[TRACE] closing statement

Nothing hidden here. Happy debugging!

This entry was posted in en and tagged , , , . Bookmark the permalink.

5 Responses to How To Debug Hibernate SQL Queries With Parameters

  1. Anonymous says:

    Thank you… much appreciated!

  2. Anonymous says:

    It appears to work fine in my Apache Tomcat but not in WebSphere

  3. Tien Thai says:

    It appears to work fine with Apache Tomcat but not in Websphere 7. Does anyone have any solution to this issue?

  4. dariusz.cieslak says:

    You have to ensure you are using proper log4j.properties file. Probably you have another log4j.properties file under Webshpere that is selected by classpath settings.

  5. Evan says:

    Oh, man, this is really cool. Actually the important thing we need is not the sql, is the parameter. Because we can find the sql in DAO, but the parameter? It's not that easy to dig out. But now we can dig it so easily. 🙂

Comments are closed.