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

Posted on 2021-02-20 Edited on 2024-09-12 In Programming Language , Java , Spring , Spring Boot , Spring Data JPA Views: Word count in article: 5.7k Reading time ≈ 5 mins.

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

# 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

logging:
  level:
    org:
      hibernate: 
        SQL: DEBUG
        type: 
          descriptor:
            BasicBinder: TRACE

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

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

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")

<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:

# 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

# 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):

@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)