[Spring Data JPA Logging] Logging Java Persistence API (JPA) and Hibernate SQL in Spring Boot

Logging Java Persistence API (JPA) and Hibernate SQL

Spring JDBC and JPA provide abstractions over native JDBC APIs, allowing developers to do away with native SQL queries. However, we often need to see those auto-generated SQL queries and the order in which they were executed for debugging purposes.

To Standard Output

The simplest way to dump the queries to standard out is to add the following to application.properties:

1
spring.jpa.show-sql=true

Or application.yml

1
2
3
spring:
jpa:
show-sql: true

To beautify or pretty print the SQL, we can add:

1
spring.jpa.properties.hibernate.format_sql=true

Or application.yml

1
2
3
4
5
6
7
# application.yml

spring:
jpa:
properties:
hibernate:
format_sql: true

While this is extremely simple, it’s not recommended, as it directly unloads everything to standard output without any optimizations of a logging framework.

1
2
3
Hibernate: select next_val as id_val from hibernate_sequence for update
Hibernate: update hibernate_sequence set next_val= ? where next_val=?
Hibernate: insert into employee (name, id) values (?, ?)

Moreover, it doesn’t log the parameters of prepared statements.

Via Loggers

Now let’s see how we can log the SQL statements by configuring loggers in the properties file application.properties:

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

The first line logs the SQL queries, and the second statement logs the prepared statement parameters.

Or application.yml

1
2
3
4
5
6
7
8
logging:
level:
org:
hibernate:
SQL: DEBUG
type:
descriptor:
BasicBinder: TRACE

The pretty print property will work in this configuration as well.

1
2
3
4
5
2020-11-10 15:40:24.582 DEBUG 92677 --- [nio-8080-exec-1] org.hibernate.SQL                        : select next_val as id_val from hibernate_sequence for update
2020-11-10 15:40:24.592 DEBUG 92677 --- [nio-8080-exec-1] org.hibernate.SQL : update hibernate_sequence set next_val= ? where next_val=?
2020-11-10 15:40:24.608 DEBUG 92677 --- [nio-8080-exec-1] org.hibernate.SQL : insert into employee (name, id) values (?, ?)
2020-11-10 15:40:24.610 TRACE 92677 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [1] as [VARCHAR] - [test]
2020-11-10 15:40:24.611 TRACE 92677 --- [nio-8080-exec-1] o.h.type.descriptor.sql.BasicBinder : binding parameter [2] as [BIGINT] - [1]

By setting these properties, logs will be sent to the configured appender. By default, Spring Boot uses logback with a standard out appender.

Logging JdbcTemplate Queries

To configure statement logging when using JdbcTemplate, we need the following properties:

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

Or application.yml

1
2
3
4
5
6
7
8
logging:
level:
org:
springframework:
jdbc:
core:
JdbcTemplate: DEBUG
StatementCreatorUtils: TRACE

Similar to the JPA logging configuration, the first line is for logging statements and the second is to log parameters of prepared statements.

Via p6spy

P6Spy is a framework that enables database data to be seamlessly intercepted and logged with no code changes to existing application. The P6Spy distribution includes P6Log, an application which logs all JDBC transactions for any Java application.

Spring Boot autoconfiguration is handled by the separate project: gavlyukovskiy/spring-boot-data-source-decorator: Spring Boot integration with p6spy, datasource-proxy, flexy-pool and spring-cloud-sleuth - https://github.com/gavlyukovskiy/spring-boot-data-source-decorator, please consult the respective documentation for usage.

Installation

Install spring-boot-data-source-decorator:

1
implementation("com.github.gavlyukovskiy:p6spy-spring-boot-starter:1.7.1")

Or

1
2
3
4
5
<dependency>
<groupId>com.github.gavlyukovskiy</groupId>
<artifactId>p6spy-spring-boot-starter</artifactId>
<version>1.7.1</version>
</dependency>

Configuration

Update database url and driver class:

1
2
3
4
5
6
7
8
9
10
# application.properties

# spring.datasource.url=jdbc:postgresql://postgres:5432/cloudolife_development
# Update JDBC URL in the datasource with p6spy:
spring.datasource.url=jdbc:p6spy:postgresql://postgres:5432/cloudolife_development
spring.datasource.username=cloudolife
spring.datasource.password=cloudolife

# Change driver in the datasource
spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver

Or application.yml

1
2
3
4
5
6
7
8
9
10
11
12
# application.yml

spring:
datasource:
# url: jdbc:postgresql://postgres:5432/cloudolife_development
# Update JDBC URL in the datasource with p6spy:
url: jdbc:p6spy:postgresql://postgres:5432/cloudolife_development
username: cloudolife
password: cloudolife

# Change driver in the datasource
driver-class-name: com.p6spy.engine.spy.P6SpyDriver

Replace jdbc:p6spy:postgresql to jdbc:p6spy:mysql or others in other databses.

FAQs

How to print SQL parameters of LIMIT and OFFSET in JPA

JPA doesn’t allow to use keywords LIMIT/OFFSET in it. The calls setFirstResult(1) and setMaxResults(3) don’t append OFFSET and LIMIT clause to the query. The first option is to use a native SQL query for your DB, but it will make the code non-portable. Another option is utilization of a Pageable for retrieving results (not sure if it will produce for you the expected log lines either, but you need to check):

1
2
3
4
@Query("SELECT f FROM Foo f ORDER BY f.id DESC") // or ASC
List<Foo> getLastDetails(Pageable pageable);

List<Foo> fooList = getLastDetails(new PageRequest(0,1)); // (int page, int size)

java - How to print SQL parameters of LIMIT and OFFSET - Stack Overflow - https://stackoverflow.com/questions/57336837/how-to-print-sql-parameters-of-limit-and-offset

References

[1] Show Hibernate/JPA SQL Statements in Spring Boot | Baeldung - https://www.baeldung.com/sql-logging-spring-boot

[2] Print Hibernate SQL query string with parameter values in Spring Boot – Yawin Tutor - https://www.yawintutor.com/print-hibernate-sql-query-string-with-parameter-values/

[3] Logging | “How-to” Guides - https://docs.spring.io/spring-boot/docs/current/reference/html/howto.html#howto.logging

[4] Logback Home - http://logback.qos.ch/

[5] gavlyukovskiy/spring-boot-data-source-decorator: Spring Boot integration with p6spy, datasource-proxy, flexy-pool and spring-cloud-sleuth - https://github.com/gavlyukovskiy/spring-boot-data-source-decorator

[6] Spring Boot - https://spring.io/projects/spring-boot