GeeCON Prague 2014*
The moose likes JDBC and the fly likes Batch Update question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Batch Update question" Watch "Batch Update question" New topic
Author

Batch Update question

Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Hi all,

I am using Prepared Statements batchupdate/execute batch methods to insert multiple records into the database.

first I have conn.setautocommit(false) and then i am adding objects and finally executing update,

My question is if I get a batchUpdate exception, is there any way for me to commit all other rows and just rollback the affected row which had an exception ?

Thanks
Atul
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


My question is if I get a batchUpdate exception, is there any way for me to commit all other rows and just rollback the affected row which had an exception ?

No. You can only rollback to the point you started your transaction. If this is the entire batch, which is usually a bad idea, then its all or nothing. I say the entire batch is a bad idea because if this batch is of any reasonable size you are going to eat up database resources maintaining all this data that could be thrown away.

COuld you not do your batch in chunks? Is there a reason you want to wrap the whole batchin one transaction?


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
If this is the entire batch, which is usually a bad idea, then its all or nothing. I say the entire batch is a bad idea because if this batch is of any reasonable size you are going to eat up database resources maintaining all this data that could be thrown away.

COuld you not do your batch in chunks? Is there a reason you want to wrap the whole batchin one transaction?


I am already working on chunks. The chunks are like working on each state. The maximum number of inserts can be 700 [ on an average].

Since this is not possible, I am trying to check with the PKs and see whether the objects exist in the database. If not I am inserting it. That seems to be doing okay so far.

Also I am double checking any nulls.

Thanks
Atul
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
I have a question while using bulk inserts/ bulk update

How do I do batch commits ?

Like if i have 10000 records to be inserted, how will I say to commit every 1000 transactions.

Thanks
Atul
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

You can use a loop to do this.
tim fox
Greenhorn

Joined: Oct 18, 2009
Posts: 4
You need to look at the addbatch() method. I think it will do what you're looking for,

Good luck.

TGF
Atul Mishra
Ranch Hand

Joined: Jun 08, 2006
Posts: 140
Paul,

Could you please give me some more inputs ?
I tried using sublist method. looks like that is eating so much memory..

Again I want to commit at every 1000 transactions

Thanks
 
GeeCON Prague 2014
 
subject: Batch Update question