Jdbc Driver Throws "Resultset Closed" Exception on Empty Resultset

JDBC driver throws ResultSet Closed exception on empty ResultSet

Empty or not, but doing the following is always faulty:

resultSet = statement.executeQuery(sql);
string = resultSet.getString(1); // Epic fail. The cursor isn't set yet.

This is not a bug. This is documented behaviour. Every decent JDBC tutorial mentions it. You need to set the ResultSet's cursor using next() before being able to access any data.

If you're actually interested whether the supposedly unique row exist or not, then just check the outcome of next(). For example in a fictive UserDAO class:

public boolean exist(String username, String password) throws SQLException {
    boolean exist = false;

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id FROM user WHERE username = ? AND password = MD5(?)");
    ) {
        statement.setString(1, username);
        statement.setString(2, password);

        try (ResultSet resultSet = statement.executeQuery()) {
            exist = resultSet.next();
        }
    }

    return exist;
}

If you actually expect only zero or one row, then just do something like:

public User find(String username, String password) throws SQLException {
    User user = null;

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user WHERE username = ? AND password = MD5(?)");
    ) {
        statement.setString(1, username);
        statement.setString(2, password);

        try (resultSet = statement.executeQuery()) {
            if (resultSet.next()) {
                user = new User(
                    resultSet.getLong("id"),
                    resultSet.getString("username"),
                    resultSet.getString("email"),
                    resultSet.getDate("birthdate")); 
            }
        }
    }

    return user;
}

and then just handle it accordingly in the business/domain object, e.g.

User user = userDAO.find(username, password);

if (user != null) {
    // Login?
}
else {
    // Show error?
}

If you actually expect only zero or many rows, then just do something like:

public List<User> list() throws SQLException {
    List<User> users = new ArrayList<User>();

    try (
        Connection connection = database.getConnection();
        PreparedStatement statement = connection.prepareStatement("SELECT id, username, email, birthdate FROM user");
        ResultSet resultSet = statement.executeQuery();
    ) {
        while (resultSet.next()) {
            users.add(new User(
                resultSet.getLong("id"),
                resultSet.getString("username"),
                resultSet.getString("email"),
                resultSet.getDate("birthdate")));
        }
    }

    return users;
}

and then just handle it accordingly in the business/domain object, e.g.

List<User> users = userDAO.list();

if (!users.isEmpty()) {
    int count = users.size();
    // ...
}
else {
    // Help, no users?
}

Operation not allowed after ResultSet closed in java

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;
}

How can I avoid ResultSet is closed exception in Java?

Sounds like you executed another statement in the same connection before traversing the result set from the first statement. If you're nesting the processing of two result sets from the same database, you're doing something wrong. The combination of those sets should be done on the database side.

Java and SQLite: Throwing ResultSet closed but proceeding

The only location where Error while processing the query: java.sql.SQLException: ResultSet closed could be printed to the console is in UserExists(..), unless there is another method with a similar catch block. Indeed the ResultSet is not used correctly in UserExists, what may cause the error.

For a more complete description of how to work with JDBC look at this answer or the JDBC documentation. A possible alternative to the existing UserExists is:

boolean userExists(String name, Connection conn) {
   PreparedStatement stmt = null;
   try{
      stmt = conn.prepareStatement("SELECT COUNT(Username) FROM User WHERE Username = ?");
      stmt.setString(1, name);
      ResultSet rs = stmt.executeQuery();
      rs.next();  // set cursor to first row
      int count = rs.getInt(1);
      rs.close();
      return count > 0;
   } catch(SQLException e) {
      // propagate error
      throw new RuntimeException(e);
   } finally {
      // clean up resources
      if (stmt != null) {
        try {
           stmt.close();
        } catch (SQLException ignore) {
           log("error on sql clean up", ignore);
        }
      }
   }
}

ResultSet closing immediately after executing query

Typical processing resultset iterator uses next method call, like the following:

  ResultSet rs = stmt.executeQuery(query);
  while (rs.next()) {

    //read record
  }

In your case make sure you are calling next before reading first record:

  ResultSet rs = stmt.executeQuery(query);
  if(! rs.next()) {
    return;
  }
  //read first reacord

   if(! rs.next()) {
    return;
  }
  //read second reacord

Closing ResultSet twice throws an exception

As already indicated in the comments, you are not calling ResultSet.close(), but calling ResultSet.getStatement() followed by a Statement.close() instead.

And that call to resSet.getStatement() is what throws the exception, as is required by the JDBC spec/api doc:

Throws:
SQLException - if a database access error occurs or this method is called on a closed result set

(emphasis mine)

ResultSet closed after executeQuery

You need to call ResultSet.next before reading the firs row (which may not exist).

In this case "closed" is as in not open, not as in was open but no longer.

@Marged points out you code is subject to SQL injection. In this case you may get away with it depending upon how the selection model was derived, nevertheless use PreparedStatement with a constant string. Also use try-with-resource or the Execute Around idiom to ensure your clean up resources whether or not there is an exception..

jdbc ResultSet closed

Your resultset probably didn't have any record, which is the reason why next() closed it.

next() returns a boolean, check it.

---

Related Topics