多次重用 PreparedStatement

如果使用 PreparedStatement 和一个没有任何池的公共连接，是否可以为每个 dml/sql 操作重新创建一个实例，以保持预处理语句的强大功能？

我的意思是:

for (int i=0; i<1000; i++) {
PreparedStatement preparedStatement = connection.prepareStatement(sql);
preparedStatement.setObject(1, someValue);
preparedStatement.executeQuery();
preparedStatement.close();
}

而不是:

PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i=0; i<1000; i++) {
preparedStatement.clearParameters();
preparedStatement.setObject(1, someValue);
preparedStatement.executeQuery();
}
preparedStatement.close();

我的问题是，我想把这段代码放到一个多线程环境中，你能给我一些建议吗？谢谢

110138 次浏览
小开

The loop in your code is only an over-simplified example, right?

It would be better to create the PreparedStatement only once, and re-use it over and over again in the loop.

In situations where that is not possible (because it complicated the program flow too much), it is still beneficial to use a PreparedStatement, even if you use it only once, because the server-side of the work (parsing the SQL and caching the execution plan), will still be reduced.

To address the situation that you want to re-use the Java-side PreparedStatement, some JDBC drivers (such as Oracle) have a caching feature: If you create a PreparedStatement for the same SQL on the same connection, it will give you the same (cached) instance.

About multi-threading: I do not think JDBC connections can be shared across multiple threads (i.e. used concurrently by multiple threads) anyway. Every thread should get his own connection from the pool, use it, and return it to the pool again.

小开
最佳答案

The second way is a tad more efficient, but a much better way is to execute them in batches:

public void executeBatch(List<Entity> 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();
}
}

You're however dependent on the JDBC driver implementation how many batches you could execute at once. You may for example want to execute them every 1000 batches:

public void executeBatch(List<Entity> 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.
}
}
}
}

As to the multithreaded environments, you don't need to worry about this if you acquire and close the connection and the statement in the shortest possible scope inside the same method block according the normal JDBC idiom using try-with-resources statement as shown in above snippets.

If those batches are transactional, then you'd like to turn off autocommit of the connection and only commit the transaction when all batches are finished. Otherwise it may result in a dirty database when the first bunch of batches succeeded and the later not.

public void executeBatch(List<Entity> 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;
}
}
}
}

提交答案