How to return the Result Set in Java

Question

I am trying to return a resultset of a given student id and show the gpa. However it is saying I am not able to do the operation when the result set is closed. This is my code:

/**
 * Returns a ResultSet with a row containing the computed GPA (named as gpa) for a given student id.
 * You must use a PreparedStatement.
 * 
 * @return
 *       ResultSet containing computed GPA
 */
public ResultSet computeGPA(String studentId) throws SQLException
{
    ResultSet rst;

     // TODO: Use a PreparedStatement
    try(Connection con = connect()){

        String sql = "SELECT gpa FROM student WHERE sid=?";
        PreparedStatement pstmt = con.prepareStatement(sql);
        pstmt.setString(1, studentId);
        rst = pstmt.executeQuery();

        while(rst.next()){
            System.out.println(rst.getString("gpa"));
        }
    }
    return rst;
}

This is the result im getting

Connecting to database. 2.96 Exception in thread "main" java.sql.SQLException: Operation not allowed after ResultSet closed at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:975) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:920) at com.mysql.jdbc.ResultSetImpl.checkClosed(ResultSetImpl.java:804) at com.mysql.jdbc.ResultSetImpl.getMetaData(ResultSetImpl.java:3107) at EnrollJDBC.resultSetToString(EnrollJDBC.java:500) at EnrollJDBC.main(EnrollJDBC.java:59)

Ivan · Answer 1 · 2018-10-29T18:28:13.123

You use try-with-resources which closes Connection when your try block is done. In Java when you close Connection you also automatically close all ResultSet objects associated with that connection. And that is what exception tells you. You are trying to use closed ResultSet. So you need to change your code to close connection only when ResultSet is fully processed.

Since you execute SELECT by id you get either one record or no records. So why not to return that only value if it exists or null (or empty string) if student with such id doesn't exists instead of returning ResultSet object from computeGPA() method?

Joop Eggen · Accepted Answer · 2019-01-02T06:57:51.610

When the (prepared) statement is closed, also its result sets are closed. This is understandable as ResultSet is a heavy class, with metadata and all.

Return a more specific list.

public List<String> computeGPA(String studentId) throws SQLException {
    String sql = "SELECT gpa FROM student WHERE sid=?";
    try (Connection con = connect();
            PreparedStatement pstmt = con.prepareStatement(sql)) {
        pstmt.setString(1, studentId);
        try (ResultSet rst = pstmt.executeQuery()) {
            List<String> gpas = new ArrayList<>();
            while (rst.next()) {
                gpas.add(rst.getString(1));
            }
            return gpas;
        }
    }
}

The second try-with-resources is officially not needed as said, but code checkers will notice that ResultSet is Autocloseable, and might give a false positive warning.

Well, what if the rows aren't Strings but of different types, what then? — Freddy, Oct 28 '19 at 18:02
@Freddy I used the OP's types and the error `Operation not allowed after ResultSet closed` indicates that they probably are okay. `setLong(1, longId)` for other cases. — Joop Eggen, Oct 29 '19 at 09:06

score 1 · Answer 3 · answered Jul 31 '19 at 16:14

You can't return ResultSet because it will be closed when method destroyed. But you can get raw data from ResultSet, try this:

public ArrayList<Byte[]> getResultQuery(String query){
    ArrayList<Byte[]> arrayList = new ArrayList<>();
    Byte[] row;
    conn = getConnection(db_url);
    try {
        Statement statement = conn.createStatement();
        ResultSet resultSet = statement.executeQuery(query);
        int countColumn = resultSet.getMetaData().getColumnCount();
        if (countColumn==0) return null;
        while (resultSet.next()){
            row = new Byte[countColumn];
            for (int i = 0; i<countColumn; i++){
                row[i] = resultSet.getByte(i+1);
            }
            arrayList.add(row);
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
    return arrayList;
}

public static Connection getConnection (String db_url){
    Connection conn = null;
    try{
        Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
        conn = DriverManager.getConnection(db_url);
    }catch(Exception e){
        e.printStackTrace();
    }
    return conn;
}

In this, i try to return ArrayList of Byte[], 1 Byte[] = 1 row in ResultSet. If you want to get some value, just convert Byte[i] to your datatype you want!

How to return the Result Set in Java

3 Answers3