Hibernate 5 + P6Spy configuration example

Hibernate 5 + P6Spy configuration example

Technologies used:   Java SE 1.8 | Hibernate 5.2.10.Final | P6Spy 3.0.0 | Maven 3.3.9 | MySQL 5.7.12 | Eclipse Neon.3

P6Spy is a framework that enables us to log all sql statements and parameters for java application.  

By using P6Spy with Hibernate, you can log current execution time, total elapsed time, sql statement with bind variable, sql statement executed etc.

In this section, I will show you how to setup P6Spy with Hibernate framework.


Project structure

Review the following maven project structure.

Log4j 2 - JSON configuration example | BORAJI.COM


Jar Dependencies

In pom.xml file of your maven project, add the dependencies below.


   <!-- Mysql Connector -->
   <!-- Hibernate 5.2.9 Final -->
   <!-- P6Spy 3.0.0 -->



P6Spy configuration file

Create spy.properties file under src/main/resources source folder and add the following properties in it.


dateformat=yyyy-MM-dd hh:mm:ss a
#logfile = C:/log/spy.log

For all available options in spy.properites file, refer this link - Common Property File Settings


Hibernate Utility class + P6Spy

To integrate P6Spy with Hibernate, modify the connection URL and driver class. For example –

Change driver class from com.mysql.cj.jdbc.Driver to com.p6spy.engine.spy.P6SpyDriver.
Change connection URL from jdbc:mysql://host/db to jdbc:p6spy:mysql://host/db.

The following HibernateUtil helper class demonstrates how to bootstrap hibernate with P6Spy.


package com.boraji.tutorial.hibernate;

import java.util.HashMap;
import java.util.Map;

import org.hibernate.SessionFactory;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.cfg.Environment;

import com.boraji.tutorial.hibernate.entity.Person;
 * @author imssbora
public class HibernateUtil {

   private static StandardServiceRegistry registry;
   private static SessionFactory sessionFactory;

   public static SessionFactory getSessionFactory() {
      if (sessionFactory == null) {
         try {
            StandardServiceRegistryBuilder registryBuilder = 
                  new StandardServiceRegistryBuilder();

            Map<String, String> settings = new HashMap<>();
            settings.put(Environment.DRIVER, "com.p6spy.engine.spy.P6SpyDriver");
            settings.put(Environment.URL, "jdbc:p6spy:mysql://localhost:3306/BORAJI");
            settings.put(Environment.USER, "root");
            settings.put(Environment.PASS, "admin");
            settings.put(Environment.HBM2DDL_AUTO, "update");

            registry = registryBuilder.build();
            MetadataSources sources = new MetadataSources(registry)
            Metadata metadata = sources.getMetadataBuilder().build();
            sessionFactory = metadata.getSessionFactoryBuilder().build();
         } catch (Exception e) {
            if (registry != null) {
      return sessionFactory;

   public static void shutdown() {
      if (registry != null) {


Entity class

A simple model class with JPA annotations.


package com.boraji.tutorial.hibernate.entity;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;

@Table(name = "PERSONS")
public class Person {
  @Column(name = "ID")
  private Long id;

  @Column(name = "NAME")
  private String name;

  //Getter and Setter


Run Application

Create the MainApp class to test the above P6Spy and Hibernate configuration.


package com.boraji.tutorial.hibernate;

import org.hibernate.Session;
import org.hibernate.Transaction;

import com.boraji.tutorial.hibernate.entity.Person;

 * @author imssbora
public class MainApp {

   public static void main(String[] args) {
      Session session = null;
      Transaction transaction = null;
      try {
         session = HibernateUtil.getSessionFactory().openSession();
         transaction = session.getTransaction();

         Person person=new Person();
         person.setName("Mike Lewis");
      } catch (Exception e) {
         if (transaction != null) {
      } finally {
         if (session != null) {


2017-05-09 10:16:23 PM|0|commit|connection 0||
2017-05-09 10:16:23 PM|100|statement|connection 0|create table hibernate_sequence (next_val bigint) engine=MyISAM|
2017-05-09 10:16:23 PM|3|statement|connection 0|insert into hibernate_sequence values ( 1 )|
2017-05-09 10:16:23 PM|39|statement|connection 0|create table PERSONS (ID bigint not null, NAME varchar(255), primary key (ID)) engine=MyISAM|
2017-05-09 10:16:24 PM|9|statement|connection 1|select next_val as id_val from hibernate_sequence for update|select next_val as id_val from hibernate_sequence for update
2017-05-09 10:16:24 PM|0|statement|connection 1|update hibernate_sequence set next_val= ? where next_val=?|update hibernate_sequence set next_val= 2 where next_val=1
2017-05-09 10:16:24 PM|0|commit|connection 1||
2017-05-09 10:16:24 PM|1|statement|connection 0|insert into PERSONS (NAME, ID) values (?, ?)|insert into PERSONS (NAME, ID) values ('Mike Lewis', 1)
2017-05-09 10:16:24 PM|0|commit|connection 0||


Download Source: