Operation not allowed after ResultSet closed in java

Question

I have class called Database.

    public class Database {
    public Connection connect = null;
    public Statement st = null;
    public PreparedStatement ps = null;
    public ResultSet rs = null;

    public boolean connectDB() throws Exception {
      try {

      Class.forName("com.mysql.jdbc.Driver");

      connect = DriverManager
        .getConnection("jdbc:mysql://localhost/ots?"
              + "user=root&password=mongolia");
    } catch (Exception e) {
      System.out.println(e);
    }
    return true;
   }

    public void disconnectDB() {
    try {
      if (rs != null) {
        rs.close();
      }

      if (st != null) {
        st.close();
      }

      if (connect != null) {
        connect.close();
      }
    } catch (Exception e) {

    }
    }

     } 

and class called user which is extending Database class

public class User extends Database {
    public ResultSet fetchTable(){
        try{
            connectDB();            
            st = connect.createStatement();
            rs = st.executeQuery("SELECT * FROM user");         
        }catch(Exception e){
            System.out.println(e);
        }finally{
            disconnectDB();
        }
        return rs;
    }
  }
//Inside JSP page
  User user = new User();
  ResultSet data = user.fetchTable();

  //getting exception in the data.next() function
  //java.sql.SQLException: Operation not allowed after ResultSet closed

  while(data.next()){
        out.println("<p>"+data.getInt(0)+"</p>");
   }

//getting exception in the data.next() function
//java.sql.SQLException: Operation not allowed after ResultSet closed

Answer 1

The exception is entirely expected. You're connecting the DB, obtaining a result set, closing the DB and the result set and then trying to access the closed result set.

This is not how things are supposed to work in JDBC.

You need to map the result set to a List<User> directly after retrieving it and then close the result set and return the List<User> instead.

For some concrete examples, head to the answer on this question: JDBC driver throws "ResultSet Closed" exception on empty ResultSet

---

Unrelated to the concrete problem, you've other severe problems in the code. Among others, you have declared the Connection, Statement and ResultSet as instance variables instead of as method local variables. This will fail hard when the same instance is been shared between multiple threads (which may occur when two or more users simultaneously access your web application). I'd also fix on that.

---

Update: the other answers posted so far recommend to remove the disconnectDB() call or to call it only after iterating through the result set in the other method. This is wrong. You should not pass the ResultSet out of the method. Your code would be still threadunsafe and you would still risk resource leaking in case of exceptions. You should create, use and close it in the very same method block. Here's the proper approach, copypasted from the aforementioned question:

public List<User> list() throws SQLException {
    Connection connection = null;
    PreparedStatement statement = null;
    ResultSet resultSet = null;
    List<User> users = new ArrayList<User>();

    try {
        connection = database.getConnection();
        statement = connection.prepareStatement("SELECT id, username, email, age FROM user");
        resultSet = statement.executeQuery();
        while (resultSet.next()) {
            users.add(new User(
                resultSet.getLong("id"),
                resultSet.getString("username"),
                resultSet.getString("email"),
                resultSet.getInteger("age")));
        }
    } finally {
        close(resultSet, statement, connection);
    }

    return users;
}