How to display real Hibernate SQL query using slf4j

In this tutorial you will find out how to show real SQL statements (with real parameter values instead of question marks), that are send to database. This will greatly help us to better debug our code.

Hibernate configuration allows us to show SQL statements in logs by setting the parameter:

hibernate.show_sql=true

But then generated sql in the logs looks like:

insert into User (id, username, version) values (default, ?, ?)

If the query has the parameters, there will be question marks instead of the real parameters. To get info about the parameters you should use slf4j:

Add maven dependency:

<!-- slf4j-log4j -->
 <dependency>
     <groupId>org.slf4j</groupId>
     <artifactId>slf4j-log4j12</artifactId>
     <version>1.6.1</version>
</dependency>

Then create log4j.properties file in your classpath with the following content:

# Direct log messages to 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{ABSOLUTE} %5p %c{1}:%L - %m%n

# Root logger option
log4j.rootLogger=INFO, stdout

# logs the SQL statements
log4j.logger.org.hibernate.SQL=debug 

# Log all JDBC parameters
log4j.logger.org.hibernate.type=trace

The log4j.logger.org.hibernate.SQL parameter hibernate to log SQL statements. The parameter is equivalent to the hibernate.show_sql.
log4j.logger.org.hibernate.type is responsible for logging JDBC parameters.

Now the log should look like this:

10:58:49,627 DEBUG SQL:109 - insert into User (id, username, version) values (default, ?, ?)

10:58:49,635 TRACE BasicBinder:81 - binding parameter [1] as [VARCHAR] - [username_688]

10:58:49,637 TRACE BasicBinder:81 - binding parameter [2] as [BIGINT] - [0]

The source code can be downloaded from here.

Leave a Reply

Your email address will not be published. Required fields are marked *