Win a copy of Svelte and Sapper in Action this week in the JavaScript forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Bear Bibeault
  • Junilu Lacar
Sheriffs:
  • Jeanne Boyarsky
  • Tim Cooke
  • Henry Wong
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • salvin francis
  • Frits Walraven
Bartenders:
  • Scott Selikoff
  • Piet Souris
  • Carey Brown

Batch Update question

 
Ranch Hand
Posts: 140
  • 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
 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • 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
  • 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
  • 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
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can use a loop to do this.
 
Greenhorn
Posts: 4
  • 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
  • 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
 
    Bookmark Topic Watch Topic
  • New Topic