Batch Processing and Native SQL

The execution of series of programs is called batch processing. Batch processing is the process of reading data from a persistent store, doing something with the data, and then storing the processed data in the persistent store. It permits the automation and sharing of computer resources and usually run when computer resources are less busy.

We are using flush() and clear() methods of the Session API for the batch insert process and these methods are regularly called to control the size of the first level cache. If you don’t need to interact with second level cache, you need to call session.setCacheMode.(CacheMode.IGNORE).

When you need to upload a large number of records into our database by using hibernate we are using the below code.

Eg:

Session session = SessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
Employee employee = new Employee(.....);
session.save(employee);
}
tx.commit();
session.close();

The prime step for using the batch processing feature is to set hibernate.jdbc.batch_size as batch size to a number either at 20 or 50 depending on object size. This shows the hibernate container that every X rows to be inserted as batch.

Eg: –

Session session = SessionFactory.openSession();
Transaction tx = session.beginTransaction();
for ( int i=0; i<100000; i++ ) {
Employee employee = new Employee(.....);
session.save(employee);
if( i % 50 == 0 ) { // Same as the JDBC batch size
//flush a batch of inserts and release memory:
session.flush();
session.clear();
}
}
tx.commit();
session.close();

Advantage:-

Batch processing helps to resolve the problem of OutOfMemoryException.

Hibernate Native SQL

The native SQL helps to express database queries to utilize database specific features like query hints or the CONNECT keyword in Oracle. Hibernate permits us to specify handwritten SQL, including stored procedures, for all create, update, delete, and load operations. If you want to create a native SQL query from the session with the createSQLQuery() method on the Session interface.

Syntax: –

public SQLQuery createSQLQuery(String sqlString) throws HibernateException

If you wants to pass a string consists of the SQL query to the createSQLQuery() method, you can associate the SQL result with either an existing Hibernate entity, a join, or a scalar result using addEntity(), addJoin(), and addScalar() methods.

The Native SQL supports the following queries. They are,

  1. Scalar queries
  2. Entity queries
  3. Named SQL queries

Scalar queries

This is the most basic SQL query. This query helps to get a list of scalar (values) from one or more tables.

Syntax: –

 String sql = "SELECT first_name, salary FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql);
query.setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP);
List results = query.list();

Entity queries

The entity queries are used to access entity objects as a whole from a native sql query via addEntity().

Syntax: –

String sql = "SELECT * FROM EMPLOYEE";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
List results = query.list();

Named SQL queries

The Named SQL query is used to get entity objects from a native SQL query via addEntity() and using named SQL query.

Syntax: –

String sql = "SELECT * FROM EMPLOYEE WHERE id =:employee_id";
SQLQuery query = session.createSQLQuery(sql);
query.addEntity(Employee.class);
query.setParameter("employee_id", 10);
List results = query.list();

Batch Processing Example:

Let us modify configuration file as to add hibernate.jdbc.batch_size property:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration SYSTEM
"http://www.hibernate.org/dtd/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
   <session-factory>
   <property name="hibernate.dialect">
      org.hibernate.dialect.MySQLDialect
   </property>
   <property name="hibernate.connection.driver_class">
      com.mysql.jdbc.Driver
   </property>

   <!-- Assume students is the database name -->
   <property name="hibernate.connection.url">
      jdbc:mysql://localhost/test
   </property>
   <property name="hibernate.connection.username">
      root
   </property>
   <property name="hibernate.connection.password">
      root123
   </property>
   <property name="hibernate.jdbc.batch_size">
      50
   </property>

   <!-- List of XML mapping files -->
   <mapping resource="Employee.hbm.xml"/>

</session-factory>
</hibernate-configuration>

Consider the following POJO Employee class:

public class Employee {
   private int id;
   private String firstName;
   private String lastName;
   private int salary;  

   public Employee() {}
   public Employee(String fname, String lname, int salary) {
      this.firstName = fname;
      this.lastName = lname;
      this.salary = salary;
   }
   public int getId() {
      return id;
   }
   public void setId( int id ) {
      this.id = id;
   }
   public String getFirstName() {
      return firstName;
   }
   public void setFirstName( String first_name ) {
      this.firstName = first_name;
   }
   public String getLastName() {
      return lastName;
   }
   public void setLastName( String last_name ) {
      this.lastName = last_name;
   }
   public int getSalary() {
      return salary;
   }
   public void setSalary( int salary ) {
      this.salary = salary;
   }
}

Let us create the following EMPLOYEE table to store Employee objects:

create table EMPLOYEE (
   id INT NOT NULL auto_increment,
   first_name VARCHAR(20) default NULL,
   last_name  VARCHAR(20) default NULL,
   salary     INT  default NULL,
   PRIMARY KEY (id)
);

Following will be mapping file to map Employee objects with EMPLOYEE table.

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-mapping PUBLIC
 "-//Hibernate/Hibernate Mapping DTD//EN"
 "http://www.hibernate.org/dtd/hibernate-mapping-3.0.dtd"> 

<hibernate-mapping>
   <class name="Employee" table="EMPLOYEE">
      <meta attribute="class-description">
         This class contains the employee detail.
      </meta>
      <id name="id" type="int" column="id">
         <generator/>
      </id>
      <property name="firstName" column="first_name" type="string"/>
      <property name="lastName" column="last_name" type="string"/>
      <property name="salary" column="salary" type="int"/>
   </class>
</hibernate-mapping>

Finally, we will create our application class with the main() method to run the application where we will use flush() and clear() methods available with Session object so that Hibernate keep writing these records into the database instead of caching them in the memory.

import java.util.*;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class ManageEmployee {
   private static SessionFactory factory;
   public static void main(String[] args) {
      try{
         factory = new Configuration().configure().buildSessionFactory();
      }catch (Throwable ex) {
         System.err.println("Failed to create sessionFactory object." + ex);
         throw new ExceptionInInitializerError(ex);
      }
      ManageEmployee ME = new ManageEmployee();

      /* Add employee records in batches */
      ME.addEmployees( );
   }
   /* Method to create employee records in batches */
   public void addEmployees( ){
      Session session = factory.openSession();
      Transaction tx = null;
      Integer employeeID = null;
      try{
         tx = session.beginTransaction();
         for ( int i=0; i<100000; i++ ) {
            String fname = "First Name " + i;
            String lname = "Last Name " + i;
            Integer salary = i;
            Employee employee = new Employee(fname, lname, salary);
            session.save(employee);
         	if( i % 50 == 0 ) {
               session.flush();
               session.clear();
            }
         }
         tx.commit();
      }catch (HibernateException e) {
         if (tx!=null) tx.rollback();
         e.printStackTrace();
      }finally {
         session.close();
      }
      return ;
   }
}

For the compilation and execution purpose we are using the following steps. First set PATH and CLASSPATH appropriately before proceeding for the compilation and execution.

1.         Create hibernate.cfg.xml configuration file.

2.         Create Employee.hbm.xml mapping file.

3.         Create Employee.java source file and compile it.

4.         Create ManageEmployee.java source file and compile it.

5.         Execute ManageEmployee binary to run the program.