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.
Jar Dependencies
In pom.xml file of your maven project, add the dependencies below.
<dependencies>
<!-- Mysql Connector -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>6.0.5</version>
</dependency>
<!-- Hibernate 5.2.9 Final -->
<dependency>
<groupId>org.hibernate</groupId>
<artifactId>hibernate-core</artifactId>
<version>5.2.10.Final</version>
</dependency>
<!-- P6Spy 3.0.0 -->
<dependency>
<groupId>p6spy</groupId>
<artifactId>p6spy</artifactId>
<version>3.0.0</version>
</dependency>
</dependencies>
P6Spy configuration file
Create spy.properties file under src/main/resources source folder and add the following properties in it.
spy.properties
driverlist=com.mysql.cj.jdbc.Driver
dateformat=yyyy-MM-dd hh:mm:ss a
appender=com.p6spy.engine.spy.appender.StdoutLogger
#appender=com.p6spy.engine.spy.appender.FileLogger
#logfile = C:/log/spy.log
logMessageFormat=com.p6spy.engine.spy.appender.SingleLineFormat
For all available options in spy.properties 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.
HibernateUtil.java
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");
registryBuilder.applySettings(settings);
registry = registryBuilder.build();
MetadataSources sources = new MetadataSources(registry)
.addAnnotatedClass(Person.class);
Metadata metadata = sources.getMetadataBuilder().build();
sessionFactory = metadata.getSessionFactoryBuilder().build();
} catch (Exception e) {
if (registry != null) {
StandardServiceRegistryBuilder.destroy(registry);
}
e.printStackTrace();
}
}
return sessionFactory;
}
public static void shutdown() {
if (registry != null) {
StandardServiceRegistryBuilder.destroy(registry);
}
}
}
Entity class
A simple model class with JPA annotations.
Person.java
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;
@Entity
@Table(name = "PERSONS")
public class Person {
@Id
@GeneratedValue
@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.
MainApp.java
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();
transaction.begin();
Person person=new Person();
person.setName("Mike Lewis");
session.save(person);
transaction.commit();
} catch (Exception e) {
if (transaction != null) {
transaction.rollback();
}
} finally {
if (session != null) {
session.close();
}
}
HibernateUtil.shutdown();
}
}
Output
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||
