While Loop Terminating after ResultSet.execute

Question

I am trying to make TABLE COMPB equivalent to TABLE COMPA by Update and Delete operations on Table COMPB based on Table COMPA. The code works perfectly fine while generating proper SQL. Problem is, the moment I put execute statement as below, while loop terminates in first iteration itself. Not able to understand this behavior of ResultSet in java. Any help here? Where I am going wrong?

//Comparision between Minus Table(seta) and COMPB (setc)
        //ID Contains Primary Key of COMPB Table
        ResultSet seta=stmt.executeQuery("select * from COMPA minus select * from COMPB");


        while(seta.next())
        { 
            String insert="";
            String update="";
            boolean contains=ArrayUtils.contains(ID, seta.getInt(1));

            if (contains==true)
             { 
                update="Update COMPB SET COMPB.EMPNAME='"+seta.getString(2)+"',COMPB.EMAILID='"+seta.getString(3)+"' where "+seta.getInt(1)+"=COMPB.EMPID";
                stmt.executeUpdate(update);
                System.out.println(update);
             }
            else 
             {
            insert="Insert INTO COMPB values ("+seta.getInt(1)+" , '"+seta.getString(2)+"' , '"+seta.getString(3)+"')";
              stmt.executeUpdate(insert);
              System.out.println(insert);
             }  


        }

Answer 1

There can be two possible reasons for this:

• Re-use of same stmt object : Result set object is mapped to statement that returned it (via executeQuery method), and re-using the same statement object in the middle of iteration will make resultset invalid. You should create a new statement object and execute those queries with it.
• Using CONCUR_READ_ONLY resultSet (default) : Here's Oracle's documentation on result set. By default, it is not modifiable. You may need to use CONCUR_UPDATABLE resultset if records need modifying. Here is an example of that.

Answer 2

Not able to understand this behavior of ResultSet in java.

You use the same PreparedStatement stmt instance to perform the update :

ResultSet seta=stmt.executeQuery("select * from COMPA minus select * from COMPB");

while(seta.next()){ 
    ...

   update="Update COMPB SET COMPB.EMPNAME='"+seta.getString(2)+"',COMPB.EMAILID='"+seta.getString(3)+"'    where "+seta.getInt(1)+"=COMPB.EMPID";
   stmt.executeUpdate(update);
   System.out.println(update);
   ...
  }

Your ResultSet seta comes from PreparedStatement stmt instance.
So I suppose that invoking another execute method on a PreparedStatement instance has side-effect on previously ResultSet object returned by the PreparedStatement instance.

You should create a new instance of PreparedStatement to execute your other queries.

Answer 3

As per suggestions below, I changed the loop by creating preparedstatement objects.

         while(seta.next())
        { 

            boolean contains=ArrayUtils.contains(ID, seta.getInt(1));

            if (contains==true)
             { 


                PreparedStatement update_stmt =con.prepareStatement("Update COMPB SET COMPB.EMPNAME=?,COMPB.EMAILID=? where COMPB.EMPID=?");
                update_stmt.setInt(3,seta.getInt(1));
                update_stmt.setString(1,seta.getString(2));
                update_stmt.setString(2,seta.getString(3));
                int k=update_stmt.executeUpdate();  
                System.out.println(k+" records updated");

             }
            else 
             {

            PreparedStatement insert_stmt =con.prepareStatement("insert into COMPB values(?,?,?)");
            insert_stmt.setInt(1,seta.getInt(1));
            insert_stmt.setString(2,seta.getString(2));
            insert_stmt.setString(3,seta.getString(3));
            int k=insert_stmt.executeUpdate();  
            System.out.println(k+" records inserted"); 

             }  


        }