• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Why Batch Update Support Only One Single PreparedStatement

 
James Gordon
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

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.

Any comments?

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)


Is it save to do so?


Thanks in advance.
 
Afroz Ahmed
Ranch Hand
Posts: 64
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hai James,

Now executeUpdate()returns the number of rows updated but you don't know exactly how many rows were updated for each table.
 
Peter den Haan
author
Ranch Hand
Posts: 3252
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by James Gordon:
This is what I gathered so far regarding JDBC 2.0 batch update:
That looks correct to me. It's worth stating explicitly that each update in the PreparedStatement batch can have different values for the parameters.
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.
I'd agree. Does it really need to be a single batch?
Or maybe another way round is to combine my SQL statements in one single PreparedStatement
Not all JDBC drivers support this; and if it works, you might be able to get detailed results for each item using getMoreResults()/getUpdateCount()/getResultSet() but it's slightly painful

- Peter
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Jules
 
James Gordon
Ranch Hand
Posts: 106
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

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.


Thanks.
 
Julian Kennedy
Ranch Hand
Posts: 823
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Jules
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic