Java 多次重用 PreparedStatement

Reusing a PreparedStatement multiple times

提问by Steel Plume

in the case of using PreparedStatement with a single common connection without any pool, can I recreate an instance for every dml/sql operation mantaining the power of prepared statements?

如果将 PreparedStatement 与没有任何池的单个公共连接一起使用，我是否可以为每个 dml/sql 操作重新创建一个实例，以保持准备好的语句的功能？

I mean:

我的意思是：

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

instead of:

代替：

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

my question arises by the fact that I want to put this code into a multithreaded environment, can you give me some advice? thanks

我的问题是因为我想将此代码放入多线程环境中，您能给我一些建议吗？谢谢

采纳答案by BalusC

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:

然而，您依赖于 JDBC 驱动程序实现，您可以一次执行多少批。例如，您可能希望每 1000 个批次执行一次：

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 blockaccording the normal JDBC idiom using try-with-resourcesstatement as shown in above snippets.

对于多线程环境，如果您根据正常的 JDBC 习惯用法使用try-with-resources语句在同一方法块内的最短可能范围内获取和关闭连接和语句，则无需担心这一点，如图所示以上片段。

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

回答by Thilo

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.

最好只创建 PreparedStatement 一次，并在循环中一遍又一遍地重复使用它。

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.

在不可能的情况下（因为它使程序流变得过于复杂），使用 PreparedStatement 仍然是有益的，即使您只使用一次，因为服务器端的工作（解析 SQL 并缓存执行计划），仍将减少。

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.

为了解决想要重用Java端PreparedStatement的情况，一些JDBC驱动（比如Oracle）有一个缓存特性：如果你在同一个连接上为同一个SQL创建一个PreparedStatement，它会给你同样的（缓存）实例。

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.

关于多线程：我不认为JDBC连接可以跨多个线程共享（即由多个线程并发使用）。每个线程都应该从池中获取自己的连接，使用它，然后再次将其返回到池中。