🏠Spring BootShow SQL from Spring Data JPA/hibernate in Spring Boot

Show SQL from Spring Data JPA/hibernate in Spring Boot

Spring Boot

Let’s learn how to configure spring boot to show sql statements and parameters generated by Spring data JPA and hibernate via various approaches.

Overview

If you are using Spring JPA or hibernate for JDBC abstraction, then you may already find it difficult to debug your SQL statements. For this reason you can configure your application to print SQL statements using the following methods.

Show SQL from Spring data JPA

The easiest way to show the hibernate generated SQL into console is to use the following configuration.

spring.jpa.show-sql=true
Code language: Properties (properties)

note that the above configuration is equivalent to spring.jpa.properties.hibernate.show_sql=true. You can also format the printed sql from JPA by setting up the following hibernate property.

spring.jpa.properties.hibernate.format_sql=true
Code language: Properties (properties)

Even though this is way easier compared to other methods, writing logs into stdout is a bad idea. First, it is difficult to manage console logs. Second, writing to stdout directly will reduce your application performance.

Also, with this approach, you can’t see the parameters of the JPA SQL statements.

Show SQL with JPA Parameters

Hibernate prints the SQL and their parameter bindings all along. However they are in DEBUG and TRACE levels. We can instruct the logging framework to show these sql logs using the following configuration.

logging.level.org.hibernate.SQL=DEBUG logging.level.org.hibernate.type=TRACE
Code language: Properties (properties)

Using XML based logging configuration

If your application is using a logback.xml or similar, then you can write the above configuration as shown here.

<?xml version="1.0" encoding="UTF-8"?> <configuration> <include resource="org/springframework/boot/logging/logback/default.xml"/> <include resource="org/springframework/boot/logging/logback/console-appender.xml" /> <root level="INFO"> <appender-ref ref="CONSOLE" /> </root> <logger name="org.hibernate.SQL" level="DEBUG"/> <logger name="org.hibernate.type" level="TRACE"/> </configuration>
Code language: HTML, XML (xml)

Summary

To conclude, we learned how to show spring data JPA generated SQL and their parameters in console output as well as in log files.

If you liked this short article, you may also like the following titles related to spring boot.

You can try out these configurations at this github project.

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *