How to enable Database or SQL Logging in Spring Boot?
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
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
3. So, how does this work?
4. How to log SQL statements in Spring Boot.
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
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.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]
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..
Thanks for this tip, very helpful. Can you also suggest how to enable logging of SQL queries and their values in Spring Data JPA?
ReplyDelete