Hello guys if you are working on a Spring Boot application which loads and save data from database but you are not sure which queries are run on backend and you want to see them then you have come to the right place. Earlier, I have showed you how to set logging level in Spring Boot and In this article we are going to have a look at another very interesting topic from Spring Boot on how to log SQL statements in spring boot app. I know you might be wondering why I’m calling a very boring topic an interesting one? A proper logging is among one of the features that sets intermediate or experienced developers apart from beginners.
Logging helps a developer in many ways; adding proper log to their application helps in quickly troubleshooting the bugs or at least get an idea of, why your application is broken. It also helps in monitoring your app by checking the data because database entries frequently include details like the date a record was created, changed, or removed, as well as the person who carried out each activity.
It is to note here that, software development is undoubtedly a process of iterative failure until you find the ideal solution to make an app function as intended. You may save time while adding the breakpoints and debugging to repair the fault by being able to rapidly review the most recent logs and get a sense of where the error is occurring.
So far we have highlighted the importance of logging and shared our opinions with you. Now it’s time to take a look at how can log SQL statements in our Spring Boot Project.
2 ways to Print SQL Statements in Spring Boot application
Here we mainly discuss two main ways to log SQL statements in spring app. These two methods are
1. Using standard output.
2. Via Loggers.
We will start our understanding using the first method i.e. using standard output.
2. 1 Enable SQL logging in Spring Boot with standard output
This method recognizes as the easiest way to log SQL statements in the spring boot application. All you need to do is to write a few (below-mentioned) lines of code in the application.properties file.
spring.jpa.show-sql=true
To see it in action you need to run an SQL statement. In my case, I will run a simple insert statement on my Stadium table, and the application will show the following results.
Hibernate:
insert into stadium (id, stadiumId, stadiumName, stadiumCapacity) values (?, ?, ?, ?)One can access the above log from
/tomcat/logs/Catalina.out
As we can see from the above-logged query, the parameters are represented with a question mark (?) and their values are kept hidden for security reasons.
With the addition of the below code, one can beautify the logged queries.
spring.jpa.properties.hibernate.format_sql=true
The above statement is helpful in case we have long and complex queries to be run against the database system. The net result after adding the above line will look like this.
Hibernate:
insert
into
stadium
(id, stadiumId, stadiumName, stadiumCapacity)
values
(?, ?, ?, ?)Despite being a very simple and easy way to log the SQL statements, this is not the recommended way, because it throws everything directly to standard output without any optimizations or taking the help of a logging framework.
Furthermore, It doesn’t log the parameters of a prepared statement.
2.2 Using Loggers
The next in our row is to use Loggers API. Like standard output, it also helps to trace down the error that may occur during the execution of SQL statements. Unlike the previous method, this method logs the parameter values of a prepared statement. To configure this method, you need to update the application.properties file as shown below
logging.level.org.hibernate.SQL=DEBUG logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
The first statement logs the SQL queries, whereas the second statement logs the parameters of a prepared statement.
After adding the following two lines you will see the following logs.
2022–12–27 00:28:26.005 DEBUG 2041 — — [io-8080-exec-12] org.hibernate.SQL : insert into stadium (id, stadiumId, stadiumName, stadiumCapacity) values (?, ?, ?, ?)
2022–12–27 00:28:26.039 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [BIGINT] — [1]
2022–12–27 00:28:26.037 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [BIGINT] — [101]
2022–12–27 00:28:26.037 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [VARCHAR] — [Lusail]
2022–12–27 00:28:26.037 TRACE 2041 — — [io-8080-exec-12] o.h.type.descriptor.sql.BasicBinder : binding parameter [4] as [BIGINT] — [88966]How Does SQL logging work in Spring Boot?
If you are wondering where this code for logging is coming from? The answer to this question is Spring/Hibernate classes are responsible for generating the SQL statements.
What to avoid?
While using Logging framework one must avoid writing the below code in application.properties file
spring.jpa.show-sql=true
The issue with the above statement is that it prints SQL statements directly in the console window without filtering them, contrary to a Logging framework.
That's all about how to log SQL statement in Spring Boot application. We finally reach the ending note of our article, I hope this article has helped you understand the value of logging in software development and how SQL statement logging be achieved in our spring boot project.
Logging SQL statements directly in your console window tells you which statements were executed by your application and will ultimately save developers time in debugging the errors that may occur during the development lifecycle.
I appreciate you taking the time. You should now be able to log SQL statements using a spring boot app, I hope this article has taught you the logging skills using Spring Boot. Even if this article didn’t convince you, I still hope you may have found this post to be interesting and full of information.
- 10 Spring MVC annotations Java developers should learn (annotations)
- How to setup request timeout in spring? (timeout tutorial)
- 5 Spring Boot Annotations for full-stack Java developers (tutorial)
- How to fix consider defining bean issue in Spring boot? (solution)
- Top 5 Courses to learn Microservices in Java? (courses)
- How to access values from config file ins Spring? (example)
- Top 5 Books and Courses to learn RESTful Web Service (books)
- How to read file from resource folder in spring? (example)
- 10 Courses to learn Spring Security with OAuth 2 (courses)
- 10 Example of WebClient in Spring? (webclient example)
- 5 Courses to learn Spring Cloud and Microservices (courses)
- Java + Spring Boot + Microservice exam (project)
- 5 Spring Boot Features Every Java Developer Should Know (features)
- 5 Course to Master Spring Boot online (courses)
- 10 Things Java Developer should learn (goals)
- 10 Tools Java Developers use in their day-to-day life (tools)
- 3 ways to change Tomcat port in Spring Boot (tutorial)
- 10 Tips to become a better Java developer (tips)
- 5 courses to learn Spring Boot and Spring Cloud ( courses)
- 10 Advanced Spring Boot Courses for Java Programmers (courses)
- 20 Spring Boot Interview Questions for Java Programmers (questions)
- 3 Best Practices Java Programmers can learn from Spring (best practices)
No comments:
Post a Comment
Feel free to comment, ask questions if you have any doubt.