When utilizing the iBATIS framework you define your SQL Maps to perform the basic CRUD functionality and additional business logic for an individual item. Very often you will be only updating single objects at a time and this is great, but what happens on those occasions where you find yourself processing hundreds of objects at the same time. The simple answer Batch Processing.
Batch Processing is the execution of a set of SQL statements without any user intervention. In essence it takes a group of commands executes them one at a time and then returns the updated count. Well in most cases some drivers won’t return the actual update count and instead returns 0.
Below is a short tutorial on how to perform Batch Processing in Java utilizing the Spring and iBATIS frameworks. In the example we will define and implement the method processInBatch method that will handle the processing. It assumes you have basic knowledge of each of the frameworks and focuses strictly on how to setup batch processing.
Initial Setup
Lets start by assuming for this example that we have a table in our database called APPLICATION and have defined the following basic SQL MAP which has been shortened for brevity.
<?xml version="1.0" encoding="UTF-8" ?> <!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN" "http://ibatis.apache.org/dtd/sql-map-2.dtd"> <sqlMap namespace="Application"> <!-- applicationMap - Application Object Map --> <resultMap id="applicationMap" class="com.example.model.Application" groupBy="id"> <result property="appId" column="APP_ID" /> <result property="name" column="NAME" /> ... </resultMap> <!-- list --> <select id="Application.list" resultMap="applicationMap" > ... </select> <!-- insert --> <insert id="Application.insert" parameterClass="com.example.model.Application"> ... </insert> <!-- update --> <update id="Application.update" parameterClass="com.example.model.Application"> ... </update> <!-- delete --> <delete id="Application.delete" parameterClass="com.example.model.Application"> ... </delete> </sqlMap>
In addition to created the SQLMap we’ve also create our basic DAO interface ApplicationDAO and implementation ApplicationDAOImpl which extends Spring’s SqlMapClientDaoSupport class.
package com.example.dao; import java.util.List; import com.example.model.Application; public interface ApplicationDAO { List<Application> list(); int update( Application obj ); Integer insert( Application obj ); int delete( Application obj ); }
package com.example.dao.impl; import java.util.List; import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport; import com.example.dao.ApplicationDAO; import com.example.model.Application; public class ApplicationDAOImpl extends SqlMapClientDaoSupport implements ApplicationDAO { @SuppressWarnings("unchecked") public List<Application> list(){ return (List<Application>) getSqlMapClientTemplate().queryForList( "Application.list" ); } public Integer insert( Application obj ){ Integer result = Integer.valueOf(-1); try { result = (Integer) getSqlMapClientTemplate().insert( "Application.insert", obj ); } catch ( Exception e ) { log.error( "Failed to insert Application: " + e.getMessage() ); } return result; } public int update( Application obj ){ return getSqlMapClientTemplate().update( "Application.update", obj ); } public int delete( Application obj ){ return getSqlMapClientTemplate().delete( "Application.delete", obj ); } }
Define the new processInBatch method
The processInBatch method will be responsible for setting up and executing the batch. It will take a list of Applications that are to be updated and perform the update SQL Map operation for each of them. To begin lets define the method stub in the ApplicationDAO interface and ApplicationDAOImpl class
// ApplicationDAO int processInBatch( List<Application> applications );
// ApplicationDAOImpl public int processInBatch( List<Application> applications ) { }
Understanding the Batch Logic
To perform Batch Processing it is important to understand the two main classes that you will be interacting with SqlMapClientCallback and SqlMapExecutor
The SqlMapClientCallback class
The SqlMapClientCallback is a class to be overwritten in line that will perform the actual batch process. In it you define the doInSqlMapClient method. The SqlMapClientCallback object creates and passes the SqlMapExecutor instance to this method for your usage in setting up and executing the batch. The doInSqlMapClient method like the rest of the methods returns an Object so you can return any object you want.
try { Integer updateCount = (Integer) getSqlMapClientTemplate().execute( new SqlMapClientCallback() { public Object doInSqlMapClient( SqlMapExecutor executor ) throws SQLException { } }); return updateCount.intVal(); catch (Exception e ) { return -1; }
Looking at the above base implementation you can see that the general setup is the same for any batch processing you will need to perform the only difference is how you define the doInSqlMapClient method.
Implementing the doInSqlMapClient
The main batch worker is the SqlMapExecutor object. This object has 2 additional methods in addition to the basic insert, update, and execute: startBatch and executeBatch. The startBatch method is used to specify that you are performing the following statements as a batch, instead of executing them in line. Once you have set all your statements the executeBatch method is used to execute all of the statements in a batch and return the update count.
In our example as we are updating all the Applications passed to the processInBatch method we will start the batch, iterate over all applications, for each application call update on the executor for that object, then execute the batch.
executor.startBatch(); Iterator<Application> iter = applications.iterator(); while( iter.hasNext() ) { executor.update( "Application.update", iter.next() ); } return new Integer( executor.executeBatch() );
The complete processInBatch method
public int processInBatch( List<Application> applications ) { try { Integer updateCount = (Integer) getSqlMapClientTemplate().execute( new SqlMapClientCallback() { public Object doInSqlMapClient( SqlMapExecutor executor ) throws SQLException { executor.startBatch(); Iterator<Application> iter = applications.iterator(); while( iter.hasNext() ) { executor.update( "Application.update", iter.next() ); } return new Integer( executor.executeBatch() ); } }); return updateCount.intVal(); catch (Exception e ) { return -1; } }
Wrapping it all Up
There you have it we’ve created a method that executes multiple SQL statements as a batch. This reduces the overhead of performing multiple statements individually and can increase the performance of your applications especially as you get to large sets of statements. The greatest thing is that you don’t need to define any new sql statements and can use the ones you’ve already defined for doing single modifications.