Log Hibernate/JPA SQL Queries in Spring Boot

In this article, we will learn how to log SQL statements in the Spring Boot application for debugging.

Overview

Spring Data JDBC and JPA modules provide the abstraction over the data access layer by writing the boilerplate code and using Hibernate as the default JPA provider. Developers generally write the Repository interfaces and Spring boot JPA auto-generates the SQL statements for CRUD operations for those repositories.

There are times when we encounter internal server/database errors, which require us to debug the data access layer code and SQL statements executed then. We will learn how to print SQL statements in the console (standard output) and application logs for debugging and production support.

(Don’t Use) Print SQL Statements in the Console

The easiest but not recommended way to show the hibernate-generated SQL statements in Console (standard output) is by setting the following property in the property file:-

spring.jpa.show-sql=true

Please note that the above configuration is equivalent to:

spring.jpa.properties.hibernate.show_sql=true

This approach writes SQL queries to the standard output (same as System.out.println). You see these SQL queries in the console when using an IDE during development or in stdout logs generated by the underlying application server e.g. tomcat in /tomcat/logs/catalina.out file

It is not a recommended approach for production-grade applications and we should write the SQL queries in application logs for better log management and debugging.

Write SQL Statements in Application Logs

We can use the following properties to write the SQL statements in the application log file:-

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

The first property logs the hibernate-generated SQL queries and the second property logs the prepared statement parameters with values.

Logs:
-2022-12-11 23:09:39.293 -DEBUG 4620 --- [nio-8080-exec-1] org.hibernate.SQL                        :  insert into user (age, name, id) values (?, ?, ?)
-2022-12-11 23:09:39.307 -TRACE 4620 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      :  binding parameter [1] as [INTEGER] - [36]
-2022-12-11 23:09:39.308 -TRACE 4620 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      :  binding parameter [2] as [VARCHAR] - [ashish]
-2022-12-11 23:09:39.309 -TRACE 4620 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder      :  binding parameter [3] as [BIGINT] - [1]

Pretty Print SQL Queries

You can also pretty-print the SQL queries, which is quite useful to analyze long and complex queries. Use the below property along with the above two properties:-

spring.jpa.properties.hibernate.format_sql=true

The formatted SQL query in the logs looks like this:-

Logs:
-2022-12-11 23:26:10.193 -DEBUG 10272 --- [nio-8080-exec-2] org.hibernate.SQL                        :  
    insert 
    into
        user
        (age, name, id) 
    values
        (?, ?, ?)
-2022-12-11 23:26:10.203 -TRACE 10272 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      :  binding parameter [1] as [INTEGER] - [36]
-2022-12-11 23:26:10.205 -TRACE 10272 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      :  binding parameter [2] as [VARCHAR] - [ashish]
-2022-12-11 23:26:10.207 -TRACE 10272 --- [nio-8080-exec-2] o.h.type.descriptor.sql.BasicBinder      :  binding parameter [3] as [BIGINT] - [1]

Print SQL Queries when using JdbcTemplate

If you are using JdbcTemplate in Spring boot to query database then use the following properties to print the SQL queries:-

logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE

Similar to JPA logging, the first property prints the SQL queries and the second prints the parameters of the prepared statements.

That is all about logging SQL queries. Thanks for reading!