Commit every x inserts/updates question

[davidcnoel]

I'm not sure what is the best way to do this.

In my business logic I have a query which could return over 100,000 rows from a database. I want to iterate over these objects, modify them and update them back to the database. Doing 100,000 updates in one transaction is not possible and doing every update in it's own transaction is also not possible. For performance reasons I want to commit updates in groups of 10,000 or so. My question then is, what is the best way to do this with Spring and Hibernate? I would like to use declarative transactions if possible. Do I have to implement my own PlatformTransactionManager? Is there some way I can place a setting on my datasource in a similiar way to autocommit is set on a datasource? Am I forced to use programatic transactions and keep a counter running in my app and do the commit when it reaches 10,000 updates?

I'm hoping it is a feature that is already supported and I am just unware of how to use it.

Any ideas?

Thanks

Dave

[Costin Leau]

I'm hoping it is a feature that is already supported and I am just unware of how to use it.

See Hibernate reference documentation, mainly:
http://www.hibernate.org/hib_docs/v3...l#batch-direct
http://www.hibernate.org/hib_docs/v3...#queryhql-bulk
http://www.hibernate.org/hib_docs/v3...rformance.html

[davidcnoel]

Thanks for the reply costin but I think we are talking about different things. I am not trying to do a bulk update. I actually have 100,000 unique updates that need to be performed. What I need to be able to do is control when the commits occur. I would like to be able to do them every x rows. I know I can get it to work through programmatic transaction management but I would like to use a declarative approach if possible.

[Costin Leau]

The links are good because they show how to store a lot of things inside the database without running into an OOM error.

What I need to be able to do is control when the commits occur.

You can do that by setting the FlushMode on your working session.
Do something like:

session.setFlushMode(FlushMode.NEVER);
// loop with all your updates here
// place into session the x objects to be saved
session.flush();
session.evict();