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)

Answer 1

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.