第二种方式更有效,但更好的方法是批量执行它们:
public void executeBatch(List entities) throws SQLException {
try (
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL);
) {
for (Entity entity : entities) {
statement.setObject(1, entity.getSomeProperty());
// ...
statement.addBatch();
}
statement.executeBatch();
}
}
但是,您依赖于JDBC驱动程序实现,您可以一次执行多少批次。 例如,您可能希望每1000批次执行它们:
public void executeBatch(List entities) throws SQLException {
try (
Connection connection = dataSource.getConnection();
PreparedStatement statement = connection.prepareStatement(SQL);
) {
int i = 0;
for (Entity entity : entities) {
statement.setObject(1, entity.getSomeProperty());
// ...
statement.addBatch();
i++;
if (i % 1000 == 0 || i == entities.size()) {
statement.executeBatch(); // Execute every 1000 items.
}
}
}
}
对于多线程环境,如果使用try-with-resources语句按照正常的JDBC习惯用法获取并关闭同一方法块内最短可能范围内的连接和语句,则不必担心这种情况。 上面的代码段显示。
如果这些批次是事务性的,那么您希望关闭连接的自动提交,并且只在所有批次完成时提交事务。 否则,当第一批批次成功而后者不成功时,可能会导致脏数据库。
public void executeBatch(List entities) throws SQLException {
try (Connection connection = dataSource.getConnection()) {
connection.setAutoCommit(false);
try (PreparedStatement statement = connection.prepareStatement(SQL)) {
// ...
try {
connection.commit();
} catch (SQLException e) {
connection.rollback();
throw e;
}
}
}
}