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;
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;
- }});