Executing sql statements as a batch through hibernate

They are two ways to execute sql statements as batch in hibernate, which are
1) Work
2) ReturningWork

This approach is useful when we want to perform batch operations directly on the database instead of indirectly through hibernate.

First we will implement the Work interface as shown below

JdbcWork


class JdbcWork implements Work {
    private int noOfRecords;
    
    public JdbcWork(int noOfRecords) {
        this.noOfRecords = noOfRecords;
    }
    
    @Override
    public void execute(Connection connection) throws SQLException {
        String sql = "insert into series (ID, NAME, DESCRIPTION) values(? ,?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for(int i = 1; i <= 100; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "Series" + i);
            preparedStatement.setString(3, "Description" + i);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        preparedStatement.close();
    }
}

The above implementation of Work interface performs a batch option by inserting 100 records in the database. The connection object is passed to the execute method by the Session object. The number of records to be inserted is passed from the main class as shown below

Main class


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.Work;

public class HibernateDemo6 {
    public static void main(String[] args) {
        SessionFactory sessionFactory = HibernateUtil1.createSessionFactory();
        Session session = sessionFactory.openSession();
        JdbcWork jdbcWork = new JdbcWork(100);
        session.doWork(jdbcWork);
        session.close();
        HibernateUtil1.shutdown();
    }
}

The next code shows an implementation of ReturningWork interface. The difference between ReturningWork and the Work interface is that the former returns a result.

Below is an implementation of ReturningWork interface.

ReturningWork


class ReturningJdbcWork implements ReturningWork {
    private int noOfRecords;
    
    public ReturningJdbcWork(int noOfRecords) {
        this.noOfRecords = noOfRecords;
    }
    
    @Override
    public Integer execute(Connection connection) throws SQLException {
        String sql = "insert into series (ID, NAME, DESCRIPTION) values(? ,?, ?)";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);
        for(int i = 1; i <= 100; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "Series" + i);
            preparedStatement.setString(3, "Description" + i);
            preparedStatement.addBatch();
        }
        int[] results = preparedStatement.executeBatch();
        preparedStatement.close();
        return results.length;
    }
}

Main class


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.ReturningWork;

public class HibernateDemo7 {
    public static void main(String[] args) {
        SessionFactory sessionFactory = HibernateUtil1.createSessionFactory();
        Session session = sessionFactory.openSession();
        ReturningJdbcWork returningJdbcWork = new ReturningJdbcWork(100);
        int count = session.doReturningWork(returningJdbcWork);
        System.out.println("Number of records inserted: " + count);
        session.close();
        HibernateUtil1.shutdown();
    }
}

Output

Number of records inserted: 100

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s