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


        }

I imagine it doesn't like you reusing `stmt` for the subsequent `executeUpdate()` calls, but what debugging have you done? What does the initial query get if you run it manually - assuming all changes in all sessions are committed? Also, why use loops instead of a single merge statement; and if you must use loops, why aren't you using prepared statements and bind variables? — Alex Poole, Jan 08 '17 at 20:37
Thanks Alex. Reuse of stmt within a loop was the issue. I am yet to understand how can it break the while loop. But for now I corrected it. — Hari Prasad, Jan 09 '17 at 09:36
I strongly recommend that you stop concatenating values into a query string, use a prepared statement with parameter placeholders instead. Your current code is not safe and vulnerable to SQL injection. — Mark Rotteveel, Jan 09 '17 at 09:41
@MarkRotteveel & all:Thanks for all the experienced suggestions and spending time to go through my small query, in spite of your busy schedule. Motivates me a lot! — Hari Prasad, Jan 09 '17 at 13:55

Darshan Mehta · Answer 1 · 2017-01-09T10:05:52.653

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.

score 1 · Answer 2 · answered Jan 08 '17 at 20:43

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.

The code shown uses a normal `Statement` object, but the gist of your answer is right. — Mark Rotteveel, Jan 09 '17 at 09:40

Hari Prasad · Answer 3 · 2017-01-09T16:13:28.407

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"); 

             }  


        }

While Loop Terminating after ResultSet.execute

3 Answers3