@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?

1) Have you created equals & hashCode in the Item class? 2) Is it a 2-way association? (from your class to Item) and 3) Please include the code you are using to delete the item. — mavroprovato, Nov 23 '12 at 14:21
The error states that your UPDATE query results in a duplicate primary-key which cannot be allowed in the database .. this either happens if the update set an existing primary-key to another record, or if the update condition apply to more than 1 record. — Khaled.K, Dec 06 '15 at 11:19
I think this already got answered here: http://stackoverflow.com/questions/4022509/constraint-violation-in-hibernate-unidirectional-onetomany-mapping-with-jointabl It's not exactly the same mapping but the problem with Hibernate seems to be the same. — Jimmy T., Dec 23 '15 at 11:04

score 1 · Answer 1 · answered Jun 18 '16 at 16:48

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.

score 0 · Answer 2 · answered Mar 04 '16 at 14:27

Maybe one option could be change the order annotation and use:

@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) })
@org.hibernate.annotations.Sort(type = SortType.COMPARATOR, comparator = ItemComparator)
public List<Item> getItems() {
return items;
}

https://dzone.com/articles/sorting-collections-hibernate

Check performance solution because maybe is too slow for big amount of data, and if you can share if was a possible solution would be great to know

@OrderColumn generates a request to update the primary key

2 Answers2