wood burning stoves 2.0*
The moose likes JDBC and the fly likes Why Batch Update Support Only One Single PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Why Batch Update Support Only One Single PreparedStatement" Watch "Why Batch Update Support Only One Single PreparedStatement" New topic
Author

Why Batch Update Support Only One Single PreparedStatement

James Gordon
Ranch Hand

Joined: Aug 09, 2002
Posts: 106
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

Joined: Jan 18, 2004
Posts: 64
Hai James,

Now executeUpdate()returns the number of rows updated but you don't know exactly how many rows were updated for each table.


The value of an idea lies in the usage of it.
Peter den Haan
author
Ranch Hand

Joined: Apr 20, 2000
Posts: 3252
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

Joined: Aug 02, 2004
Posts: 823
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

Joined: Aug 09, 2002
Posts: 106
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

Joined: Aug 02, 2004
Posts: 823
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
 
Don't get me started about those stupid light bulbs.
 
subject: Why Batch Update Support Only One Single PreparedStatement
 
Similar Threads
PreparedStatement in batched mode gives Exception
Batch consisting of multiple PreparedStatements
Html Input to oracle through servlet and JSP output
getGeneratedKeys() + batchUpdate
multiple prepared statemnets using executeBatch