I have a question regarding multirow updates.

Here is my scenario:

I have a table called Policy that has two columns, name and item. The item colum is an int and is used to order the policies. It has a unique constraint.

What I want to be able to do in a web app is move a given policy up the order list.

So i am taking one policy's item value, call it x, and changing it to y. Assume x < y.

Because of the unique constraint, and the fact that there may be a policy with item value y already, I believe I need to do the following when i try to update the Policy:

i) For a Policy instance, get the old value of the Policy.item value by calling session.load(policy.id) to get the original version, then comparing the two policy.items.

ii) If he item has changed, update and persist the new item value to its negative (-y) to create a 'hole' in the item list. I do this by calling session.saveOrUpdate(policy) with the new Policy instance as an argument.

iii) Update all of the policy objects where policy.item is > x and <= y. This moves those policy instances down a item.

iv) update the new policy object to its correct value. i.e policy.setItem(y)
session.saveOrUpdate(policy)

My question is, what is the best way to perform the batch update at iii) Is it to call session.connection() and then use that to do a normal JDBC call. Or are there better Hibernate calls that could achieve the same functionality without recourse to JDBC...

Thanks in advance

Don't know anything in Hibernate for this, believe session.connection() is your only option.

Jeff