fayeyiwang 2011-02-24
spring JdbcTemplate 的批量更新:
1、JdbcTemplate batchUpdate(new String[]{});
一次执行多个sql语句;
2、
final List tmpList = ....; int count = JdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter(){ public int getBatchSize() { return tmpList.size(); } public void setValues(PreparedStatement ps, int i)throws SQLException { ps.setString(1, tmpList .get(i).getName()); ps.setString(2,tmpList .get(i).getCode()); ps.setString(3,tmpList .get(i).getDate()); ps.setInt(4,tmpList .get(i).getRank()); }}).length;
此方法可以批量,但是在spring里面是一条一条执行的,大批数据执行效率不佳
3、写PreparedStatement 进行批量
final List<TmpBean> tmpBeanList=...; final String sql = ....; int[] count = (int[]) this.pushJdbcTemplate.execute(sql, new PreparedStatementCallback(){ public Object doInPreparedStatement(PreparedStatement ps)throws SQLException, DataAccessException { int length = stockRanList.size(); ps.getConnection().setAutoCommit(false); for(int i=0;i<length;i++){ ps.setString(1, tmpBeanList.get(i).getName()); ps.setString(2,tmpBeanList.get(i).getCode()); ps.setString(3,tmpBeanList.get(i).getDate()); ps.setInt(4,tmpBeanList.get(i).getRank()); ps.addBatch(); } Object o = ps.executeBatch(); ps.getConnection().commit(); ps.getConnection().setAutoCommit(true); //如果用<aop:config> 来控制事务,需要把上一行注掉,否则会报错 return o; }});