Hibernate order of [update, delete] on collection is breaking a DB constraint. How to fix?

Hiber开发者_C百科nate is causing pain merging an object that contains an ordered collection when removing an element from the middle of the collection.

Some background: I'm working on a tool that produces CSV reports. In my model I want a report object to have column objects whose order is dictated by a column.orderIndex int value.

The problem comes when I remove a column whose orderIndex isn't the last in the list. When executing merge(), Hibernate runs the following SQL:

update REPORT_COLUMNS set [...], orderIndex=1 where pkey=2 --This is formerly the 2nd column, and now it's orderIndex is going to collide with the orderIndex of the 1st 
delete from REPORT_COLUMNS where pkey=1 --This is the 1st column, which is being removed

If Hibernate ran the deletion first, there wouldn't be a problem.

Here's how the collection is declared:

@OneToMany(fetch = FetchType.EAGER)
@Fetch(value = FetchMode.SUBSELECT)
@OrderBy("orderIndex")
@JoinColumn(name = "report", updatable = true, insertable = true, nullable = false)
@Cascade({ org.hibernate.annotations.CascadeType.DELETE,
    org.hibernate.annotations.CascadeType.DELETE_ORPHAN,
    org.hibernate.annotations.CascadeType.MERGE,
    org.hibernate.annotations.CascadeType.PERSIST,
    org.hibernate.annotations.CascadeType.REMOVE,
    org.hibernate.annotations.CascadeType.SAVE_UPDATE })
public List<ReportColumnImpl> getColumnImpls() {
    return columns;
}

Your help would be much appreciated.

The easiest solution is to make the constraint deferred, i.e. checked only at the end of the transaction. Oracle supports deferred constraints. Don't know about your database.

Else, you might remove the entity to delete, then flush the session, then update the orderIndexes. But you might also have problems if Hibernate tries to update the index of the 4th element before updating the index of the 3rd one.

Do you need to update the orderindex?

this collection of ints (1, 2, 3, 4, 5, 6, 7) could still be sorted into the same order if you removed 1 or a number of elements. e.g.

(1, 2, 5, 7)

if you do need to maintain an order why not create a ReportColumn that stores a references to next and previous column (much like a linked list) and then when you remove one update the elements either side of it?