• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Ignoring a unique constraint on insert

 
Roel De Nijs
Sheriff
Posts: 10666
144
AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 EntityExistsException
  • creating 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
     
    Arun Kumarr
    Ranch Hand
    Posts: 661
    Eclipse IDE Java Spring
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator


    Some basic queries:
    1. Are you perisiting selection item per user or is a selection item common across users?
    2. Can you not keep a separate dataset when a user adds a selection item (a item with contains selection item with null "id"s)
    3. Third if a user is removing a selection item from one place, removing it from the DB - will it make the selection item for the user (or for every other users) to be removed from all the other places.


    Ignore them, if they sound too naive.

    Here's my understanding, you have a map which is collection of objects (with a surrogate key, object id and type). Multiple users can connect to your system and add selection items or remove selection items. If a user removes a selection item, then you will go ahead and remove it from the system. The removal is independent of whether any other user has selected it or not.

    Couple of suggestion:

    1. Can we not use a layer of cache in hibernate for this?
    2. Can we not using object versions for the selection item? - Timestamp could be a version. You could also have an incremental version number.
     
    Roel De Nijs
    Sheriff
    Posts: 10666
    144
    AngularJS Chrome Eclipse IDE Hibernate Java jQuery MySQL Database Spring Tomcat Server
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thanks for the suggestions!

    In the end I finally ended up implementing my 2nd attempt. I created an embeddable (composite) SelectionItemKey. And instead of using an automatically generated id, I created a custom id (of course based on the SelectionItemKey). This makes it possible to address each item uniquely without having to use the seperate values which simplifies the code as well (you can use 1 string value to identify an item instead of multiple string values). And using insertable and updatable attributes of the Column annotation, you can decide which columns should be added to insert statement and more importantly (as Hibernate performs an update if you add an item with an already existing key) to update statement as well. This works very well, much better than expected!
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic