How to Enable logging of SQL statements in Spring Boot? Hibernate + JPA Example Tutorial

Hello guys, if you are a Java developer developing Spring Boot applications, you may need to have proper knowledge of various kinds of errors and also how to overcome those. For example, most of Java developers use JPA in spring boot to make our work easy when managing databases. If you get an error like an internal server error (504 error), you need to go through your backend system and find the error. In many cases, these errors are caused by running invalid SQL or due to error on database. At that time, you often need to know which SQL statements are executed by Hibernate or JPA, or any other database library you are using and if you don't know how to retrieve those SQL statement then debugging and troubleshooting becomes a painful process as you don't have enough data. That's why its important to know how to enable logging of SQL in Spring Boot application. 

If you log those errors in your backend system, then it will be easy for anyone to find exact errors and make solutions to them. In this article, I will share the configuration and tips on  how to log SQL statements in Spring boot which will help you to log the Hibernate/JPA statements and troubleshoot any database related error on your Hibernate and JPA based Java backend,

How to enable Database or SQL Logging in Spring Boot?

In this Spring Boot tutorial, you will learn many cases to log SQL statements in Spring boot, but we will mainly discuss two methods used in Spring boot for SQL logging.

1. To standard Output

2. Via loggers.

So first, have a look at how to log SQL statements into standard output.

1.  How to Enable SQL Logging in Spring Boot to standard Output

This is the simplest way that you can follow to log SQL statements in Spring boot. Only you have to do is apply the below code in the application.properties file in your Spring app.

 spring.jpa.show-sql=true

This is the simplest way to enable SQL logging in JPA and Spring Boot, and let's see the outcome of the results. After inserting data into your application, the hibernate will show the following result.

To access this, you need to go to your application logs from the /tomcat/logs/Catalina.out.

Hibernate: insert into student (id, studentid, name, grade) values (?, ?, ?, ?)

From the above output, you can only see the parameter used there and not the values we have passed, and those are represented with the '?' mark. While it does provide some information, it's really very useful because in most of the cases its data which caused problem like you try to insert invalid date, or a number in A String column or vice-versa. 

In another case, if you want to beautify or pretty print, you can add the following code in your application.properties file in your Spring application.

 spring.jpa.properties.hibernate.format_sql=true

This way is mostly used in case of having long queries and need indentation to easily read the SQL query. The result of adding a new entity to the application will be indicated in the following way.

    Hibernate:
        insert
        into
            student
                (id, studentid, name, grade)
            values
                (?, ?, ?, ?)

2. Loggers

Another method to track down the errors that happen in SQL statements in Spring boot is using the loggers. Using this method, there is the possibility of tracing down statement parameters. In there also, you only need to update the application.properties file in your spring application with the following code lines.

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

So the logs will be as follows.

2021–10–06 00:28:26.005 DEBUG 2041 — — [io-8080-exec-12] org.hibernate.SQL : insert into program (id, studentid, name, grade) values (?, ?, ?, ?)

2021–10–06 00:28:26.039 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] — [1]

2021–10–06 00:28:26.037 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] — [IT170925480]

2021–10–06 00:28:26.037 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARCHAR] — [Harry Potter]

2021–10–06 00:28:26.037 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [VARCHAR] — [grade 7]

The '?' marks are still there, but the thing here is you see the parameters under that. Unlike the other method, you can see the parameters passed through SQL in this method which makes it little bit more useful. If you like, you can use this method or the standard output method to log SQL statements in spring boot.

3. So, how does this work?

The Spring/hibernate classes already contain the code for logging them. But here, we used the TRACE and DEBUG, respectively, to show the info in our app. With this configuration, we can set those loggers to the required level.

4. How to log SQL statements in Spring Boot.

I have the following properties in the application.properties file. 

spring.datasource.url=...
spring.datasource.username=user
spring.datasource.password=1234
spring.datasource.driver-class-name=net.sourceforge.jtds.jdbc.Driver
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
security.ignored=true
security.basic.enabled=false
logging.level.org.springframework.web=INFO
logging.level.org.hibernate=INFO
logging.file=c:/temp/my-log/app.log

 After you run the following command, 

 cmd>mvn spring-boot:run

In the console, I can see SQL statements, but they don't appear in the app.log file. The file just provides basic spring logs.

What should I do if the log file contains SQL statements?

So in here, you can use the following properties in your application.properties file in order to log sql statements. 

logging.level.org.hibernate.SQL=DEBUG

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

So let's have a problem discussion with how to log SQL statements in Spring Boot.

What you should avoid?

spring.jpa.show-sql=true 

    






    
    
4. Settings to avoid

The problem with show-sql is that the SQL statements are printed in the console, so there is no way to filter them, as you'd normally do with a Logging framework.

5. Using hibernate logging

If you are using Hibernate in your Spring Boot project and Hibernate is responsible for storing and retrieving data from database then you can use following configuration to enable SQL logging. For Example, in your spring boot application configuration file, if you add the following loggers,

<logger name="org.hibernate.SQL" level="debug"/>

Then, Hibernate will print the SQL statements when the JDBC PreparedStatement is created. That's why the statement will be logged using parameter placeholders:
INSERT INTO post (title, version, id) VALUES (?, ?, ?)
If you want to log the bind parameter values, just add the following logger as well:
<logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="trace"/>
Once you set the BasicBinder logger, you will see that the bind parameter values are logged as well:
DEBUG [main]: o.h.SQL - insert into post (title, version, id) values (?, ?, ?)
TRACE [main]: o.h.t.d.s.BasicBinder - binding parameter [1] as [VARCHAR] - 
      [High-Performance Java Persistence, part 1]
TRACE [main]: o.h.t.d.s.BasicBinder - binding parameter [2] as [INTEGER] - [0]
TRACE [main]: o.h.t.d.s.BasicBinder - binding parameter [3] as [BIGINT] - [1]

That's all about how to log SQL statements in Java and Spring Boot application which are using Hibernate or Spring Data JPA. This is a really useful tips if you are working on real world Spring boot application as it can save hours during debugging and troubleshooting any performance of functional issue. By logging the SQL commands in log file you know exactly which commands were executed  and then you can also copy then and run directly on database using tools like SQL Server Management Studio, or Oracle SQL developer. 

Other Java and Spring Tutorial you may like
20+ Spring MVC Interview Questions for Programmers (answer)
13 Spring Boot Actuator Interview questions (boot questions)
10 Advanced Spring Boot Courses for Java developers (courses)
Difference between @RequestParam and @PathVariable in Spring (answer)
Top 7  Courses to learn Microservices in Java (courses)
Difference between @Component, @Service, and @Controller in Spring (answer)
Difference between @Autowired and @Inject in Spring? (answer)
How Spring MVC works internally? (answer)
Spring Data JPA Repository (JpaReposistory example)
How to update an entity using Spring Data JPA? (example)
Spring Data JPA @Query Example (query example)
What is @Conditional annotation in Spring? (conditional example)
Top 5 Frameworks Java Developer Should Know (frameworks)
How to create your first Spring MVC application (tutorial)
Top 5 Courses to Learn and Master Spring Cloud (courses)
How to upload and download file using Spring Boot (Example)
10 Spring MVC annotations Java developer should know (annotations)
How to use @Bean in Spring framework (Example)
5 Courses to Learn Spring Security for Java programmers (courses)
Top 5 Spring Boot Annotations Java Developers should know (read)
Spring Boot + Reactjs example (example)
5 Spring Cloud annotations Java programmer should learn (cloud)
15 Spring Boot Interview Questions for Java Developers (questions)
@SpringBootApplication vs. @EnableAutoConfiguration? (answer)

So in this tutorial, we discussed how to log SQL statements in Spring boot using the standard method and using loggers. Hope you understand this very much and see you in the next tutorial.
Thanks for reading this article so far. If you find this Spring Boot SQL Tutorial  then please share it with your friends and colleagues. If you have any questions or feedback, then please drop a note.

P. S. - If you are a beginner and want to learn the Spring Boot  from scratch and look for some of the best online resources, you can also check out these best Spring Boot Courses. This list contains best Udemy and Pluralsight courses to learn Spring Boot in depth..