How to debug SQL statements in Spring Boot applications

Spring boot auto-configuration does not require a dedicated logging configuration file. So, in order to debug your SQL Statements in Spring Boot applications, by default can use the following settings in your application.properties file:

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

When this configuration is added, you will be able to see:

1) Each SQL Statement executed

2) The timing information so that you can find slow SQL queries

3) The single parameters used to bind your Prepared Statements

On the other hand, if you are using a YAML file to configure Spring Boot, the following configuration will do:

spring:
  jpa:
    properties:
      hibernate:
        show_sql: true
        format_sql: true
logging:
  level:
    org:
      hibernate:
        type: trace

The default logging is good enough for most scenarios. But sometimes in enterprise applications, we need more fine control over logging with other complex requirements. In that case, having a dedicated logging configuration is suitable.

Spring boot by default uses logback, so to customize it’s behavior, all we need to add only logback.xml in classpath and define customization over the file.

Within the XML configuration file, you can add the following appender to debug SQL Statements:

<logger name="org.hibernate.SQL" level="trace" additivity="false">
    <appender-ref ref="file" />
</logger>

To get bind variables as well:

<logger name="org.hibernate.type.descriptor.sql" level="trace">
    <appender-ref ref="file" />
</logger>

In conclusion, we have covered several ways to debug / log SQL statements in Spring Boot applications.

Found the article helpful? if so please follow us on Socials
Twitter Icon       Facebook Icon       LinkedIn Icon       Mastodon Icon