Effective Logging of SQL Statements in Spring Boot Applications

Logging is an essential part of any application development process, providing insights into runtime behavior and helping diagnose issues. In Spring Boot applications that use Hibernate as an ORM tool, logging SQL statements can be particularly useful for understanding how your database interactions are being executed.

Introduction to Logging in Spring Boot

Spring Boot simplifies the configuration of logging by supporting various logging systems such as Logback (default), Log4J2, and others. When using Spring Boot with Hibernate, it’s often necessary to log SQL statements for debugging or performance analysis. This tutorial will guide you through setting up your application to log SQL statements effectively.

Understanding Logging Levels

Before we dive into the configuration details, let’s briefly cover logging levels:

TRACE: Most detailed level of logging; useful for very fine-grained informational events.
DEBUG: Provides insights into the flow of the application; generally used for debugging.
INFO: General information about the system’s operation; suitable for regular logs.
WARN: Indicates potential issues that are not necessarily errors but may require attention.
ERROR: Logs critical issues which might prevent parts of the application from functioning.

For logging SQL statements, we will primarily use DEBUG and TRACE.

Configuring Logging in `application.properties`

To log Hibernate-generated SQL queries to a file, you need to configure your application.properties. Here are the key properties:

Enable Logging for Hibernate SQL:
This logs each generated SQL statement.
```
logging.level.org.hibernate.SQL=DEBUG
```
Log Bind Parameter Values:
If you want to see not just the query but also the values being bound, use:
```
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
```
Enable and Format SQL Output:
These settings ensure that the SQL statements are formatted nicely.
```
spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true
```
Log Hibernate Types:
To log all types of parameters and their values, set:
```
logging.level.org.hibernate.type=trace
```
Specify Log File Location:
Ensure that logs are written to the desired file.
```
logging.file=c:/temp/my-log/app.log
```

Example Configuration

Here is how you can set up your application.properties to log SQL statements effectively:

spring.datasource.url=jdbc:your-database-url
spring.datasource.username=user
spring.datasource.password=1234
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE
logging.level.org.hibernate.type=trace

spring.jpa.properties.hibernate.show_sql=true
spring.jpa.properties.hibernate.format_sql=true

security.ignored=true
security.basic.enabled=false

logging.file=c:/temp/my-log/app.log

Advanced Logging with Datasource Proxy

For more granular control over SQL logging, consider using the datasource-proxy library:

Dependency:
Add datasource-proxy to your pom.xml:

<dependency>
    <groupId>net.ttddyy</groupId>
    <artifactId>datasource-proxy</artifactId>
    <version>1.8</version>
</dependency>

Proxy DataSource Bean:

Configure a proxy DataSource bean in your Spring configuration:

@Bean
public DataSource dataSource(DataSource actualDataSource) {
    SLF4JQueryLoggingListener loggingListener = new SLF4JQueryLoggingListener();
    loggingListener.setQueryLogEntryCreator(new InlineQueryLogEntryCreator());
    return ProxyDataSourceBuilder.create(actualDataSource)
        .name("PROXY_DATASOURCE")
        .listener(loggingListener)
        .build();
}

Configure Logging Level:

Set the logging level for datasource-proxy in your logging configuration (e.g., Logback):
```
<logger name="net.ttddyy.dsproxy.listener" level="debug"/>
```

Conclusion

By configuring these settings, you can effectively log SQL statements and their bind parameter values to a file. This approach aids in understanding how your application interacts with the database and helps in diagnosing performance issues.

Remember, excessive logging can lead to large log files, so adjust your logging levels based on the environment (development vs. production) as needed.