Getting java.sql.SQLException: Operation not allowed after ResultSet closed

Question

When I execute the following code, I get an exception. I think it is because I'm preparing in new statement with he same connection object. How should I rewrite this so that I can create a prepared statement AND get to use rs2? Do I have to create a new connection object even if the connection is to the same DB?

    try      {         //Get some stuff         String name = "";         String sql = "SELECT `name` FROM `user` WHERE `id` = " + userId + " LIMIT 1;";         ResultSet rs = statement.executeQuery(sql);         if(rs.next())         {             name = rs.getString("name");         }          String sql2 = "SELECT `id` FROM  `profiles` WHERE `id` =" + profId + ";";         ResultSet rs2 = statement.executeQuery(sql2);         String updateSql = "INSERT INTO `blah`............";          PreparedStatement pst = (PreparedStatement)connection.prepareStatement(updateSql);              while(rs2.next())          {              int id = rs2.getInt("id");             int stuff = getStuff(id);              pst.setInt(1, stuff);             pst.addBatch();          }          pst.executeBatch();      }      catch (Exception e)      {         e.printStackTrace();     }  private int getStuff(int id) {      try     {                 String sql = "SELECT ......;";             ResultSet rs = statement.executeQuery(sql);              if(rs.next())             {                 return rs.getInt("something");              }             return -1;     }//code continues 

Answer 1

The problem is with the way you fetch data in getStuff(). Each time you visit getStuff() you obtain a fresh ResultSet but you don't close it.

This violates the expectation of the Statement class (see here - http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html):

By default, only one ResultSet object per Statement object can be open at the same time. Therefore, if the reading of one ResultSet object is interleaved with the reading of another, each must have been generated by different Statement objects. All execution methods in the Statement interface implicitly close a statment's current ResultSet object if an open one exists.

What makes things even worse is the rs from the calling code. It is also derived off-of the statement field but it is not closed.

Bottom line: you have several ResultSet pertaining to the same Statement object concurrently opened.