PSQLException: this ResultSet is closed

Question

i've occours this strange error for the first time in my life, and i don't know what does it means. I've a class that retrieve information from a table on a postgresql database, do some operations and return an arraylist with parsed element:

ResultSet rs = ProduttoreDCS.getProduttori();
        System.out.println("Recuperato result set produttori");
        ArrayList<String[]> res = new ArrayList<String[]>();


        while (rs.next()) {
            String[] current = new String[6];

            current[0] = Integer.toString(rs.getInt("partita_iva"));
            current[1] = rs.getString("nome");
            current[2] = rs.getString("cognome");
            current[3] = rs.getString("via_sede");
            current[4] = rs.getString("citta_sede");
            current[5] = rs.getString("provincia_sede");

            res.add(current);
            current = null;
        }

        return res;

the error is on "while" line.

public static ResultSet getProduttori() throws ClassNotFoundException, SQLException {
    /*
     * retrieve all record from produttori table
     */
    Connection conn = null;
    ResultSet res = null;
    Statement stmt = null;
    String query = "SELECT * FROM produttore";

    conn = ConnectionManager.getConnection();
    System.out.println("Connection obtained by ProduttoreDCS class");
    stmt = conn.createStatement();
    res = stmt.executeQuery(query);

    stmt.close();
    conn.close();


    return res;   
}

Answer 1

As per Docs

A ResultSet object is automatically closed when the Statement object that generated it is closed, re-executed, or used to retrieve the next result from a sequence of multiple results.

You are closed the connection and then you are iterating ,where it is null.Please read the data and then close the connection.

A good practice here is

Connection conn = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
    conn = 
    stmt = conn.prepareStatement("sql");
    rs = stmt.executeQuery();
   //DO SOME THING HERE
} catch {
    // Error Handling
} finally {
    try { if (rs != null) rs.close(); } catch (Exception e) {};
    try { if (stmt != null) stmt.close(); } catch (Exception e) {};
    try { if (conn != null) conn.close(); } catch (Exception e) {};
}

Answer 2

Closing the connection makes the resultSet go away.

A ResultSet is not a container you can use to pass data around in, it's only a wrapper around a cursor. You should run through the resultSet contents and copy them into a data structure, then return the data structure. You are already doing that, but you need to do it before closing the statement and connection.

Change the code to:

public static List<String[]> getProduttori() throws ClassNotFoundException, SQLException {

    ArrayList<String[]> res = new ArrayList<String[]>();
    /*
     * retrieve all record from produttori table
     */
    Connection conn = null;
    ResultSet rs = null;
    Statement stmt = null;
    String query = "SELECT * FROM produttore";

    conn = ConnectionManager.getConnection();
    try {
        System.out.println("Connection obtained by ProduttoreDCS class");
        stmt = conn.createStatement();
        try {
            rs = stmt.executeQuery(query);
            while (rs.next()) {
                String[] current = new String[6];
                current[0] = Integer.toString(rs.getInt("partita_iva"));
                current[1] = rs.getString("nome");
                current[2] = rs.getString("cognome");
                current[3] = rs.getString("via_sede");
                current[4] = rs.getString("citta_sede");
                current[5] = rs.getString("provincia_sede");
                res.add(current);
            }        
            return res;
        } finally {
            try {
                stmt.close();
            } catch (SQLException e) {
                log.error(e);
            }
        }
    } finally {
        try {
        conn.close();
        } catch (SQLException e) {
            log.error(e);
        }
    }        
}

Answer 3

This is the problem:

stmt.close();
conn.close();

You're closing the connection to the database. You can't do that until you've read the data - otherwise how is the ResultSet going to read it? Maybe it will have read some data in to start with, but it's not meant to be a disconnected object.

In particular, from the docuemntation for ResultSet.close:

Note: A ResultSet object is automatically closed by the Statement object that generated it when that Statement object is closed, re-executed, or is used to retrieve the next result from a sequence of multiple results.

Answer 4

when you close the connection object in your getProduttori method, your result set will be automatically closed. when you try to re use it , it will be null.

ResultSet rs = ProduttoreDCS.getProduttori();
             = null, as you have closed the connection in getProduttori
               method, which will automatically close the resultset 
               thus, it returns null.

From Connection#close

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

applies same when closing the Statement as well.For your code to work don't close Statement and Connection untill you get the rows.

@Baadshah has already shown you the standard way. If you are using java 7+ you can make use of try-with-resource block, which would make your life simpler.

 try(Connection conn = gettheconn){
     get the statement here
     get the result set 
      perform your ops
 }
 catch(SQLException ex){
   ex.printstacktrace(); 
  }

If you observe, there is no finally block, your connection object will be closed once you exit the try block automatically.you don't have to explicity call Connection#close()