Disclosure: This article may contain affiliate links. When you purchase, we may earn a small commission.

How to log SQL statements in Spring Boot? Example Tutorial

When developing Spring Boot applications, you may need to have proper knowledge of various kinds of errors and also how to overcome those. We 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. So 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, we are going to discuss how to log SQL statements in Spring boot which will help you to log the Hibernate/JPA statements.
In this case, we can rely on many cases to log SQL statements in Spring boot. We will discuss two methods used in Spring boot below.

1. To standard Output

2. Via loggers.

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

1. 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 do it, 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, we can see only the parameter used there and not the values we have passed, and those are represented with the '?' mark.

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. 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.

How to log SQL statements in Spring Boot? Example Tutorial

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

In your 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]


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. Thank you.

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 appliation (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)

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 dpeth..      

No comments :

Post a Comment