aspose file tools*
The moose likes JDBC and the fly likes Batch consisting of multiple PreparedStatements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Batch consisting of multiple PreparedStatements" Watch "Batch consisting of multiple PreparedStatements" New topic
Author

Batch consisting of multiple PreparedStatements

Sol Mayer-Orn
Ranch Hand

Joined: Nov 13, 2002
Posts: 311
Hi,

Is there a way to create a single batch, with each line being generated by a PrepareStatememt, rather than manual string concatenation ?
I don't think it would do to use PreparedStatement.addBatch(), because this would enforce a uniform structure on all lines...

For example, suppose I'd like the following 4 lines to execute as a single batch. Do I have to concatenate them manually, or is there a way to use PreparedStatements (one PreparedStatement for "update", and one for "insert):

--------- start batch --------
- update TAB set salary=1000 where id=1
- update TAB set salary=2000 where id=2
- insert into TAB values(3,3000)
- insert into TAB values(4,4000)
--------- end batch ---------

Thanks
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30537
    
150

Sol,
What do you mean by "manual string concatenation" ?


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Sol,
Perhaps the following will be of help (assuming you haven't already seen it):

http://java.sun.com/docs/books/tutorial/jdbc/jdbc2dot0/batchupdates.html

Good Luck,
Avi.
Sol Mayer-Orn
Ranch Hand

Joined: Nov 13, 2002
Posts: 311
Thanks for replying.

What I meant was, I'd like to create a batch that consists of PreparedStatemtns.

It's *easy* to create a batch if you represent each line as a *stirng*:
batchStmt.addBatch("update TAB set salary=1000 where id=1");
batchStmt.addBatch("update TAB set salary=2000 where id=2");
batchStmt.addBatch("insert into TAB values(3000)");
batchStmt.addBatch("insert into TAB values(4000)");

But i'd like each line to be a preparedStatemet, which would look something like:


I know it can be done if all 4 lines have the exact same structure (e.g. if all 4 lines where "insert"), but I couldn't find a way to do it when there's mixed structure (e.g. 2 "inserts" and 2 "updates")...

Thanks
Avi Abrami
Ranch Hand

Joined: Oct 11, 2000
Posts: 1132

Sol,
You said:

I couldn't find a way to do it when there's mixed structure

That's because it doesn't exist! You can only associate precisely one SQL statement with a single instance of "PreparedStatement". Guess you'll have to find a workaround -- like maybe implementing this functionality yourself (probably by creating some "wrapper" class for "PreparedStatement").

Good Luck,
Avi.
Sol Mayer-Orn
Ranch Hand

Joined: Nov 13, 2002
Posts: 311
Thank you very much.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Batch consisting of multiple PreparedStatements