Reusing a PreparedStatement multiple times

Question

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    I mean   for  int i 0  i lt 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 lt 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

User · Answer

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.

User · Answer

The second way is a tad more efficient  but a much better way is to execute them in batches   public void executeBatch List lt Entity gt  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 lt Entity gt  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 lt Entity gt  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

[java] Reusing a PreparedStatement multiple times

Examples related to java

Examples related to jdbc

Examples related to prepared-statement