How to log slow queries in hibernate

Jun 6, 2021 | - views

Add LOG_QUERIES_SLOWER_THAN_MS to hibernate properties

spring:
  jpa:
    properties:
      hibernate:
        session:
          events:
            log:
              LOG_QUERIES_SLOWER_THAN_MS: 100

Change logging level for org.hibernate.SQL_SLOW

logging:
  level:
    org:
      hibernate:
        SQL_SLOW: info

After this slow queries will be visible in your logs

INFO 7882 --- [nio-8080-exec-1] org.hibernate.SQL_SLOW                   : SlowQuery: 3004 milliseconds. SQL: 'HikariProxyPreparedStatement@87979891 wrapping SELECT 1 as ret FROM (SELECT pg_sleep(3.0)) foo'

[DEBUG] Service with slow query

@Service
@RequiredArgsConstructor
public class MainService {
  private final EntityManagerFactory entityManagerFactory;

  @Transactional
  public String handle(Double sleepTimeout) {
    EntityManager entityManager = entityManagerFactory.createEntityManager();
    EntityTransaction entityTransaction = entityManager.getTransaction();
    try {
      entityTransaction.begin();
      Query
          query = entityManager.createNativeQuery(String.format("SELECT 1 as ret FROM (SELECT pg_sleep(%s)) foo", sleepTimeout));
      query.getResultList();
      return "OK";
    } finally {
      entityTransaction.commit();
      entityManager.close();
    }
  }
}
@RestController
@RequestMapping("/api")
@RequiredArgsConstructor
public class MainController {
  private final MainService mainService;

  @PostMapping
  public String handler(@RequestParam Double sleepTimeout) {
    return mainService.handle(sleepTimeout);
  }
}
POST http://localhost:8080/api?sleepTimeout=0.01

###

POST http://localhost:8080/api?sleepTimeout=3
Example project available at github.com/mrk-andreev/example_hibernate_slow_queries