Reusing PreparedStatement causes SQLException

Question

Try with recources gives me following exception:

java.sql.SQLException: Operation not allowed after ResultSet closed

My code:

public Set<Tablet> viewAllTablets(int offset, int noOfRecords) throws OutOfRangeException {
    Set<Tablet> tabletSet = new HashSet<>();
    Tablet tablet = null;

    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 = preparedStatement.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();
    }
    return tabletSet;
}

Please include the ENTIRE, COMPLETE stack trace, and also identify the line in your code that throws the exception. Without that basic information nobody can help you. — Jim Garrison, Jul 15 '16 at 06:43
Try with resources automatically close the opened resources when the try block is done — pahan, Jul 15 '16 at 06:44
@pahan: While that's true, it's not relevant to the above. The OP isn't accessing (and can't access) those ResultSets after the try-with-resources has done that. — T.J. Crowder, Jul 15 '16 at 06:51

T.J. Crowder · Accepted Answer · 2016-07-15T06:51:28.857

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.

If I would like to set parameters for the `preparedStatement` (eg: `setInt()`), would I just do that within the try-with-resources block as well? Right after the `preparedStatement` is initialized? — theyuv, May 09 '20 at 15:27
@theyuv - You'd either use nested `try-with-resources` or you use a function to create the prepared statement and set its parameters, details in [the answers to this question](https://stackoverflow.com/questions/8066501/how-should-i-use-try-with-resources-with-jdbc). — T.J. Crowder, May 10 '20 at 08:07

Reusing PreparedStatement causes SQLException

1 Answers1