Batch Inserting using Spring JDBC (JdbcTemplate)

In this small example we use the classic JdbcTemplate to complete batch processing with the Oracle JDBC driver. Although we could simply use SimpleJdbcTemplate and write less code this one just seems more natural to me. The code is assuming insertion of new records here.

Table definition

drop table jdbc_batch_table;

create table jdbc_batch_table
(message_id number primary key,
 message_type varchar2(1),
 message varchar2(100))
/

purge recyclebin;

exit;

Spring DAO - JdbcBatchDAOImpl

package pas.au.gemfire.demo.cachewriter.dao.jdbcbatch;

import java.math.BigDecimal;

import java.sql.PreparedStatement;

import java.sql.SQLException;

import java.util.List;

import java.util.logging.Level;
import java.util.logging.Logger;

import javax.sql.DataSource;

import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

public class JdbcBatchDAOImpl implements JdbcBatchDAO
{
  private Logger logger = Logger.getLogger(this.getClass().getSimpleName());
  
  private JdbcTemplate jdbcTemplate;

  public void setDataSource(DataSource dataSource)
  {
    this.jdbcTemplate = new JdbcTemplate(dataSource);
  }
  
  @Override
  public void storeBatch(final List<JdbcBatch> jdbcBatchEntries)
  {

    int[] updateCounts = 
     jdbcTemplate.batchUpdate
      (Constants.INSERT_SQL,
       new BatchPreparedStatementSetter() 
       {
          public void setValues(PreparedStatement ps, int i) throws SQLException 
          {
              ps.setBigDecimal(1, new BigDecimal(jdbcBatchEntries.get(i).getMessageId().toString()));
              ps.setString(2, jdbcBatchEntries.get(i).getMessageType());
              ps.setString(3, jdbcBatchEntries.get(i).getMessage());
          }
          
          public int getBatchSize() 
          {
            return jdbcBatchEntries.size();                  
          }
        }
      );
    
    logger.log (Level.INFO, 
                String.format("Inserted %s records using spring jdbc batching", updateCounts.length));
  }
}

Constants Interface

package pas.au.gemfire.demo.cachewriter.dao.jdbcbatch;

public interface Constants
{
  public final String INSERT_SQL = "insert into jdbc_batch_table values (:1, :2, :3)";
}  

Output showing the insertion of 100 records at a time

Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:43 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:44 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching
Sep 7, 2011 10:03:44 PM pas.au.gemfire.demo.cachewriter.DBGatewayListener processEvents
INFO: Size of DBGatewayListener = 100
Sep 7, 2011 10:03:44 PM pas.au.gemfire.demo.cachewriter.dao.jdbcbatch.JdbcBatchDAOImpl storeBatch
INFO: Inserted 100 records using spring jdbc batching

More info on Spring JDBC can be found here.

http://static.springsource.org/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html