Hibernate 5 - datasource-proxy configuration example

Hibernate 5 - datasource-proxy configuration example

Technologies used:   Java SE 1.8 | Hibernate 5.2.10.Final | datasource-proxy 1.4.1 | Maven 3.3.9 | MySQL 5.7.12 | Eclipse Neon.3

We can use datasource-proxy API with Hibernate application for query and parameter logging.  By using this API, you can log query execution time, parameters, number of select/update/delete/insert queries, type of sql statement (Prepared or Statement), batch size etc.

The datasource-proxy API also allows us to performing certain tasks before/after query execution.

Let’s write a simple hibernate application to see how we can log sql queries and parameters with datasource-proxy.

 

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>
   
   <!-- datasource-proxy -->
   <dependency>
     <groupId>net.ttddyy</groupId>
     <artifactId>datasource-proxy</artifactId>
     <version>1.4.1</version>
   </dependency>
   
   <!-- commons-logging for query metrics. -->
   <dependency>
     <groupId>commons-logging</groupId>
     <artifactId>commons-logging</artifactId>
     <version>1.2</version>
   </dependency>
</dependencies>

 

Hibernate Utility class + ProxyDataSource

To integrate datasource-proxy with Hibernate, you need to create a ProxyDataSource object.

ProxyDataSource dataSource = ProxyDataSourceBuilder.create(actualDataSource)
            .logQueryByCommons(CommonsLogLevel.INFO)
            .countQuery()
            .multiline()
            .build();

Then, set the ProxyDataSource object in hibernate setting values.

Map<String, Object> settings = new HashMap<>();
settings.put(Environment.DATASOURCE, getDataSource());
//...
registryBuilder.applySettings(settings);

Here is the complete example of HibernateUitl helper class to bootstrap application.

HibernateUtil.java

package com.boraji.tutorial.hibernate;

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

import javax.sql.DataSource;

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;
import com.mysql.cj.jdbc.MysqlDataSource;

import net.ttddyy.dsproxy.ExecutionInfo;
import net.ttddyy.dsproxy.QueryInfo;
import net.ttddyy.dsproxy.listener.QueryExecutionListener;
import net.ttddyy.dsproxy.listener.logging.CommonsLogLevel;
import net.ttddyy.dsproxy.support.ProxyDataSource;
import net.ttddyy.dsproxy.support.ProxyDataSourceBuilder;

/**
 * @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, Object> settings = new HashMap<>();
            settings.put(Environment.DATASOURCE, getDataSource());
            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);
      }
   }

   private static DataSource getDataSource() {

      // Create DataSource
      MysqlDataSource ds = new MysqlDataSource();
      ds.setURL("jdbc:mysql://localhost:3306/BORAJI");
      ds.setUser("root");
      ds.setPassword("admin");

      // Create ProxyDataSource
      ProxyDataSource dataSource = ProxyDataSourceBuilder.create(ds)
            .logQueryByCommons(CommonsLogLevel.INFO)
         // .logQueryToSysOut()
            .countQuery()
            .multiline()
            .listener(new QueryExecutionListener() {
               @Override
               public void beforeQuery(ExecutionInfo info, List<QueryInfo> queryInfos) {
                  System.out.println("Before Query Execution");
               }
               @Override
               public void afterQuery(ExecutionInfo info, List<QueryInfo> queryInfos) {
                  System.out.println("\nAfter Query Execution");
               }
            }).build();

      return dataSource;
   }
}

 

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 datasource-proxy 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("John Walker");
         session.save(person);
         
         transaction.commit();
      } catch (Exception e) {
         if (transaction != null) {
            transaction.rollback();
         }
      } finally {
         if (session != null) {
            session.close();
         }
      }
   }
}

Output

Before Query Execution
May 08, 2017 9:40:13 PM net.ttddyy.dsproxy.listener.logging.CommonsQueryLoggingListener writeLog
INFO: 
Name:, Time:132, Success:True
Type:Statement, Batch:False, QuerySize:1, BatchSize:0
Query:["create table hibernate_sequence (next_val bigint) engine=MyISAM"]
Params:[]

After Query Execution
Before Query Execution
May 08, 2017 9:40:13 PM net.ttddyy.dsproxy.listener.logging.CommonsQueryLoggingListener writeLog
INFO: 
Name:, Time:16, Success:True
Type:Statement, Batch:False, QuerySize:1, BatchSize:0
Query:["insert into hibernate_sequence values ( 1 )"]
Params:[]

After Query Execution
Before Query Execution
May 08, 2017 9:40:13 PM net.ttddyy.dsproxy.listener.logging.CommonsQueryLoggingListener writeLog
INFO: 
Name:, Time:53, Success:True
Type:Statement, Batch:False, QuerySize:1, BatchSize:0
Query:["create table PERSONS (ID bigint not null, NAME varchar(255), primary key (ID)) engine=MyISAM"]
Params:[]

After Query Execution
Before Query Execution
May 08, 2017 9:40:13 PM net.ttddyy.dsproxy.listener.logging.CommonsQueryLoggingListener writeLog
INFO: 
Name:, Time:0, Success:True
Type:Prepared, Batch:False, QuerySize:1, BatchSize:0
Query:["select next_val as id_val from hibernate_sequence for update"]
Params:[()]

After Query Execution
Before Query Execution
May 08, 2017 9:40:13 PM net.ttddyy.dsproxy.listener.logging.CommonsQueryLoggingListener writeLog
INFO: 
Name:, Time:0, Success:True
Type:Prepared, Batch:False, QuerySize:1, BatchSize:0
Query:["update hibernate_sequence set next_val= ? where next_val=?"]
Params:[(2,1)]

After Query Execution
Before Query Execution
May 08, 2017 9:40:13 PM net.ttddyy.dsproxy.listener.logging.CommonsQueryLoggingListener writeLog
INFO: 
Name:, Time:0, Success:True
Type:Prepared, Batch:False, QuerySize:1, BatchSize:0
Query:["insert into PERSONS (NAME, ID) values (?, ?)"]
Params:[(John Walker,1)]

After Query Execution