File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Batch Update question

 
Atul Mishra
Ranch Hand
Posts: 140
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

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?
 
Atul Mishra
Ranch Hand
Posts: 140
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 140
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 10336
Eclipse IDE Hibernate Java
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use a loop to do this.
 
tim fox
Greenhorn
Posts: 4
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 140
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic