The best way to log SQL statements with Spring Boot
source link: https://vladmihalcea.com/log-sql-spring-boot/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
If you are trading Stocks and Crypto using Revolut, then you are going to love RevoGain!
Introduction
In this article, I’m going to show you the best way to log SQL statements when using Spring Boot.
Logging SQL queries is very important, as it allows you to validate the number of generated statements, the syntax of the auto-generated queries, as well as prove that JDBC batching works as expected.
Don’t log SQL statements with the Spring Boot show-sql property
It’s very common for a Spring Boot application to store data in a relational database, and, most often, Spring Data JPA is the default data access technology.
While Spring Data JPA is generic and can work with any JPA provider, in reality, the vast majority of projects using Spring Data JPA happen to use Hibernate as well since Hibernate is the default JPA provider used by Spring Boot.
Always has been! pic.twitter.com/tYCR13wnmH
— Vlad Mihalcea (@vlad_mihalcea) November 21, 2021
To log SQL statements generated by Spring Data JPA, Spring Boot provides the spring.jpa.show-sql
property that you can add to your application.properties
configuration file:
spring.jpa.show-sql=
true
However, this is not a good idea because, behind the scenes, it simply enables the Hibernate hibernate.show_sql
configuration property:
jpaProperties.put(
"hibernate.show_sql"
,
"true"
);
As I explained in this article, you should not use the hibernate.show_sql
configuration property as it’s basically the equivalent of printing your SQL queries using System.out.println
.
For the same reasons we are using logging frameworks and not System.out.println
calls for application logging, we should not use the show_sql
properties provided by Spring Boot or Hibernate.
Hibernate logging limitations
A slightly better option is to enable SQL logging at Hibernate-level by providing the following logger options in your application.properties
configuration file:
logging.level.org.hibernate.SQL=debug
logging.level.org.hibernate.type.descriptor.sql=trace
And, Spring Boot will log SQL statements like this:
org.hibernate.SQL :
SELECT
u.id
AS
id1_7_,
u.email
AS
email5_7_,
u.external_id
AS
external6_7_,
u.first_name
AS
first_na7_7_,
u.last_name
AS
last_nam9_7_,
FROM
users u
WHERE
u.email = ?
o.h.t.d.sql.BasicBinder :
The org.hibernate.SQL
log message is printed when the JDBC PreparedStatement
is created, while the parameter binding log messages are printed when the setParameter
query methods are executed.
This Hibernate-based SQL logging option also has several drawbacks:
- for batching, it will not be clear how many statements are actually sent to the database server since the log message is printed during the prepare phase and not at the moment the
executeBatch
method is called. - the
org.hibernate.type.descriptor.sql
logger only works for the built-in Hibernate Core Types. If you’re using the Hibernate Types project, then the Hibernate package logger setting will not work for non-standard Hibernate Types.
DataSource-Proxy
As I explained in this article, DataSource-Proxy is one of the best ways to log SQL statements.
Since it proxies the JDBC DataSource
, it can log statements for any data access layer technology you might be using with Spring Boot. Even if you mix JPA and jOOQ, DataSource-Proxy will print all your SQL statements that are executed via the JDBC Connection
that was proxied.
Another great advantage of DataSource-Proxy is that you can assert the number statements that are generated by the data access layer. Check out this article for a step-by-step tutorial.
When Spring Boot starts, it creates a DataSource
to be used by JPA, Hibernate, or other data access technologies. In order to use DataSource-Proxy, we need to proxy the default JDBC DataSource
and allow the application to use the Proxy instead.
Luckily, Arthur Gavlyukovskiy saved the day and provided us with a list of Spring Boot DataSource starters that support DataSource-Proxy, FlexyPool, or P6spy.
All you need to do is add the following Maven dependency:
<
dependency
>
<
groupId
>com.github.gavlyukovskiy</
groupId
>
<
artifactId
>datasource-proxy-spring-boot-starter</
artifactId
>
<
version
>${ds-proxy-spring-boot-starter.version}</
version
>
</
dependency
>
And, in your application.properties
configuration file, you need to enable logging for the net.ttddyy.dsproxy.listener
package, as follows:
logging.level.net.ttddyy.dsproxy.listener=debug
And now Spring Boot can print meaningful SQL statements that were generated by any data access technology:
Name
:dataSource,
Connection
:5,
Time
:1, Success:
True
Type:Prepared, Batch:
False
, QuerySize:1, BatchSize:0
Query:[
"
SELECT
u.id AS id1_7_,
u.email AS email5_7_,
u.external_id AS external6_7_,
u.first_name AS first_na7_7_,
u.last_name AS last_nam9_7_,
FROM users u
WHERE u.email = ?
"
]
Params:[(
)]
Cool, right?
If you enjoyed this article, I bet you are going to love my Book and Video Courses as well.
Conclusion
If you’re using Spring Boot with a relational database, then you should definitely have a good way to log SQL statements.
While you might think that the default show-sql
property or the Hibernate logging option is sufficient, they are not worth it. Using DataSource-Proxy is just as easy and, not only it provides better logging options, but you can use it to detect N+1 query issues automatically.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK