@OrderColumn generates a request to update the primary key

Question

I use a list. The list is comprised of a compound primary key which is also used for sorting the list. The problem is that if I delete an element in the list (key compound), annotation @OrderColumn generates a request to update a primary key, and the cost rises an exception of type:

[26-05-2011 10:34:18:835] WARN  org.hibernate.util.JDBCExceptionReporter  - SQL Error: 1062, SQLState: 23000  
[26-05-2011 10:34:18:835] ERROR org.hibernate.util.JDBCExceptionReporter  -Duplicate  entry '10-10' for key 'PRIMARY'  
[26-05-2011 10:34:18:835] ERROR org.hibernate.event.def.AbstractFlushingEventListener  - Could not synchronize database state with session org.hibernate.exception.ConstraintViolationException: Could not execute JDBC batch update  

Here is the definition of the mapping :

@ManyToMany(cascade=CascadeType.ALL, fetch = FetchType.LAZY)
@JoinTable(name = "chapter_item", joinColumns = { @JoinColumn(name = "chapter_id", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "item_id", nullable = false, updatable = false) })
@OrderColumn(name="iorder")
public List<Item> getItems() {
    return items;
}

Here is the update query where I have a problem:

Hibernate: 
update
    chapter_item 
set
    item_id=? 
where
    chapter_id=? 
    and iorder=?  

I wonder if this is a known bug, and if anyone has a solution?

Answer 1

Regarding @OrderColumn, following documentation is found at http://docs.oracle.com/javaee/6/api/javax/persistence/OrderColumn.html

Specifies a column that is used to maintain the persistent order of a list. The persistence provider is responsible for maintaining the order upon retrieval and in the database. The persistence provider is responsible for updating the ordering upon flushing to the database to reflect any insertion, deletion, or reordering affecting the list.

So we see that the persistence provider i.e. hibernate is responsible for updating the column named iorder. It is also said that:

The OrderColumn annotation is specified on a OneToMany or ManyToMany relationship or on an element collection. The OrderColumn annotation is specified on the side of the relationship that references the collection that is to be ordered. The order column is not visible as part of the state of the entity or embeddable class.

Please take note of the sentence that says:

The order column is not visible as part of the state of the entity or embeddable class.

So, may I suggest you to consider not selecting the column iorder for @OrderColumn since it is a part of your composite key and hibernate is sure to update this value when you delete or insert an element in list (List<Item>).

Hope, this helps.