Why does my Java code throw a \'Operation Not Allowed After ResultSet Closed\' error when using JDBC?

JDBC ResultSet Error: "Operation Not Allowed After ResultSet Closed"

In Java, using JDBC to connect to a database and execute queries via a ResultSet can lead to errors if the connection is closed prematurely.

Cause:

The JDBC ResultSet is not a complete representation of the query results. Instead, it provides a live connection to the database, allowing you to fetch results row by row. If the connection is closed, the ResultSet loses its connection and becomes invalid.

Error Message:

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

Code Sample:

The following code demonstrates the error:

<code class="java">ResultSet rs = null;
Connection connection = null;
Statement st = null;
try {
    connection = DriverManager.getConnection("databaseadress","username","password");
    st = connection.createStatement();  
    rs = st.executeQuery(query);
} catch (...) {} // exception handling
finally {
    if (rs != null) rs.close();
    if (st!= null) st.close();
    if (connection != null) connection.close();
}</code>

In this code, the error occurs because rs is still being used outside the finally block, even after the connection is closed.

Solution:

To avoid this error, you should populate a transient object or collection based on the ResultSet within the database connection scope. Once the connection is closed, the transient object or collection can be returned or used without any issues.

Restructured Code:

<code class="java">public static &lt;T&gt; List&lt;T&gt; sqlquery (String query, RowMapper&lt;T&gt; 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);
    List&lt;T&gt; list = new ArrayList&lt;&gt;();
    while (rs.next()) {
        list.add(rowMapper.mapRow(rs));
    }
    if (rs != null) rs.close();
    if (st != null) st.close();
    if (connection != null) connection.close();
    return list;
}</code>

The above is the detailed content of Why does my Java code throw a \'Operation Not Allowed After ResultSet Closed\' error when using JDBC?. For more information, please follow other related articles on the PHP Chinese website!