How to show and format SQL in Spring Boot project

Details
Written by  
Last Updated on 29 April 2020   |   Print  Email


When developing Spring Boot applications with Spring Data JPA and Hibernate, you may want to see the generated SQL statements for testing/debugging purpose. By default, SQL statements are not printed unless you change the default logging level to DEBUG in the application.properties file:

logging.level.root=DEBUG

Then you can look for the SQL statements under the logging category org.hibernate.SQL like this:

DEBUG ... org.hibernate.SQL : select product0_.id ... from product product0_ order by ...

However, seeing SQL statements this way is not convenient because you have to wade through a lot of unrelated information. And running Spring Boot applications with debug logging level is very slow as a huge amount of information is printed.

So the best way to see SQL statements generated by a Spring Boot application is setting a couple the following entries in the application.properties file:

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

Then you can see the SQL statements are nicely formatted like this:

Hibernate: 
    select
        product0_.id as id1_0_,
        product0_.brand as brand2_0_,
        product0_.madein as madein3_0_,
        product0_.name as name4_0_,
        product0_.price as price5_0_ 
    from
        product product0_ 
    order by
        product0_.id asc limit ?

And keep the logging level as default (INFO) or WARN so you can focus only on SQL statements.

Watch the following video to see how to show and format SQL in a Spring Boot project in action:

 

Other Spring Boot Tutorials:


About the Author:

is certified Java programmer (SCJP and SCWCD). He began programming with Java back in the days of Java 1.4 and has been passionate about it ever since. You can connect with him on Facebook and watch his Java videos on YouTube.



Add comment