Categories: Spring Boot

Show Hibernate SQL queries with values in Spring Boot

When debugging a hibernate related issue, it is useful to examine queries in the console. Check out how to print them with binding parameters.

To log queries with values as console output, add the following lines to the application-development.properties file:

#awesome-project/src/main/resources/application-development.properties
logging.level.org.hibernate.SQL=debug
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.type.descriptor.sql=trace

Check out the example output given after calling a simple action for creating a new user account:

# console output
2018-12-16 08:30:57.070 DEBUG 9161 --- [nio-8080-exec-1] org.hibernate.SQL: 
    insert 
    into
        user
        (id, password, role, username) 
    values
        (null, ?, ?, ?)
TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder  : binding parameter [1] as [VARCHAR] - [$2a$10$oA1XYAouefHO4EvgDsW3s.JAmqrYJApqESKgFsnlpPJMWTSQx7QqK]
TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder  : binding parameter [2] as [VARCHAR] - [ROLE_USER]
TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder  : binding parameter [3] as [VARCHAR] - [user]

For security and performance reasons print queries only when you need to debug them and avoid doing this on the production environment.

Properties explained

Show only queries

#awesome-project/src/main/resources/application-development.properties
logging.level.org.hibernate.SQL=debug

This property allows us to see only the prepared statement:

# console output
DEBUG 12006 --- org.hibernate.SQL : insert into user (id, password, role, username) values (null, ?, ?, ?)

Format output

To beautify the output and make it more readable we can add the format property:

#awesome-project/src/main/resources/application-development.properties
logging.level.org.hibernate.SQL=debug
spring.jpa.properties.hibernate.format_sql=true

Now the same query is displayed in a more comprehensible way:

# console output
2018-12-16 08:30:57.070 DEBUG 11700 --- [nio-8080-exec-2] org.hibernate.SQL                        : 
    insert 
    into
        user
        (id, password, role, username) 
    values
        (null, ?, ?, ?)

Include binding parameters

To print values we need the following line:

#awesome-project/src/main/resources/application-development.properties
logging.level.org.hibernate.type.descriptor.sql=trace

Finally, the output contains also the binding parameters values:

# console output
TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder  : binding parameter [1] as [VARCHAR] - [$2a$10$oA1XYAouefHO4EvgDsW3s.JAmqrYJApqESKgFsnlpPJMWTSQx7QqK]
TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder  : binding parameter [2] as [VARCHAR] - [ROLE_USER]
TRACE 9161 --- o.h.type.descriptor.sql.BasicBinder  : binding parameter [3] as [VARCHAR] - [user]

Photo by frank mckenna on StockSnap

little_pinecone