使用dbutils可以进行批处理操作,使用的方法是batch,但是只能执行相同的SQL语句,参数可以不同。
有两个batch方法,源码如下:
/**
* Execute a batch of SQL INSERT, UPDATE, or DELETE queries.
*
* @param conn The Connection to use to run the query. The caller is
* responsible for closing this Connection.
* @param sql The SQL to execute.
* @param params An array of query replacement parameters. Each row in
* this array is one set of batch replacement values.
* @return The number of rows updated per statement.
* @throws SQLException if a database access error occurs
* @since DbUtils 1.1
*/
public int[] batch(Connection conn, String sql, Object[][] params)
throws SQLException {
PreparedStatement stmt = null;
int[] rows = null;
try {
stmt = this.prepareStatement(conn, sql);
for (int i = 0; i < params.length; i++) {
this.fillStatement(stmt, params[i]);
stmt.addBatch();
}
rows = stmt.executeBatch();
} catch (SQLException e) {
this.rethrow(e, sql, params);
} finally {
close(stmt);
}
return rows;
}
/**
* Execute a batch of SQL INSERT, UPDATE, or DELETE queries. The
* <code>Connection</code> is retrieved from the <code>DataSource</code>
* set in the constructor. This <code>Connection</code> must be in
* auto-commit mode or the update will not be saved.
*
* @param sql The SQL to execute.
* @param params An array of query replacement parameters. Each row in
* this array is one set of batch replacement values.
* @return The number of rows updated per statement.
* @throws SQLException if a database access error occurs
* @since DbUtils 1.1
*/
public int[] batch(String sql, Object[][] params) throws SQLException {
Connection conn = this.prepareConnection();
try {
return this.batch(conn, sql, params);
} finally {
close(conn);
}
}
public void fillStatement(PreparedStatement stmt, Object[] params)
throws SQLException {
if (params == null) {
return;
}
ParameterMetaData pmd = stmt.getParameterMetaData();
if (pmd.getParameterCount() < params.length) {
throw new SQLException("Too many parameters: expected "
+ pmd.getParameterCount() + ", was given " + params.length);
}
for (int i = 0; i < params.length; i++) {
if (params[i] != null) {
stmt.setObject(i + 1, params[i]);
} else {
// VARCHAR works with many drivers regardless
// of the actual column type. Oddly, NULL and
// OTHER don't work with Oracle's drivers.
int sqlType = Types.VARCHAR;
if (!pmdKnownBroken) {
try {
sqlType = pmd.getParameterType(i + 1);
} catch (SQLException e) {
pmdKnownBroken = true;
}
}
stmt.setNull(i + 1, sqlType);
}
}
}
示例:向account表中增加10条记录
account表:
create table account(
id int primary key auto_increment,
name varchar(20),
money float
);
//code
@Test
public void batchTest() throws SQLException{
QueryRunner qr = new QueryRunner(JdbcC3p0Utils.getDataSource());
String sql = "insert into account(name, money) values(?,?)";
Object[][] param= new Object[10][];
for(int i=0; i<10; i++){
param[i]= new Object[2];
param[i][0] = "name"+i*10+0;
param[i][1] = i*10+1;
}
qr.batch(sql, param);
}