spring之JDBC批量操作

spring之JDBC批量操作
      jdbcTemplate.batchUpdate(final String[] sql) ，API解释：Issue multiple SQL updates on a single JDBC Statement using batching，翻译过来大致为：解决多个sql的插入、更新、删除操作在一个Statement中。性能一般。
   jdbcTemplate.batchUpdate(String sql, final BatchPreparedStatementSetter pss),类似于JDBC的PreparedStatement，性能较上着有所提高。
   我们举例说明如何使用，示例如下:

final int count = 2000;  
    final List<String> firstNames = new ArrayList<String>(count);  
    final List<String> lastNames = new ArrayList<String>(count);  
    for (int i = 0; i < count; i++) {  
      firstNames.add("First Name " + i);  
      lastNames.add("Last Name " + i);  
    }  
    jdbcTemplate.batchUpdate(  
            "insert into customer (id, first_name, last_name, last_login, comments) values (?, ?, ?, ?, ?)",  
            new BatchPreparedStatementSetter() {  
           //为prepared statement设置参数。这个方法将在整个过程中被调用的次数  
        public void setValues(PreparedStatement ps, int i) throws SQLException {  
                ps.setLong(1, i + 10);  
                ps.setString(2, firstNames.get(i));  
                ps.setString(3, lastNames.get(i));  
                ps.setNull(4, Types.TIMESTAMP);  
                ps.setNull(5, Types.CLOB);  
              }  
              //返回更新的结果集条数  
          public int getBatchSize() {  
                   return count;  
              }  
            });  
  }  
final int count = 2000;
    final List<String> firstNames = new ArrayList<String>(count);
    final List<String> lastNames = new ArrayList<String>(count);
    for (int i = 0; i < count; i++) {
      firstNames.add("First Name " + i);
      lastNames.add("Last Name " + i);
    }
    jdbcTemplate.batchUpdate(
            "insert into customer (id, first_name, last_name, last_login, comments) values (?, ?, ?, ?, ?)",
            new BatchPreparedStatementSetter() {
           //为prepared statement设置参数。这个方法将在整个过程中被调用的次数
        public void setValues(PreparedStatement ps, int i) throws SQLException {
                ps.setLong(1, i + 10);
                ps.setString(2, firstNames.get(i));
                ps.setString(3, lastNames.get(i));
                ps.setNull(4, Types.TIMESTAMP);
                ps.setNull(5, Types.CLOB);
              }
              //返回更新的结果集条数
          public int getBatchSize() {
                   return count;
              }
            });
  }
 

  BatchSqlUpdate类是SqlUpdate 的子类，适用于插入、删除、更新批量操作，内部使用PreparedStatement，所以效率很高，批量语句达到设定的batchSize，或者手动调用flush才会执行批量操作。注意：此类是非线程安全的，必须为每个使用者创建一个实例，或者在同一个线程中使用前调用reset。
   下面我们举例说明如何使用BatchSqlUpdate，来执行批量操作。示例如下：

class BatchInsert extends BatchSqlUpdate {  
  private static final String SQL = "insert into t_customer (id, first_name, last_name, last_login, "  
      + "comments) values (?, ?, ?, ?, null)";  
  
  BatchInsert(DataSource dataSource) {  
    super(dataSource, SQL);  
    declareParameter(new SqlParameter(Types.INTEGER));  
    declareParameter(new SqlParameter(Types.VARCHAR));  
    declareParameter(new SqlParameter(Types.VARCHAR));  
    declareParameter(new SqlParameter(Types.TIMESTAMP));  
  
    setBatchSize(10);  
  }  
  
}  
class BatchInsert extends BatchSqlUpdate {
  private static final String SQL = "insert into t_customer (id, first_name, last_name, last_login, "
      + "comments) values (?, ?, ?, ?, null)";
  BatchInsert(DataSource dataSource) {
    super(dataSource, SQL);
    declareParameter(new SqlParameter(Types.INTEGER));
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.VARCHAR));
    declareParameter(new SqlParameter(Types.TIMESTAMP));
    setBatchSize(10);
  }
}
 
int count = 5000;  
    for (int i = 0; i < count; i++) {  
      batchInsert.update(new Object[] { i + 100L, "a" + i, "b" + i, null });  
    }  
int count = 5000;
    for (int i = 0; i < count; i++) {
      batchInsert.update(new Object[] { i + 100L, "a" + i, "b" + i, null });
    }