When trying to reorder a List, I have the following error :

org.hibernate.exception.ConstraintViolationException: could not update collection rows: [com.netappsid.erp.server.bo.base.BaseDiscountGroup.details#3]

Here is a simple mapping of what I got :

@IndexColumn(name="sequence")
@Cascade(org.hibernate.annotations.CascadeType.DELETE_ORPHAN)
@OneToMany(fetch=FetchType.LAZY, cascade={CascadeType.ALL})
public List<DiscountDetail> getDetails()
{
return details;
}

Hibernate manage this relation automatically by populating the DiscountGroup_DiscountDetail table.

If I add a new item to the collection the update is done properly. In fact it is an insert, that is :
insert into DiscountGroup_DiscountDetail (DiscountGroup_id, sequence, details_id) values (?, ?, ?)

But if I modify the order, I end up with some update :
update DiscountGroup_DiscountDetail set details_id=? where DiscountGroup_id=? and sequence=?

Let's say I have the following data :

DiscountGroup_id, sequence, details_id
1, 0, , 1
1, 1, , 2
1, 2, , 0

If I try to change the order to that one (switching details_id 0 and 2) :

DiscountGroup_id, sequence, details_id
1, 0, , 1
1, 1, , 0
1, 2, , 2

Such change when persisting, will end up with these updates :

update DiscountGroup_DiscountDetail set details_id=0 where DiscountGroup_id=1 and sequence=1
update DiscountGroup_DiscountDetail set details_id=2 where DiscountGroup_id=1 and sequence=2

Since I have a unique constraint on the details_id, when updating doing the first update (details_id = 0), I end up with the following exception :

org.hibernate.exception.ConstraintViolationException: could not update collection rows: [com.netappsid.erp.server.bo.base.BaseDiscountGroup.details#3]

This is normal since updating the 2nd line details_id to 0 will conflict with currently existing 3rd row that also has 0 for the details_id value.

The solution would be to delete the entire table and refill it again. Maybe there is another way, but I can't find any other way to solve it.

Here is the complete stack trace if it can helps :

org.hibernate.exception.ConstraintViolationException: could not update collection rows: [com.netappsid.erp.server.bo.base.BaseDiscountGroup.details#3]
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:69)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.persister.collection.BasicCollectionPersister.doUpdateRows(BasicCollectionPersister.java:222)
at org.hibernate.persister.collection.AbstractCollectionPersister.updateRows(AbstractCollectionPersister.java:1337)
at org.hibernate.action.CollectionUpdateAction.execute(CollectionUpdateAction.java:55)
at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:243)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:227)
at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:143)
at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:296)
at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1009)
at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:356)
at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:106)
at org.hibernate.ejb.TransactionImpl.commit(TransactionImpl.java:38)





Thanks in advance to help me solve this problem.

Sonny

In one of my projects I dealt with list reordering successfully by manipulating the order field of the list element in addition to changing the order of elements in the list itself. This is a bit tricky because if the RDBMS update falls through and you roll back, you need to restore the original order of the list and the original values of the order field.

I hope this helps.

I already thought of that. I want my sequence to be managed by Hibernate to keep the database integrity.

I have the same problem here too. Does someone know the solution to this problem, will we have a fix in Hibernate soon ?

Thx

Francois

Two swap the position of record A with record B you could do this:

Make it a three step process.

1) update the record A to 100 (some temp value out of the domain )
2) update the record B to A's prev value
3) update the record A to B's prev value

Wrap this in a transaction and
possibly put "session.flush()" in between each step, it should
give you the desired results.

-JT

I don't know if it solves the original problem posted by SonnyD, but it will not help me out since I want to change multiple "rows". When on the "server" side, I will not know which item in the list has been changed unless I reload the previous version of the given entity and I don't want to make a special processing for each list I have in my application.

There should be a better way to solve it, is it not a bug in Hibernate ???

Why you are saying it's a bug with Hibernate? - this is a common "update" problem on a unique constraint. Whether the constraint is managed by hibernate or database is irrelavent IMHO.

As a matter of fact, this is one of the reasons that manufactured surrogate keys are recommended instead of Natural PK's. It makes updating unique constraints easier.

francois - have you considered doing a stored procedure...sounds like you case may require more fine-grain attention to the values.

Surrogate keys would not be helpful in my situation.

Doing a stored procedure is not a solution at all since I don't want to be database dependent and this is one of the main reason we are using hibernate, to forget about database specific things.

But for this problem, I have found a solution on another post, here is the link:
http://www.jboss.com/?module=bb&op=viewtopic&t=76437

The solution consist of using a JoinColumn annotation on both the master and the detail.

jt_1000 : Thanks anyway for the help.

Francois