An Encounter with JDBC Batch

In this QuickPeek section I am going to share my quick & sharp experiences with some trivial issues, situations & new technologies which we come across in our day to day work.

This post is about a issue, which I believe, most often we face it & when we discover the cause,
we really got to say "How could I miss this!!!!!!", that is exactly what I uttered after an hour of
ransacking the code.

Let me first describe the scenario:
I was working on an application, a module, which does some series of repetative JDBC operation,
like updates and inserts on a single table. So, to perform the operation, I thought of using
JDBC Batch instead of executing each statements individually.

The code prototype may be thought of as:

for(int i = 0; i<some_value;i++ )
{
 Statement stmt = some_prepared_statement_is_being_created_out_of_some_SQL;
 stmt.setString(1,value1);
 stmt.setInt(2,value2);
 
 stmt.addBatch();
}

stmt.executeBatch();

Now, when I was executing the above code block, only the last statement was getting executed in DB. This situation, really left me puzzled and for some time, believe me!!!! I could not figure out anything wrong with the above piece of code.
Then after a brief break, I realized that everytime a new statement is created inside the loop and that statement is added to the batch, and at the at the end of the loop, only that one was getting executed. So this was the main cause behind this problem.

On moving the Statement creation outside the loop, made everything look and work good, cause at that time,only the statements (PreparedStatement) placeholder(i.e. "?" if any) are being changed and the same statement is added to the batch.

Please share your views and comments and till next time keep coding........ keep sharing.