Hibernate show-sql logs using log4j2 format

Question

I have configured spring.jpa.show-sql: true

so console logs looks like that:

2024-04-20 16:02:01.031 | http-nio-9001-exec-1 | INFO | org.springframework.web.servlet.DispatcherServlet |  | Initializing Servlet 'dispatcherServlet'
2024-04-20 16:02:01.032 | http-nio-9001-exec-1 | INFO | org.springframework.web.servlet.DispatcherServlet |  | Completed initialization in 1 ms
Hibernate: 
    select
        nextval('id_account_seq')
Hibernate: 
    select
        nextval('id_passenger_seq')
Hibernate: 
    insert 
    into
        account
        (creation_date, date_of_birth, email, first_name, last_name, fk_nationality, phone_number, id_account) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: 
    insert 
    into
        passenger
        (fk_account, date_of_birth, email, first_name, is_primary, last_name, fk_nationality, phone_number, id_passenger) 
    values
        (?, ?, ?, ?, ?, ?, ?, ?, ?)

How to configure logs generated by spring.jpa.show-sql: true (those that start with Hibernate:) to use the same pattern of the first 2 logs, that are using the following yaml configuration for log4j2:

Configuration:

  status: warn
    
  Appenders:
    Console:
      name: CONSOLE
      target: SYSTEM_OUT
      PatternLayout:
        Pattern: "%d{yyyy-MM-dd HH:mm:ss.SSS} | %t | %-5level| %logger{25} | %mdc{id}{12} | %msg%n"
  
  Loggers:
    Root:
      level: INFO
      AppenderRef:
        - ref: CONSOLE

Answer 1

While this is extremely simple, it’s not recommended to use spring.jpa.show-sql: true, out of a test scenario as it directly unloads everything to standard output without any optimizations of a logging framework.

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

You should configure loggers in the properties file:

Hibernate 6:

logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

Hibernate 5:

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

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

By setting these properties, logs will be sent to the configured appender.

Source: https://vladmihalcea.com/the-best-way-to-log-jdbc-statements/