I have a problem when removing elements from a list mapped as described above. Here is the mapping:
@Entity @Table( name = "foo") class Foo { private List bars; @OneToMany @OrderColumn( name = "order_index" ) @JoinTable( name = "foo_bar_map", joinColumns = @JoinColumn( name = "foo_id" ), inverseJoinColumns = @JoinColumn( name = "bar_id" ) ) @Fetch( FetchMode.SUBSELECT ) public List getBars() { return bars; } }
Inserting Bar-instances and saving the Foo works fine, but when I remove an element from the list and save again, the unique constraint on bar_id in the mapping table is violated. The following SQL-statements are issued by hibernate, and these look quite odd:
LOG: execute : delete from foo_bar_map where foo_id=$1 and order_index=$2 DETAIL: parameters: $1 = '4', $2 = '6' LOG: execute S_5: update foo_bar_map set bar_id=$1 where foo_id=$2 and order_index=$3 DETAIL: parameters: $1 = '88', $2 = '4', $3 = '0' ERROR: duplicate key value violates unique constraint "foo_bar_map_bar_id_key"
The error perfectly makes sense, given the statements generated by Hibernate (there are five items in the list, I remove the first one and Hibernate deletes the mapping row with the LAST index and the tries to updates the remaining ones, starting with the first).
What is wrong with the mapping above?
Best Answer
Your mapping is totally valid and works with EclipseLink as JPA 2.0 implementation (without the
Fetch
annotation of course), but indeed fails with Hibernate.Here is the DDL with Hibernate:
So let's say
Foo#1
holds a list withBar#1
,Bar#2
,Bar#3
, the join table contains:When removing, say the first item from the list, Hibernate first
delete
the last row (WTF?) from the join table:And then tries to
update
thebar_id
column in the join table instead of theorder_index
(WTF!?) to reflect the "new" ordering of the items in the list. First (schematically):where the next step would result in:
Obviously, this approach doesn't sound right and doesn't work because of the
unique
constraint onbar_id
. More generally, why the hell does Hibernate mess with thebar_id
instead of updating theorder_index
column?I consider this to be an Hibernate bug (reported as
HHH-5694, see HHH-1268 now).