Operation not allowed after ResultSet closed?

Question

I understand that this problem occurs when ResultSet can only be open once at a time. So I added UPDATE

try
{          
  String sql = "SELECT * FROM test;
  ResultSet rs = stmt.executeQuery(sql);

  if(rs.next()){
    do {
        if(rs.getString("sent").equals("0")) {
            try {

                //update

                try {
                    String psSQL = "UPDATE sent SET ..........";
                    updateStatement = conn.prepareStatement(psSQL);
                    updateStatement.setString(1, ....);
                    updateStatement.setString(2, ....);
                    updateStatement.setString(3, ...);

                    updateStatement.executeUpdate();
                    System.out.println("Record updated");
                } catch (SQLException e) {
                    System.out.println(e.getMessage());
                } finally {
                    if(updateStatement != null) {
                        updateStatement.close();
                    }
                    if (conn != null) {
                        conn.close();
                    }
                }

            } catch (MessagingException mex) {
             mex.printStackTrace();
            }
        }
    } while(rs.next()); **<---- Still same Error here**
  } else if (rs.getRow() == 0) {
    if (!mailSender.equals(".....")) {
        try{
                //insert

             try {
                    String psSQL2 = "INSERT INTO sent"
                            + "(......) VALUES"
                            + "(?,?,?)";
                    insertStatement = conn.prepareStatement(psSQL2);
                    insertStatement.setString(1, ...);
                    insertStatement.setString(2, ....);
                    insertStatement.setString(3, ....);

                    insertStatement.executeUpdate();
                    System.out.println("Insert done!");
             } catch (SQLException e) {
                System.out.println(e.getMessage());
             } finally {
                if(insertStatement != null) {
                    insertStatement.close();
                }
                if (conn != null) {
                    conn.close();
                }
             }                 
          }catch (MessagingException mex) {
             mex.printStackTrace();
          }
    } else {
        return null;
    }
  }
}
catch (SQLException se)
{
  se.printStackTrace();
}
catch (Exception e)
{
  e.printStackTrace();
}
finally
{
  try
  {
    if (stmt != null) {
      stmt.close();
    }
  }
  catch (SQLException se2) {}
  try
  {
    if (conn != null) {
      conn.close();
    }
  }
  catch (SQLException se)
  {
    se.printStackTrace();
  }
}

So.. Im getting errors when while(rs.next()) runs. I put in a system.out.println before and after rs.next() while loop and nth sows after that while loop.

I tried to use another resultset rs2 but it is not working either so I have no idea here does this problem come from..

Answer 1

A better way of doing what you want is using a single ResultSet

  String sql = "SELECT * FROM test";
  ResultSet rs = stmt.executeQuery(sql);

  if(rs.next()){
      do {

        if (rs.getString("sent").equals("0")) {

          try{
             //if there is a row and it is equals to 0.. so update it to 1.. 

             String sql2 = "UPDATE test SET test='1' WHERE test=....";
             Statement updateStatement = <create statement here>;

             int rs3 = updateStatement.executeUpdate();
             updateStatement.close();
          }catch (SQLException sqlex) { 
             sqlex.printStackTrace();
          }
        }
     }while(rs.next());
   }else {
      try{
             //insert if i couldn't get any rows in the table
             String sql2 = "INSERT INTO test......";
             Statement insertStatement = <create statement here>;
             int rs3 = insertStatement.executeUpdate();
             insertStatement.close();
          }catch (SQLException sqlex) {
             sqlex.printStackTrace();
          }
    }

    rs.close();
    stmt.close();
    conn.close(); 

Hope this helps.

Answer 2

A ResultSet is connected to the Statement that created it. By reusing the Statement you're closing the first ResultSet.

You need to use a second Statement for the second ResultSet, if you want your code to work.