Reusing a PreparedStatement multiple times

Question

Hello,

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

Answer 1

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.

Answer 2

The second way is a tad efficienter, but a much better way is to execute them in batches:

PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
    preparedStatement.setObject(1, someValue);
    preparedStatement.addBatch();
}
preparedStatement.executeQuery();
preparedStatement.close(); // Do this in the finally block!

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 100 batches:

PreparedStatement preparedStatement = connection.prepareStatement(sql);
for (int i = 0; i < 1000; i++) {
    preparedStatement.setObject(1, someValue);
    preparedStatement.addBatch();
    if ((i + 1) % 100 == 0) {
        preparedStatement.executeBatch();
    }
}
preparedStatement.executeBatch();
preparedStatement.close(); // Do this in the finally block!

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. Here's a basic example:

public void executeBatch(/* ... */) throws SQLException { 
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = database.getConnection();
        preparedStatement = connection.prepareStatement(SQL);
        // ...
    } finally {
        close(preparedStatement);
        close(connection);
    }
}

Update: as per the comments, depending on the functional requirement, you would often indeed like to only commit the transaction when all batches are finished, else it may be unpredictable which ones of the batches are already inserted/updated and which not. The DB would get dirty then. Here's how you could do it:

public void executeBatch(/* ... */) throws SQLException { 
    Connection connection = null;
    PreparedStatement preparedStatement = null;
    try {
        connection = database.getConnection();
        connection.setAutoCommit(false);
        preparedStatement = connection.prepareStatement(SQL);
        // ...
        connection.commit();
    } catch (SQLException e) {
        connection.rollback();
        throw e;
    } finally {
        close(preparedStatement);
        close(connection);
    }
}

Answer 3

In my case the method is like this:

` public static int save(/* ... */) throws SQLException {

    Connection connection = null;
    PreparedStatement statement = null;
    try {
            statement = connection.prepareStatement(sql);
            // ...
            statement.execute();
    }
    catch (SQLException e) {

    }

}`

This is static method and is called from multithreaded environment. This surely is wrong implementation or I should say wrong use of prepared statement. I want to change it so that the prepraredStatement object is created only once.

Any suggestion are welcome.

Thanks.

--wadi