How can I avoid ResultSet is closed exception in Java?

Question

As soon as my code gets to my while(rs.next()) loop it produces the ResultSet is closed exception. What causes this exception and how can I correct for it?

EDIT: I notice in my code that I am nesting while(rs.next()) loop with another (rs2.next()), both result sets coming from the same DB, is this an issue?

Meyers

Answer 1

Sounds like you executed another statement in the same connection before traversing the result set from the first statement. If you're nesting the processing of two result sets from the same database, you're doing something wrong. The combination of those sets should be done on the database side.

Increscent

This is not true for all drivers and RDBMS. – Idyllic

sqlserver.jar cannot do it nesting, but jtds.jar can do it. – Disrelish

Answer 2

This could be caused by a number of reasons, including the driver you are using.

a) Some drivers do not allow nested statements. Depending if your driver supports JDBC 3.0 you should check the third parameter when creating the Statement object. For instance, I had the same problem with the JayBird driver to Firebird, but the code worked fine with the postgres driver. Then I added the third parameter to the createStatement method call and set it to ResultSet.HOLD_CURSORS_OVER_COMMIT, and the code started working fine for Firebird too.

static void testNestedRS() throws SQLException {

    Connection con =null;
    try {
        // GET A CONNECTION
        con = ConexionDesdeArchivo.obtenerConexion("examen-dest");
        String sql1 = "select * from reportes_clasificacion";

        Statement st1 = con.createStatement(
                ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY, 
                ResultSet.HOLD_CURSORS_OVER_COMMIT);
        ResultSet rs1 = null;

        try {
            // EXECUTE THE FIRST QRY
            rs1 = st1.executeQuery(sql1);

            while (rs1.next()) {
                // THIS LINE WILL BE PRINTED JUST ONCE ON
                                    // SOME DRIVERS UNLESS YOU CREATE THE STATEMENT 
                // WITH 3 PARAMETERS USING 
                                    // ResultSet.HOLD_CURSORS_OVER_COMMIT
                System.out.println("ST1 Row #: " + rs1.getRow());

                String sql2 = "select * from reportes";
                Statement st2 = con.createStatement(
                        ResultSet.TYPE_SCROLL_INSENSITIVE,
                        ResultSet.CONCUR_READ_ONLY);

                // EXECUTE THE SECOND QRY.  THIS CLOSES THE FIRST 
                // ResultSet ON SOME DRIVERS WITHOUT USING 
                                    // ResultSet.HOLD_CURSORS_OVER_COMMIT

                st2.executeQuery(sql2);

                st2.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            rs1.close();
            st1.close();
        }

    } catch (SQLException e) {

    } finally {
        con.close();

    }

}

b) There could be a bug in your code. Remember that you cannot reuse the Statement object, once you re-execute a query on the same statement object, all the opened resultsets associated with the statement are closed. Make sure you are not closing the statement.

Wareroom

I was having same issue with Firebird JDBC drivers. I can verify that the ResultSet.HOLD_CURSORS_OVER_COMMIT is working. – Inrush

Note that the correct way of solving this problem with Jaybird (and JDBC in general) is to disable auto-commit before executing multiple statements. – Swadeshi

Answer 3

Also, you can only have one result set open from each statement. So if you are iterating through two result sets at the same time, make sure they are executed on different statements. Opening a second result set on one statement will implicitly close the first. http://java.sun.com/javase/6/docs/api/java/sql/Statement.html

Morissa

Answer 4

The exception states that your result is closed. You should examine your code and look for all location where you issue a ResultSet.close() call. Also look for Statement.close() and Connection.close(). For sure, one of them gets called before rs.next() is called.

Brandes

Answer 5

You may have closed either the Connection or Statement that made the ResultSet, which would lead to the ResultSet being closed as well.

Artificial

Answer 6

Proper JDBC call should look something like this is modern Java (Java 7+):

try (
    Connection  conn = DriverManager.getConnection(myUrl,"",""); 
    Statement stmt = conn.createStatement(); 
    ResultSet  rs = stmt.executeQuery(myQuery); 
) {
    while ( rs.next() ) { 
        // process results
    } 
} catch (SqlException e) {
    System.err.println("Got an exception! "); 
    System.err.println(e.getMessage()); 
}

And before the days of try-with-resources syntax, in Java 6 and earlier:

try { 
    Connection conn;
    Statement stmt;
    ResultSet rs; 
    
    try {
        conn = DriverManager.getConnection(myUrl,"",""); 
        stmt = conn.createStatement(); 
        rs = stmt.executeQuery(myQuery); 
        
        while ( rs.next() ) { 
            // process results
        } 
         
    } catch (SqlException e) { 
        System.err.println("Got an exception! "); 
        System.err.println(e.getMessage()); 
    } finally {
        // you should release your resources here
        if (rs != null) { 
            rs.close();
        }

        if (stmt != null) {
            stmt.close();
        }

        if (conn != null) {
            conn.close();
        }
    }
} catch (SqlException e) {
    System.err.println("Got an exception! "); 
    System.err.println(e.getMessage()); 
}

you can close connection (or statement) only after you get result from result set. Safest way is to do it in finally block. However close() could also throe SqlException, hence the other try-catch block.

Marozik

Gotta love that nested try/catch. Sometimes I really do loathe JDBC. It does bloat this example but you really should close the resultset and the statement too. You did call it a proper JDBC call! – Tellford

Update: In modern Java, we can use try-with-resources syntax to eliminate those explicit calls to close. – Fistic

Answer 7

I got same error everything was correct only i was using same statement interface object to execute and update the database. After separating i.e. using different objects of statement interface for updating and executing query i resolved this error. i.e. do get rid from this do not use same statement object for both updating and executing the query.

Skip

Answer 8

Check whether you have declared the method where this code is executing as static. If it is static there may be some other thread resetting the ResultSet.

Parallelism

Answer 9

make sure you have closed all your statments and resultsets before running rs.next. Finaly guarantees this

public boolean flowExists( Integer idStatusPrevious, Integer idStatus, Connection connection ) {
    LogUtil.logRequestMethod();

    PreparedStatement ps = null;
    ResultSet rs = null;
    try {
        ps = connection.prepareStatement( Constants.SCRIPT_SELECT_FIND_FLOW_STATUS_BY_STATUS );
        ps.setInt( 1, idStatusPrevious );
        ps.setInt( 2, idStatus );

        rs = ps.executeQuery();

        Long count = 0L;

        if ( rs != null ) {
            while ( rs.next() ) {
                count = rs.getLong( 1 );
                break;
            }
        }

        LogUtil.logSuccessMethod();

        return count > 0L;
    } catch ( Exception e ) {
        String errorMsg = String
            .format( Constants.ERROR_FINALIZED_METHOD, ( e.getMessage() != null ? e.getMessage() : "" ) );
        LogUtil.logError( errorMsg, e );

        throw new FatalException( errorMsg );
    } finally {
        rs.close();
        ps.close();
    }

Counterinsurgency

Answer 10

A ResultSetClosedException could be thrown for two reasons.

1.) You have opened another connection to the database without closing all other connections.

2.) Your ResultSet may be returning no values. So when you try to access data from the ResultSet java will throw a ResultSetClosedException.

Hade

I was making two connection without closing the first one. This leads to the 'ResultSet is Closed' error. Thanks for highlighting the cause here. – Generate

Answer 11

It happens also when using a ResultSet without being in a @Transactional method.

ScrollableResults results = getScrollableResults("select e from MyEntity e");
while (results.next()) {
   ...
}
results.close();

if MyEntity has eager relationships with other entities. the second time results.next() is invoked the ResultSet is closed exception is raised.

so if you use ScrollableResults on entities with eager relationships make sure your method is run transactionally.

Cathouse

Answer 12

"result set is closed" happened to me when using tag <collection> in MyBatis nested (one-to-many) xml <select> statement

A Spring solution could be to have a (Java) Spring @Service layer, where class/methods calling MyBatis select-collection statements are annotated with

@Transactional(propagation = Propagation.REQUIRED)

annotations being:

import org.springframework.transaction.annotation.Propagation; 
import org.springframework.transaction.annotation.Transactional;

this solution does not require to set the following datasource properties (i.e., in JBoss EAP standalone*.xml):

<xa-datasource-property name="downgradeHoldCursorsUnderXa">**true**\</xa-datasource-property>
<xa-datasource-property name="resultSetHoldability">**1**</xa-datasource-property>

Stoma

Answer 13

I recommend to keep the st2.close(); out of the while, so it doesn't close the connection after the first loop, and then the conncection is closed.

Candra

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center. – Homemade