多次重用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.
            }
        }
    }
}

As对于多线程环境，如果根据使用内获取并关闭连接和语句在最短范围内，则无需担心这种情况。 =https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html\"rel =noreferrer> 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屋！