Problem Title : Constraint violation in Hibernate unidirectional ManyToMany mapping when removing elements from a List.
I have a problem deleting an item from the list . This seems to be connected to the way hibernate is performing
delete/update queries on the database .
I have two tables box and msg and many-to-many mapping table in database as boxmsg.
I have unidirectional mapping from com.Box which contains list of messages.
When I try to delete one item from the middle of the list it gives following error .
Code:
duplicate key value violates unique constraint "boxmsg_pkey"
Detail: Key (customerid, msgid)=(1049, 4761) already exists.
It appears that Hibernate deletes the LAST INDEX element and then update the remaining elements .
Scenario belowI try to delete 4744 which is at index 2 as shown below
Code:
customerid msgid listindex
1049 4730 0
1049 4731 1
1049 4744 2
1049 4761 3
1049 4828 4
Hibernate queries
I debugged the hibernate queries and looks like following .
delete from boxmsg where customerid=1049 and listindex=4
update boxmsg set msgid=4761 where customerid=1049 and listindex=2
Hibernate Mapping Code:
<hibernate-mapping>
<class
name="com.Box"
table="box">
<id
name="customerId"
column="customerid"
type="long">
<generator class="assigned" />
</id>
<version
name="version"
column="version"
type="long" />
<list
name="msgs"
cascade="all"
lazy="false"
fetch="join"
table="boxmsg">
<key
column="customerid"
not-null="true" />
<list-index column="listindex" />
<many-to-many
class="com.Msg"
column="msgid" />
</list>
</class>
</hibernate-mapping>
I tried a workaround as following and it works fine.
Code:
List<Msg> initialBoxMsgs = new ArrayList<Msg>();
// Copy all the elements into a new List
initialBoxMsgs.addAll(box.getMsgs());
// clear the original list
box.getMsgs().clear()
// .... remove the required element from this list
// Set this in the original list
box.setMsgs(initialBoxMsgs);
In above approach Hibernate runs a delete on the entire boxmsg table
and then insert all the elements in to the database . However, this could lead to serious performance implications
hence I am not using this approach .
Similar problem already mentioned here long ago
http://stackoverflow.com/questions/4022509/constraint-violation-in-hibernate-unidirectional-onetomany-mapping-with-jointabl
I am even thinking of using some other type of collection but can't use Set and I really need the order in which msgs
are being inserted in the database.
I tried looking at LinkedHashSet but may be there are problems in that too .
https://forum.hibernate.org/viewtopic.php?f=1&t=942105&p=2468411#p2468411
Any pointers would be very helpful.
Login
FAQ
Search