Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Agile forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Batch Processing of Queries with PreparedStatement

 
Shashank Hiwarkar
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Everybody,
I am using the batch processing with Statement which is working fine.
But when I try to use the PreparedStatement instead of Statement, rows are not getting affected, can anybody explain me why?
I am attaching my code snippet.

PreparedStatement pstmt=con.prepareStatement("update shashank_experiment set column2='abc2' where column1=?");
for(int i=0;i<3;i++){
pstmt.clearParameters();
pstmt.setInt(1,i);
pstmt.addBatch();
}
int[] updated_cnt=pstmt.executeBatch();
 
Shashank Hiwarkar
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can anybody help me out in this regards
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
is an exception being thrown? Your code should be updating all rows where column1 IN ( 0, 1, 2 ) and column2 = 'abc2' barring any exceptions being thrown, or you not committing the updates. Also, using the clearParameters() method is redundant since it is called in the setXXX() method if it has been previously set.
 
Shashank Hiwarkar
Ranch Hand
Posts: 32
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
is an exception being thrown? Your code should be updating all rows where column1 IN ( 0, 1, 2 ) and column2 = 'abc2' barring any exceptions being thrown, or you not committing the updates.
Actually when we use update query, if at all there are zero rows updation then also it will not throw the exception. Above program does not give the exception.... And I am using the
conn.autoCommit(true)... no question of not committing.......
 
Jamie Robertson
Ranch Hand
Posts: 1879
MySQL Database Suse
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"Actually when we use update query, if at all there are zero rows updation then also it will not throw the exception."
that seems odd, are you sure you are not just catching it and doing nothing with it?
"And I am using the
conn.autoCommit(true)... no question of not committing......."
This seems odd too. Usually, autocommit should be off.
From the Oracle Docs:
"Notes[on batching]:
Do not mix standard update batching syntax with Oracle update batching syntax in the same application. The Oracle JDBC driver will throw exceptions when these syntaxes are mixed.
Disable auto-commit mode if you use either update batching model. In case an error occurs while you are executing a batch, this allows you the option of committing or rolling back the operations that executed successfully prior to the error."
You may not be using Oracle, but your DB driver probably recommends the same.
Jamie
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic