PreparedStatment batchExecute only excecuted the last batch

Question

i am having trouble with the prepared statement batch excecuter:

try{
    while (operatorsQuery.next()) {
                phone = Integer.toString(operatorsQuery.getInt(1));
                prepStmt1 = connBlng.prepareStatement("update table set done='yes' where phone=?");

                prepStmt1.setString(1, phone);
                prepStmt1.addBatch();
    }
    prepStmt1.executeBatch();
} catch(Exception e){
               e.printStackTrace();
} finally{
        closeStatmentandRS(operatorsQuery, prepStmt1);
}

and for some reason it only updates the last batch(last phone).

why is that happening?

Woot4Moo · Accepted Answer · 2013-06-19T11:39:51.360

prepStmt1 = connBlng.prepareStatement("update table set done='yes' where phone=?");
prepStmt1.setString(1, phone);
prepStmt1.addBatch();

you are invalidating the previous reference to prepStmt1, therefore the batch will only ever have the last element you tried to process.

What you want is this:

prepStmt1 = connBlng.prepareStatement("update table set done='yes' where phone=?");  
while(...)  
{  
     prepStmt1.setString(1, phone);
     prepStmt1.addBatch();
}

The fix is to only assign the parametrized SQL statement once and to flip the parameters on each pass. Similar to how you would write a function and just change the inputs to it via a parameter list.

@susparsy thanks, please remember to accept if this is the answer so that others who have a similar problem can use the reference. — Woot4Moo, Jun 19 '13 at 11:40

PreparedStatment batchExecute only excecuted the last batch

1 Answers1