Why Am I Getting \'java.sql.SQLException: Operation not allowed after ResultSet closed\' When Using JDBC?

Java - Unable to Use ResultSet After Connection Closed

Problem:

An attempt to execute a database query using JDBC results in the following error:

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

Analysis:

JDBC utilizes a ResultSet object to retrieve data from a query. However, when the connection to the database is closed, the ResultSet object becomes invalid and can no longer be used.

Solution:

To resolve this issue, avoid passing the ResultSet object beyond the method that executed the query. Instead, use the ResultSet to populate an object or list and return that resulting object.

Example Code with Populating Objects:

<code class="java">public static Collection<T> sqlquery (String query, RowMapper<T> rowMapper) throws SQLException
{
    Connection connection=null;
    Statement st=null;
    ResultSet rs=null;     
    connection = DriverManager.getConnection("databaseadress","username","password");
    st = connection.createStatement();  
    rs = st.executeQuery(query);
    Collection<T> collection = new ArrayList<>();
    while (rs.next()) {
        collection.add(rowMapper.mapRow(rs));
    }
    // Close resources even if exceptions are thrown
    closeGracefully(rs, st, connection);
    return collection;
}</code>

Closing Resources Gracefully:

<code class="java">private static void closeGracefully(ResultSet rs, Statement st, Connection connection) {
    if (rs != null) {
        try {
            rs.close();
        } catch (SQLException e) { /* Log or ignore */ }
    }
    if (st != null) {
        try {
            st.close();
        } catch (SQLException e) { /* Log or ignore */ }
    }
    if (connection != null) {
        try {
            connection.close();
        } catch (SQLException e) { /* Log or ignore */ }
    }
}</code>

Additional Considerations:

• Use a row mapper to create objects from each row of the ResultSet.
• Parameterize queries to prevent SQL injection attacks.
• Consider utilizing third-party libraries such as JDBC or Spring JDBC for simplified database access.

The above is the detailed content of Why Am I Getting \'java.sql.SQLException: Operation not allowed after ResultSet closed\' When Using JDBC?. For more information, please follow other related articles on the PHP Chinese website!