多次重用 PreparedStatement [英] Reusing a PreparedStatement multiple times

问题描述

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

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?

我的意思是:

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();

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

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

推荐答案

第二种方式效率更高，但更好的方式是分批执行:

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

然而，您可以一次执行多少个批处理取决于 JDBC 驱动程序的实现.例如，您可能希望每 1000 批执行一次:

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

对于多线程环境，如果按照普通的JDBC习惯用法，在尽可能短的范围同一个方法块内获取和关闭连接和语句，你就不用担心这个问题使用 try-with-resources 语句，如上面的片段所示.

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

这篇关于多次重用 PreparedStatement的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！