This is what I gathered so far regarding JDBC 2.0 batch update: 1) If we have single repeating SQL for the batch, use PreparedStatement 2) If we have different SQL for the batch, we have to use Statement instead.
It happened that I need to process scenario (2) and I have no choice but to use Statement object.
To me, using Statement to execute SQL is not very secure and reliable as compared to PreparedStatement. Reason is that now we have to worried about escape characters, etc, etc.
Or maybe another way round is to combine my SQL statements in one single PreparedStatement, thus eliminating the need for batch update ie:
ps = conn.prepareStatement(UPDATE table A UPDATE table B)
If you're worried about network round trips, how about putting your update logic in a stored procedure and calling that?
Alternatively just use a separate PreparedStatement pattern for each UPDATE and batch each set of similar updates. Is the additional overhead really *that* significant?
Joined: Aug 09, 2002
Stored procedure maybe a good choice but don't think it fit well if we are building the SQL string dynamically, on Java side.
Executing separately as multiple PreparedStatements might not be that good in terms of performance but don't think I got much choice.
Joined: Aug 02, 2004
Where is the dynamic data for building your batched updates coming from? If it's coming from the database (and you're still concerned about performance) then why not just cut out the middle man and do it all in the sproc?
subject: Why Batch Update Support Only One Single PreparedStatement