Spring JdbcTemplate batch updates and inserts

Q. Why are batch updates faster?
A.

• The query doesnt need to be repeatedly parsed. Parsed only once per batch.
• The values are transmitted in one network round-trip to the server. So, only one remote call.
• The commands can be placed inside a single transaction when run in a transnational context. 

Q. Should batch updates run within a transaction?

A. Yes.  It is important to keep in mind, that each update added to a Statement or PreparedStatement is executed separately by the database. So, to avoid some data succeeding and others failing you need to run them inside a transaction. When executed inside a transaction, either all updates succeed or all fail, leaving the data in a consistent state.

Q. What type of statement is used for the batch updates?

A. PreparedStatement.

Q. How will you perform a batch update using the Spring JdbcTemplate?

A. The example below is used for an update SQL, but it can be used in a similar fashion for inserts and deletes as well. Spring issues multiple update statements on a single PreparedStatement. There are two ways to do this.

Approach 1: 

package com.myapp.dao;

import org.springframework.jdbc.core.JdbcTemplate;

public class TradeDaoImpl implements TradeDao {

    @Resource(name = "jdbcTemplate")
    JdbcTemplate jdbcTemplate;

    @Override
 public int[] updateTradeStatusAndErrorMsg(final List<TradeDetail> tradeDetails) {
  
  final String UPDATE_TRADES_SQL = "UPDATE trade_table SET status=?, error=? "
                 + "  WHERE  trade_id=?"
  
  List<Object[]> updateBatchArgs = getUpdateBatchArgs(tradeDetails);
  
  int[] updateCounts = jdbcTemplate.batchUpdate(UPDATE_TRADES_SQL,updateBatchArgs);
   
 }
 
 
 private List<Object[]> getUpdateBatchArgs(List<TradeDetail> tradeDetails) {
  List<Object[]> updateBatchArgs = new ArrayList<Object[]>();
  for (TradeDetail d : tradeDetails) {
   Object[] updateArgs = new Object[3];
   updateArgs[0] = d.getStatus() != null ? d.getStatus().toString() : "";
   updateArgs[1] = d.getErrorMsg() != null ? d.getErrorMsg() : "";
   updateArgs[2] = d.getTradeId().intValue();
   updateBatchArgs.add(updateArgs);
  }
  
  return updateBatchArgs;
 }
}

Approach 2:

package com.myapp.dao;

import org.springframework.jdbc.core.JdbcTemplate;

public class TradeDaoImpl implements TradeDao {

    @Resource(name = "jdbcTemplate")
    JdbcTemplate jdbcTemplate;

    @Override
 public int[] updateTradeStatusAndErrorMsg(final List<TradeDetail> tradeDetails) {
  
  final String UPDATE_TRADES_SQL = "UPDATE trade_table SET status=?, error=? "
                 + "  WHERE  trade_id=?"
  
  //anonymous inner class is used
  int[] updateCounts = jdbcTemplate.batchUpdate(UPDATE_TRADES_SQL, new BatchPreparedStatementSetter() {

      //more control over the prepeared statement
   @Override
   public void setValues(PreparedStatement ps, int i) throws SQLException {
    TradeDetail d = tradeDetails.get(i);
    ps.setObject(1, d.getStatus() != null ? d.getStatus().toString() : TradeStatusType.ERR.toString(), Types.CHAR);
    ps.setString(2, d.getErrorMsg() != null ? d.getErrorMsg() : "");
    ps.setInt(3,d.getTradeId().intValue());
   }

   @Override
   public int getBatchSize() {
    return tradeDetails.size();
   }
  });

  return updateCounts;
   
 }
}

Related Posts by Categories