Only Last Update query working in addBatch of JAVA

Question

I am using the following code

con.setAutoCommit(false);

for(int i=0;i<requestBody.size();i++)
{
    bulk_Update_Qry = new StringBuffer();

    if (requestBody.getUserDetails().get(i).getFirstName() != null) 
        dbutil.setField(bulk_Update_Qry, "FIRST_NAME",requestBody.getUserDetails().get(i).getFirstName());
    if (requestBody.getUserDetails().get(i).getLastName() != null)
        dbutil.setField(bulk_Update_Qry, "LAST_NAME",requestBody.getUserDetails().get(i).getLastName());
    if (requestBody.getUserDetails().get(i).getPhone() != null)
        dbutil.setField(bulk_Update_Qry, "PHONE",requestBody.getUserDetails().get(i).getPhone() );
    if (requestBody.getUserDetails().get(i).getEmail() != null)
        dbutil.setField(bulk_Update_Qry, "EMAIL",requestBody.getUserDetails().get(i).getEmail());
    if (requestBody.getUserDetails().get(i).getAddress()!= null)
        dbutil.setField(bulk_Update_Qry, "ADDRESS",requestBody.getUserDetails().get(i).getAddress());
    if (requestBody.getUserDetails().get(i).getZip() != null)
        dbutil.setField(bulk_Update_Qry, "ZIP",requestBody.getUserDetails().get(i).getZip() );
    if (requestBody.getUserDetails().get(i).getCity() != null)
        dbutil.setField(bulk_Update_Qry, "CITY",requestBody.getUserDetails().get(i).getCity() );
    if (requestBody.getUserDetails().get(i).getState() != null)
        dbutil.setField(bulk_Update_Qry, "STATE",requestBody.getUserDetails().get(i).getState());
    if (requestBody.getUserDetails().get(i).getCountry() != null)
        dbutil.setField(bulk_Update_Qry, "COUNTRY",requestBody.getUserDetails().get(i).getCountry());

    System.out.println("UPDATE CINR_USER SET " + bulk_Update_Qry + " WHERE ID = \'" + requestBody.getUserDetails().get(i).getId() + "\'");
    ps = con.prepareStatement("UPDATE CINR_USER SET " + bulk_Update_Qry + " WHERE ID = \'" + requestBody.getUserDetails().get(i).getId() + "\'");

    ps.addBatch();
}
ps.executeBatch();
con.commit();

This is a dynamic update query.

setField is a function that I defined to verify if it's present in the request or not.

Problem that I'm facing

If there are 5 update queries, only the 5th query is being executed. I am not sure what's happening to the first four queries.

Also I cannot afford to have

ps = con.prepareStatement(.....)

outside the for loop as I am using a dynamic update query.

Could anyone clarify what I am doing wrong?

Answer 1

In each loop you are creating a new PreparedStatement object, and the batch is per statement object. In other words, every loop you throw away the batch of the previous loop, at the end you have a prepared statement that has a batch containing only one statement, so you execute only the statement defined in the final run of the loop.

There are several ways to solve this:

1. Use a plain Statement object created outside of the loop.
   This would work because you aren't actually using the primary feature of prepared statements, however this is also unsafe. The way you are currently constructing statements leaves you wide open to SQL injection.
2. Don't try to use batches and just execute the statement inside the loop.
   But before doing this, you will need to get your act together and correctly parameterize your queries.
3. Define multiple prepared statements with correctly parameterized queries before the loop, and add a batch to the right statement, execute all those queries after the loop.