PreparedStatement를 여러 번 재사용

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

제 질문은 제가이 코드를 다중 스레드 환경에 넣고 싶다는 사실로 인해 발생합니다. 조언을 해주실 수 있습니까? 감사

---

두 번째 방법은 좀 더 효율적이지만 훨씬 더 좋은 방법은 일괄 적으로 실행하는 것입니다.

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 배치마다 실행할 수 있습니다.

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

---

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.

참고URL : https://stackoverflow.com/questions/2467125/reusing-a-preparedstatement-multiple-times