How can I log SQL statements in Spring Boot?

java

logging

spring-boot

sql-logging

byAnton Shumikhin·Sep 2, 2024

Enable SQL logging in Spring Boot by adding these properties to application.properties:

spring.jpa.show-sql=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type=TRACE

These configurations send SQL output to the console. The show-sql flag outputs the SQL statements, while DEBUG exposes the statement details, and TRACE shows bind parameter values. This quick setup lets you view the SQL transactions Hibernate performs.

Setting up advanced SQL logging

Spring Boot provides more granular control over SQL statement logging through measured configuration settings and supportive tools.

Adjusting logging properties

You may need to tweak your application.properties or application.yaml further for specific logging resolution:

SQL Formatting:
```
spring.jpa.properties.hibernate.format_sql=true
```
This directive presents SQL logs in a readable format, making it look purr-fectly arranged!
SQL Comments Logging:
```
spring.jpa.properties.hibernate.use_sql_comments=true
```
Enabling this tags along comments in your SQL logs, more comments more insights.
Parameters Values Logging: Using TRACE as logging level for org.hibernate.type does show bind parameters – never lose sight of what matters.
Log File Path Configuration: Set the correct log file path as in logging.file.name=c:/temp/my-log/app.log. Ensure the application has write access.
Performance Considerations: TRACE gives detailed logs, but being a chatterbox might affect performance. So, keep it cosy but crisply defined!

Advanced logging tools integration

For holistic insight into SQL interactions, consider adopting dedicated SQL logging tools like P6Spy or datasource-proxy:

P6Spy:

<dependency>
  <groupId>p6spy</groupId>
  <artifactId>p6spy</artifactId>
  <version>the_latest_one</version>
</dependency>

P6Spy spies on your SQL statements seamlessy – the James Bond of logging.

Datasource-Proxy:

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

Defining a dataSource bean and attaching an SLF4JQueryLoggingListener sets you up for detailed logging – because devil is in the details.

Deeper Control with Logback

When more control is needed over your logging configurations:

Implement Custom Logback Configuration: Create a logback-spring.xml file in your src/main/resources folder to customize the logging configuration specific to your needs.

Troubleshooting Logging Issues

If SQL logging behaves like a mischievous gremlin:

Check Console Output: Confirm logging is properly configured.
Review Logback Configuration: Avoid configuration conflicts, especially when using a custom Logback configuration.
Restart your Application: Always remember, when in doubt, reboot!
Check for Typos: An accidental typo in application.properties or application.yaml can throw you off the trail.

Advanced Logging Techniques

When basic logging seems too elementary:

Implement Datasource Proxies: These provide interceptors for real-time analysis and logging.
Application Startup Output: Review it for logging framework initialization operations.
Log File Monitoring: Use command line tools like tail, grep or Get-Content to monitor your log files live.
Filtering Log Noise: Adjust logging.level to filter out unnecessary logs.

Advanced Configurations - Gearing up for more

When you're thirsty for more than just the basics:

Logback Extras: Use features like <turboFilter> to achieve conditional logging.
Dynamic Adjustment: Modify log levels programmatically during runtime with actuator endpoints.
Include SQL Linting Tools: These can be part of your build process to catch errors before they reach runtime.