Spring JdbcTemplate batch updates and inserts
Q. Why are batch updates faster?
A.
- The query doesn't 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; } }
Labels: Spring JDBC
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home