Logging SQL Statements in Spring Boot

Logging SQL statements in a Spring Boot application is often crucial for debugging, performance monitoring, and auditing. By logging SQL queries, developers can gain insights into the database interactions of their applications and identify any inefficiencies or errors in the SQL operations. Spring Boot offers several approaches to achieve SQL logging, each with its own set of benefits and configurations.

Enabling SQL Logging with Hibernate

Spring Boot uses Hibernate as the default JPA provider. Hibernate provides built-in support for SQL logging that can be enabled by setting specific properties in your application.properties or application.yml file.

Configuration in application.properties

To enable SQL logging with Hibernate in Spring Boot, you can modify your application.properties like so:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true

• spring.jpa.show-sql: This property allows you to log the SQL statements generated by Hibernate.
• spring.jpa.properties.hibernate.format_sql: Formats the SQL output to make it more readable, with line breaks and proper indentation.
• spring.jpa.properties.hibernate.use_sql_comments: Includes comments in the SQL for better context and understanding.

Example Output

When you enable SQL logging and run your application, you will see SQL statements similar to the following example in the console:

1select
2  user0_.id as id1_0_,
3  user0_.name as name2_0_,
4  user0_.email as email3_0_
5from
6  users user0_
7where
8  user0_.id=1

Advanced SQL Logging with DataSource Proxy

For more comprehensive logging, including details such as execution time, you can use tools like DataSource-Proxy. DataSource-Proxy wraps your existing DataSource and intercepts calls to log detailed information.

Adding DataSource-Proxy to Your Project

First, add the DataSource-Proxy library to your pom.xml if you are using Maven:

1<dependency>
2  <groupId>net.ttddyy</groupId>
3  <artifactId>datasource-proxy</artifactId>
4  <version>1.7</version>
5</dependency>

Configuring DataSource-Proxy

Create a configuration class for the proxy:

1import net.ttddyy.dsproxy.listener.SLF4JQueryLoggingListener;
2import net.ttddyy.dsproxy.support.ProxyDataSourceBuilder;
3import org.springframework.context.annotation.Bean;
4import org.springframework.context.annotation.Configuration;
5
6import javax.sql.DataSource;
7
8@Configuration
9public class DataSourceProxyConfig {
10
11    @Bean
12    public DataSource dataSource(DataSource originalDataSource) {
13        SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();
14        loggingListener.setLogLevel(SLF4JQueryLoggingListener.LogLevel.INFO);
15
16        return ProxyDataSourceBuilder
17                .create(originalDataSource)
18                .name("MyDataSource")
19                .listener(loggingListener)
20                .build();
21    }
22}

This configuration intercepts calls to the DataSource and logs SQL statements along with execution specifics.

Example Detailed Log

With DataSource-Proxy set up, you will receive detailed logs, which might include:

22:34:03.123 INFO query - Name:MyDataSource, Time:5ms
    Type:Prepared, Batch:false, Query:["select * from users where id = ?"], Params:[(1)]

SQL Logging at Different Levels

Spring Boot allows you to control the level of SQL logging by using the logging framework's configuration. If you are using SLF4J with Logback, you can configure the verbosity of your SQL logs.

Example Logback Configuration

Add or edit the src/main/resources/logback-spring.xml file to include:

1<configuration>
2    <logger name="org.hibernate.SQL" level="DEBUG"/>
3    <logger name="org.hibernate.type.descriptor.sql.BasicBinder" level="TRACE"/>
4
5    <appender name="STDOUT" class="ch.qos.logback.core.ConsoleAppender">
6        <encoder>
7            <pattern>%d{yyyy-MM-dd HH:mm:ss} - %msg%n</pattern>
8        </encoder>
9    </appender>
10
11    <root level="INFO">
12        <appender-ref ref="STDOUT"/>
13    </root>
14</configuration>

With this configuration:

• org.hibernate.SQL: Logs the SQL statements at the DEBUG level.
• org.hibernate.type.descriptor.sql.BasicBinder: Logs the SQL binding parameters at the TRACE level, providing insight into the values used in SQL statements.

Summary Table

Conclusion

Enabling and configuring SQL logging in a Spring Boot application can greatly improve your visibility into how your application interacts with databases. Depending on your needs, Spring Boot offers basic logging with Hibernate configurations, as well as more advanced solutions using DataSource-Proxy and logging framework configurations. Through these various methods, you can tailor the SQL logging to suit your specific requirements and gain valuable insights into your application's database interactions.