Reusing a Preparedstatement Multiple Times

Reusing a PreparedStatement multiple times

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

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

Reusing of a PreparedStatement between methods?

Of course an instance of such a class would be bound to a Connection which might be a disadvantage

Might be? it would be a huge disadvantage. You'd either need to synchronize access to it, which would kill your multi-user performance stone-dead, or create multiple instances and keep them in a pool. Major pain in the ass.

Statement pooling is the job of the JDBC driver, and most, if not all, of the current crop of drivers do this for you. When you call prepareStatement or prepareCall, the driver will handle re-use of existing resource and pre-compiled statements.

Statement objects are tied to a connection, and connections should be used and returned to the pool as quickly as possible.

In short, the standard practice of obtaining a PreparedStatement at the start of the method, using it repeatedly within a loop, then closing it at the end of the method, is best practice.

Does reusing a Statement and Resultset release resources from its previous usage? Or do I have to explicitly close them before reuse?

The behavior of resultsets and (prepared) statements is explicitly documented in the Java API. I suggest that you read the actual documentation (and the JDBC spec) to get the details.

The Statement API says:

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

(emphasis mine).

In your specific code, when you call aStmt.executeQuery(), the old ResultSet assigned to aRset is implicitly closed by the driver. That said, it would be better to explicitly close it yourself (or use Java 7 try-with-resources), to prevent you from forgetting to close the ResultSet in the last iteration through the loop.

Now to the PreparedStatement: When you prepare a statement (in general, implementation can vary), the query is sent to the server for compilation. On execution the parameters for that specific execution is sent to the server. Calling close() on aStmt would result in the prepared statement being deallocated on the server, that is clearly NOT what you want here as you want to re-use the statement with different values for its parameter.

So in short

1. Closing ResultSet is not technically necessary here (except for the last ResultSet created), but it is better to do it explicitly
2. You should only close the PreparedStatement when you are done with it.

Using try-with-resources is one way to remove part of the confusion on these issues, as your code will automatically release resources when it is done with it (at the end of the scope of use):

try (
    ResultSet cRset = cStmt.executeQuery(cQuery);
    PreparedStatement aStmt = aConn.prepareStatement(aQuery);
) {
    while (cRset.next()) {
        //stuff to determine value of parm1

        aStmt.setString(1, parm1);                
        try (ResultSet aRset = aStmt.executeQuery()) {
            //more stuff
        }
    }
}

At the end of this piece of code all JDBC resources are correctly closed (in the right order, even if exceptions occurred etc)

Reusing a PreparedStatement

Yes, the statement must be closed before you perform the next connection.prepareStatement. Otherwise, you're losing your reference to the un-closed previous one (aka leaking statements). Wrap a try {} finally {} around each statement use, closing it in the finally.

Is it possible to create a prepared statement and reuse it later with Java under postgres?

When you use a java.sql.PreparedStatement with the PostgreSQL JDBC driver, it will at first not create a real prepared statement on the database server, but just construct a simple SQL statement to send to the database server. Only at the sixth execution it will think that it is worth the effort to create a named prepared statement on the server that it reuses for future executions.

You can use the prepareThreshold connection property to influence the behavior, see the documentation. So to make your second example use a server prepared statement, you would have to lower the threshold to 0. This is useful only if you know you will reuse all your prepared statements; consider that prepared statements are often used for other purposes like avoiding SQL injection problems.

On the database server there is a similar functionality: the first five times a prepared statement is executed, PostgreSQL computes a custom plan for it. Only at the sixth execution it will consider switching to a generic plan instead, so that you can avoid the overhead of planning from then on. This can be influenced with the PostgreSQL parameter plan_cache_mode from v12 on.

So, with the default settings, it will take ten executions of a java.sql.PreparedStatement before you see a performance increase from avoiding planning costs.

Reusing PreparedStatement causes SQLException

Executing a second query on the PreparedStatement implicitly closes the ResultSet from the previous query. From Statement:

By default, only one ResultSet object per Statement object can be open at the same time.

Use two different statements, something along these lines (note how resultSet1 is retrieved) although of course I don't know what the requirements of your FOUND_ROWS function are:

try (
    Connection connection = dataSource.getConnection();
    PreparedStatement preparedStatement = connection.prepareStatement("SELECT SQL_CALC_FOUND_ROWS * FROM tablets limit " + offset + ", " + noOfRecords + ";");
    ResultSet resultSet = preparedStatement.executeQuery();
    ResultSet resultSet1 = connection.createStatement().executeQuery("SELECT FOUND_ROWS()"); // ****
    ) {
    while (resultSet.next()) {
        tablet = new Tablet();
        tablet.setTabletId(resultSet.getInt("idTablet"));
        tablet.setName(resultSet.getString("name"));
        tablet.setNeedRecepie(resultSet.getBoolean("need_recipe"));
        tablet.setPrice(resultSet.getDouble("price"));
        tablet.setTypeId(resultSet.getInt("type_id"));
        tablet.setDescription(resultSet.getString("description"));
        tablet.setTabletType(TypeFactory.getType(tablet.getTypeId()));
        tablet.setWeight(resultSet.getDouble("weight_of_pack"));
        tablet.setPillsCount(resultSet.getInt("pills_count"));
        tabletSet.add(tablet);
    }
    if (resultSet1.next())
        this.noOfRecords = resultSet1.getInt(1);
} catch (SQLException e) {
    e.printStackTrace();
}

---

Also: Don't use Statement's executeQuery(String) on PreparedStatement. It really shouldn't be there, it's a flaw in the java.sql package design. In fact, the documentation for Statement#executeQuery says:

Note:This method cannot be called on a PreparedStatement or CallableStatement.

Reusing Postgres prepared statement

The prepared statement duration lasts for the duration of the database session which is different from the transaction (the session ends when the client disconnects whereas the transaction ends -usually- with COMMIT or ROLLBACK).

Whether a single plan or several plans are generated depends at least on the following (it's PREPARE STATEMENT that generates the plan):

A prepared statement can be executed with either a generic plan or a custom plan. A generic plan is the same across all executions, while a custom plan is generated for a specific execution using the parameter values given in that call. Use of a generic plan avoids planning overhead, but in some situations a custom plan will be much more efficient to execute because the planner can make use of knowledge of the parameter values. (Of course, if the prepared statement has no parameters, then this is moot and a generic plan is always used.)

By default (that is, when plan_cache_mode is set to auto), the server will automatically choose whether to use a generic or custom plan for a prepared statement that has parameters. The current rule for this is that the first five executions are done with custom plans and the average estimated cost of those plans is calculated. Then a generic plan is created and its estimated cost is compared to the average custom-plan cost. Subsequent executions use the generic plan if its cost is not so much higher than the average custom-plan cost as to make repeated replanning seem preferable.

[quoting Postgres SQL PREPARE command]

---

Related Topics