Reusing a PreparedStatement Multiple Times in Java

1. Overview

In this tutorial, we’ll be looking at how to use PreparedStatements efficiently. A PreparedStatement is an object that stores a precompiled SQL statement for us. We can then use this object to repeatedly execute that SQL. Throughout this article, we’ll see that there are several methods for doing this, and picking the right one can significantly improve our code and the performance of our application.

2. Setup

To start we need to set up a database and get a Connection we can use. Let’s create a table called CUSTOMER with three columns; id, first_name, and last_name:

Connection connection = null;

void setupDatabaseAndConnect() throws SQLException {
    connection = DriverManager.getConnection("jdbc:h2:mem:testDB", "dbUser", "dbPassword");
    String createTable = "CREATE TABLE CUSTOMER (id INT, first_name TEXT, last_name TEXT)";
    connection.createStatement().execute(createTable);
}

Here we’ve connected to a H2 in memory database and kept our Connection object as a class-level variable so that we can use it in our methods later. Following that we’ve created our table as planned using that Connection. The type of database we connect to here and the definition of the table aren’t important, this is just so we have something we can use later in our examples.

3. Inefficient Use of a Prepared Statement

For our first attempt at putting some data into our database, let’s set a very basic usage of a PreparedStatement that works but is far from ideal:

String SQL = "INSERT INTO CUSTOMER (id, first_name, last_name) VALUES(?,?,?)";

void inefficientUsage() throws SQLException {
    for (int i = 0; i < 10000; i++) {
        PreparedStatement preparedStatement = connection.prepareStatement(SQL);
        preparedStatement.setInt(1, i);
        preparedStatement.setString(2, "firstname" + i);
        preparedStatement.setString(3, "secondname" + i);
        preparedStatement.executeUpdate();
        preparedStatement.close();
    }
}

Here we’ve defined our SQL String, then jumped straight into a for loop. For each cycle of the loop, we create a PreparedStatement, set the parameters, execute the update, and then close it.

To see if this has worked we can count the columns in our Customer table. We can do this with another PreparedStatement:

int checkRowCount() {
    try (PreparedStatement counter = connection.prepareStatement("SELECT COUNT(*) AS customers FROM CUSTOMER")) {
        ResultSet resultSet = counter.executeQuery();
        resultSet.next();
        int count = resultSet.getInt("customers");
        resultSet.close();
        return count;
    } catch (SQLException e) {
        return -1;
    }
}

Finally, let’s call both in a test and see what happens:

@Test
void whenCallingInefficientPreparedStatementMethod_thenRowsAreCreatedAsExpected() throws SQLException {
    ReusePreparedStatement service = new ReusePreparedStatement();
    service.setupDatabaseAndConnect();
    service.inefficientUsage();
    int rowsCreated = service.checkRowCount();
    assertEquals(10000, rowsCreated);
}

We can see that everything worked as expected, we created 10,000 rows. However, this was performed far from optimally. We created and closed a PreparedStatement object 10,000 times. The impact on performance this has in other applications depends on how often we do this and the size of our loops. However, it’s best avoided completely as there are better ways which we’ll look at next.

4. Simple Reuse of a Prepared Statement

Following our basic implementation, the obvious improvement is to move the PreparedStatement creation out of the for loop. We can create it once and use it as many times as we need. Another slight improvement we can make is to use try-with-resources to manage the lifecycle of the PreparedStatement.

Let’s see how that looks using the same SQL as last time:

void betterUsage() {
     try (PreparedStatement preparedStatement = connection.prepareStatement(SQL)) {
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "firstname" + i);
            preparedStatement.setString(3, "secondname" + i);
            preparedStatement.executeUpdate();
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

So now we’re only creating our PreparedStatement once. We also don’t have to worry about calling close() on it as that’s taken care of for us. In a real-world implementation, we’d look to handle the exception better of course. This is the minimum efficiency we should be using in our code.

Let’s write a test to check it works as expected:

@Test
void whenCallingBetterPreparedStatementMethod_thenRowsAreCreatedAsExpected() throws SQLException {
    ReusePreparedStatement servicehow to use PreparedStatements efficiently = new ReusePreparedStatement();
    service.setupDatabaseAndConnect();
    service.betterUsage();
    int rowsCreated = service.checkRowCount();
    assertEquals(10000, rowsCreated);
}

This look very familiar from earlier. That’s because we are doing exactly the same thing. Functionally our implementations are essentially the same so far. We can see again here that we get the 10,000 rows we expected.

There are more potential issues with this approach. For one, we’re sending the update to the database every single time, so that’s a lot of database interaction. Also if we get interrupted for any reason it will be very hard to resume the task at all, let alone at the right place. We would have no way of knowing where we’d got to without going through the database and looking at every update we’d planned to do. We’ll address that concern in the next section.

5. Improving Efficiency With Batches

Finally, let’s reach the best option for reusing a PreparedStatement. The key here is using batches.

We’ll add all our updates into a batch and execute that batch at the end when we’re ready. By doing this we remove the risk of something breaking halfway through our task and not knowing where we got to:

void bestUsage() {
    try (PreparedStatement preparedStatement = connection.prepareStatement(SQL)) {
        connection.setAutoCommit(false);
        for (int i = 0; i < 10000; i++) {
            preparedStatement.setInt(1, i);
            preparedStatement.setString(2, "firstname" + i);
            preparedStatement.setString(3, "secondname" + i);
            preparedStatement.addBatch();
        }
        preparedStatement.executeBatch();
        try {
           connection.commit();
        } catch (SQLException e) {
            connection.rollback();
            throw e;
        }
    } catch (SQLException e) {
        throw new RuntimeException(e);
    }
}

Again here we’ve created a PreparedStatement in our try-with-resources. The difference this time is before we start our loop we’ve called setAutoCommit(false). This tells the Connection to group our SQL statements into transactions which we can decide when to commit. Then in our for loop, we add our parameters to the batch. Only once we’ve added them all do we execute the batch, and assuming that goes well we commit the changes. If anything goes wrong during the commit, we catch the exception and roll back to where we started. This means we aren’t left with a job half done and an unknown database state.

We could execute and commit our batches more frequently if we wanted, every 5000 records for example. This would mean we wouldn’t lose all our progress in the event of an interruption. If we did this, we’d likely want to keep a log of how far we’ve got through the updates as we go. This would help us resume what we were doing once we’ve resolved any problems.

6. Conclusion

In this article, we looked at three ways to use PreparedStatements to insert data into a database. We started by creating one for every update we made and saw that it worked well but was inefficient. We progressed to reusing one within the loop, which was better and meant we only had to create and close the object once. Finally, we not only reused the same one within a loop but also batched up our inserts and executed them periodically.

So to make the best use of a PreparedStatement, we need to create it once, reuse it as many times as we need, and execute our updates in batches if there are a lot.