Show Hibernate SQL with Parameter Values in Spring Boot

In this article, we will learn to log SQL statements in a Spring boot Data JPA application. We will also learn to print the SQL query information such as parameter values to standard output as well as to a file.

1. Setup

We will use the Person entity to make the database persistence calls.

@Entity
@Table(name = "Persons")
public class Person {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private long id;

    @Column(name = "name")
    private String name;

    @Column(name = "age")
    private int age;
    
    ...
}

And the following code inserts the a Person record in the database:

@RunWith(SpringRunner.class)
@SpringBootTest
public class TestJPAClass {
    
    @Autowired
    PersonRepository personRepository;
   
    @Test
    public void myTest() throws Exception {
        personRepository.save(new Person("Tom", 10));
    }
}

2. Enable SQL Logging using Properties Configuration

In the Spring boot applications, the Spring Data JPA module enables logging of its JPA providers. Since hibernate is the default ORM tool used by Spring Boot, it enables the hibernate logs.

The simplest way to print all SQL logs to a console window is by adding the following property to the application.properties file:

spring.jpa.show-sql=true

When we execute the test, the following SQL statements will be printed to the console.

Hibernate: select next value for hibernate_sequence
Hibernate: insert into persons (age, name, id) values (?, ?, ?)

To make logs more readable, we can beautify or pretty print the SQL statements with format_sql property.

spring.jpa.properties.hibernate.format_sql=true

Now, SQL statements will be formatted for more readability.

Hibernate: 
    select
        next value for hibernate_sequence

Hibernate: 
    insert 
    into
        persons
        (age, name, id) 
    values
        (?, ?, ?)

3. Logging SQL Statements with Parameters

The second option is to allow Hibernate to log directly using the internally written logger statements. It gives us much more control over what and where to output the logs.

The following properties will print the SQL statements and prepared statements with IN and OUT parameter values:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql=TRACE   #For prepared statements

Generated logs will look like this:

Hibernate: select next value for hibernate_sequence
Hibernate: insert into persons (age, name, id) values (?, ?, ?)

2022-12-07 09:30:41.889 TRACE 8004 [main] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [INTEGER] - [10]
2022-12-07 09:30:41.890 TRACE 8004 [main] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [VARCHAR] - [Tom]
2022-12-07 09:30:41.891 TRACE 8004 [main] o.h.type.descriptor.sql.BasicBinder : binding parameter [3] as [BIGINT] - [12]

By default, Spring boot uses logback to write the statements on the console. We can configure a file appender in logback.xml to write the logs in a file.

4. Logging SQL Statements with JdbcTemplate

To log JdbcTemplate generated SQL statements, we need to add the following properties:

logging.level.org.springframework.jdbc.core.JdbcTemplate=DEBUG
logging.level.org.springframework.jdbc.core.StatementCreatorUtils=TRACE

Now SQL statements would be output to the logs. For example, executing the following SELECT statement:

 jdbcTemplate.execute("select * from Persons");

Would be logged:

DEBUG 17612 --- [main] o.s.jdbc.core.JdbcTemplate       : Executing SQL statement [select * from Persons]

5. Conclusion

In this Spring boot article, we learned how to enable Hibernate / JPA logs, show SQL statements with parameter values, and log output to the console as well as a file.

Happy Learning !!