It sounds (a little bit) weird or crazy to ignore a unique constraint, but in this use case it (hopefully) makes sense, it does to me
Our webapplication uses Spring Data JPA, JPA, Hibernate as persistence provider and SQL Server 2008 R2 as database.
We have this (simplified) entity
Our webapplication has a map representing the infrastructure of the sewerage system. Every part of the infrastructure is identified by a "type" and an "object id". When the user selects a part of the infrastructure, a SelectionItem is created using its type and object id and is persisted to the database. A user can select as many items as he wants. A user can view his selection, remove items or clear his selection completely. This selection will be used for other business tasks, so the selected items must be persisted in a database table (it's not an option to only store the selected items client-side).
The database table has a unique constraint on "type" and "object_id". Reason: an element is selected or not selected. If you add the same element seven times to the selection (using different user interface controls), the item should only be once in the database table. This approach simplifies other management tasks, e.g. deleting on id, no confusion about having the same item 7 times in selection view, have a pageable selection view,...
Because we have this unique constraint, we have to make sure only unique items are persisted. So we have implemented
equals and
hashCode correctly (using
type and
objectId). When a user adds 1 or more items to his selection: we get all currently selected items in a
Set, add all new items and persist this set again. This approach works flawlessly, but has one drawback: even if the user wants to add only 1 item, all his selected items (can easily be 5K, 10K or even more) are read from database into a
Set, just to guarantee uniqueness. This is some kind of performance overhead. That's why I'm looking for an alternative approach/procedure.
My attempts:
you can set the IGNORE_DUP_KEY option to ON on your unique index to produce a warning (instead of an error) when you try to insert a duplicate row. But Hibernate doesn't like you trying to insert an already existing item (with a null id) and throws an EntityExistsExceptioncreating an embeddable (composite) SelectionItemKey using type and objectId and use this key as @Id for the SelectionItem entity. This works pretty well: when you add an item (already existing in the selection) to the selection again, Hibernate performs an update of this row (which is not an actual problem in our use case). So you don't need to retrieve all selected items from database anymore just to add 1 new item. Mission accomplished!
Any other hints, tips or suggestions?
Kind regards,
Roel