Overview

Working with object-relation mapping (ORM) tools like Hibernate lets developers operate mostly with object-oriented entities rather than relational tables, SQL, etc. Sounds quite attractive, but only from the first look. When the domain becomes more complex, sooner or later you’ll need to investigate the exact SQL queries being executed. So, let’s figure out how to enable various types of Hibernate logging.

How to enable Hibernate logging

If you want to print log statements with executed SQL queries, set DEBUG logging level for the following class:

logging:
  level:
    org:
      hibernate:
        SQL: DEBUG

The snippet above will configure printing one-line SQL log statements. If you want to have them formatted, the following property has to be set:

spring:
  jpa:
    properties:
      hibernate:
        format_sql: true

2021-02-22 23:38:44.677 DEBUG 126321 --- [  restartedMain] o.h.SQL                                  : 
    insert 
    into
        user_role
        (name, user_id, id) 
    values
        (?, ?, ?)

If you want to print parameter values substituted into SQL queries, set TRACE logging level for the following class:

logging:
  level:
    org:
      hibernate:
        type:
          descriptor:
            sql:
              BasicBinder: TRACE

2021-02-22 23:39:55.886 DEBUG 126660 --- [  restartedMain] o.h.SQL                                  : 
    insert 
    into
        user_role
        (name, user_id, id) 
    values
        (?, ?, ?)
2021-02-22 23:39:55.886 TRACE 126660 --- [  restartedMain] o.h.t.d.s.BasicBinder                    : binding parameter [1] as [VARCHAR] - [CONTENT_AUTHOR]
2021-02-22 23:39:55.887 TRACE 126660 --- [  restartedMain] o.h.t.d.s.BasicBinder                    : binding parameter [2] as [BIGINT] - [3]
2021-02-22 23:39:55.887 TRACE 126660 --- [  restartedMain] o.h.t.d.s.BasicBinder                    : binding parameter [3] as [BIGINT] - [4]

Often we need to import a lot of data into a database. For performance reasons, it can make sense to group INSERT/UPDATE statements into batches. In order to log information about batches, set DEBUG logging level for the following class:

logging:
  level:
    org:
      hibernate:
        engine:
          jdbc:
            batch:
              internal:
                BatchingBatch: DEBUG

If you want to generate additional statistics, the following property has to be set:

spring:
  jpa:
    properties:
      hibernate:
        generate_statistics: true

2021-02-22 23:34:45.307  INFO 125389 --- [       CUSTOM-1] i.StatisticalLoggingSessionEventListener : Session Metrics {
    437354 nanoseconds spent acquiring 1 JDBC connections;
    0 nanoseconds spent releasing 0 JDBC connections;
    42704108 nanoseconds spent preparing 12254 JDBC statements;
    837652122 nanoseconds spent executing 11949 JDBC statements;
    465165641 nanoseconds spent executing 798 JDBC batches;
    0 nanoseconds spent performing 0 L2C puts;
    0 nanoseconds spent performing 0 L2C hits;
    0 nanoseconds spent performing 0 L2C misses;
    11401494850 nanoseconds spent executing 154 flushes (flushing a total of 785805 entities and 11637 collections);
    11801849817 nanoseconds spent executing 153 partial-flushes (flushing a total of 773856 entities and 773856 collections)
}