Wednesday, 7 September 2011

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 

2 comments:

Noman Sadiq said...

Hi,Can you please explain how to use sequence in that case..If datasbe table have already sequence then how we mention it with spring jdbcTemplate while inserting the recards..

Pas Apicella said...

Hi,

2 things you would do for that

1. Change this line of code, assuming the first column was a sequence nextval request

public final String INSERT_SQL = "insert into jdbc_batch_table values (:1, :2, :3)"

to

public final String INSERT_SQL = "insert into jdbc_batch_table values (myseq.nextval, :1, :2)"

2. Then fix the code to only pass 2 parameters as follows

public void setValues(PreparedStatement ps, int i) throws SQLException
{
ps.setString(1, jdbcBatchEntries.get(i).getMessageType());
ps.setString(2, jdbcBatchEntries.get(i).getMessage());
}