-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 posts ] 
Author Message
 Post subject: Best practices for handling unique constraint violation
PostPosted: Tue Feb 23, 2010 11:02 pm 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
I am using Hibernate 3.3.2 and trying to figure out the best way to prevent a unique constraint violation in my code.

[u]Setup:[/u]
Suppose I have a many-to-many relationship. I will call the left side a parent and right side a child.
There is a unique constraint on the child's name.

[u]User Scenario:[/u]
The user creates parent P1 and child with name C1 and saves both in the database.
The user then creates a new parent P2 and adds a new child object with the same name C1 to P2 collection of children.
When P2 is saved a ConstraintViolationException is thrown.

My question is what is the recommended practice to handle unique constraint violations on children objects when the parent is saved?

Thanks.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Wed Feb 24, 2010 11:38 am 
Expert
Expert

Joined: Tue Jun 16, 2009 3:36 am
Posts: 990
Recommended practice is to prevent unique constraint violation as far as possible.
In your case you could query child's name before creating another child with the same unique name,
for example

Code:
Object obj = sess.createQuery("from C where name='myname'").uniqueResult();
if (obj != null) {
  showPopup("WARNING: This name is already used, please choose another one!");
}


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Wed Feb 24, 2010 11:51 am 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
If u have a many-to-many relation then there is no singular PARENT but multiple parents (well I would thus not even use the term "parent") and the Child class should either have a reference to a Collection of Parents or no parent at all.
Code:
public class Container {
   private int id;
   private Collection<Content> contents = new ArrayList<Content>();
   public Collection<Content> getContents() {
      return contents;
   }

   private void setContents(Collection<Content> contents) {
      this.contents = contents;
   }
}
public class Content {
   private int id;
   private String name;

   public void setName(String name) {
      this.name = name;
   }

   public String getName() {
      return name;
   }
}
<hibernate-mapping>
   <class name="Container">
      <id name="id" access="field">
         <generator class="native" />
      </id>
      <bag name="contents" cascade="save-update">
         <key></key>
         <many-to-many class="Content"></many-to-many>
      </bag>
   </class>
   <class name="Content">
      <id name="id" access="field">
         <generator class="native" />
      </id>
      <natural-id>
         <property name="name" not-null="true"/>
      </natural-id>
   </class>
</hibernate-mapping>


I believe u did something like the following:
Code:
      final Session sess = sf.openSession();

      Container container = new Container();
      Content content = new Content();
      content.setName("A");
      container.getContents().add(content);
      
      Transaction tx = sess.beginTransaction();
      sess.save(container);
      tx.commit();
      
      tx = sess.beginTransaction();
      container = new Container();
      content = new Content();
      content.setName("A");
      container.getContents().add(content);
      sess.save(container);
      tx.commit();

And of course the above will generate:
Violation of unique constraint

What I would suggest u to do is (depending on the semantics of your domain objects) if u are indeed going to have the same content in more than one containers, don't create a new content blindly but first try to load an existing one:
Code:
      final Session sess = sf.openSession();
      Container container = new Container();
      Content content = new Content();
      content.setName("A");
      container.getContents().add(content);
      
      Transaction tx = sess.beginTransaction();
      sess.save(container);
      tx.commit();
      
      tx = sess.beginTransaction();
      container = new Container();
      content = (Content) sess.createQuery("from Content where name like :name").setString("name", "A").uniqueResult();
      container.getContents().add(content);
      sess.save(container);
      tx.commit();
      sess.close();


But I also doubt if what u wanted was indeed a parent-child relation whence the relation would be one-to-many and not many-to-many.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Wed Feb 24, 2010 2:26 pm 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
Hi tseringshrestha,

Thank you for the detailed response. You captured my requirements correctly. The "parent" term was a misnomer, but I decided that it's more intuitive then using "left-hand-side" term.

My real use case is a many-to-many relationship between projects and members where member users have a unique key on their user names.

Your solution requires a separate lookup on member to check if the member with the same user name already exists. I was hoping that there was a way to instruct Hibernate to do this transparently.

Any ideas?

Thanks.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Wed Feb 24, 2010 4:32 pm 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
Hi pb00067,

Just noticed your response. Thanks!

The question remains can Hibernate be instructed to perform such a lookup automatically?

Thanks.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Wed Feb 24, 2010 5:25 pm 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
Yes there is.
Use merge.
Code:
public class Container {
   private int id;
   private Collection<Content> contents = new ArrayList<Content>();

   public Collection<Content> getContents() {
      return contents;
   }

   private void setContents(Collection<Content> contents) {
      this.contents = contents;
   }
}
public class Content {
   private String name;
   private String extraData;

   public void setName(String name) {
      this.name = name;
   }

   public String getName() {
      return name;
   }

   public void setExtraData(String extraData) {
      this.extraData = extraData;
   }

   public String getExtraData() {
      return extraData;
   }
}
<hibernate-mapping>
   <class name="Container">
      <id name="id" access="field">
         <generator class="native" />
      </id>
      <bag name="contents" cascade="merge,save-update">
         <key />
         <many-to-many class="Content" />
      </bag>
   </class>
   <class name="Content">
      <id name="name">
         <generator class="assigned" />
      </id>
      <property name="extraData"/>
   </class>
</hibernate-mapping>

Now make use of merges:
Code:
      final Session sess = sf.openSession();

      Container container = new Container();
      Content content = new Content();
      content.setName("A");
      content.setExtraData("Initial");
      container.getContents().add(content);

      Transaction tx = sess.beginTransaction();
      sess.merge(container);
      tx.commit();

      tx = sess.beginTransaction();
      container = new Container();
      content = new Content();
      content.setName("A");
      content.setExtraData("Final");
      container.getContents().add(content);
      sess.merge(container);
      tx.commit();


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Wed Feb 24, 2010 5:44 pm 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
Please note that merge has another subtle nuance compared to save/update/saveOrUpdate:
The instance does not become associated with the session. (see https://www.hibernate.org/hib_docs/v3/api/)
This will not be of any issue in most cases as long as any further changes you make to the objects - if you want to reconcile with the database, you will issue further merges. Be ware a merge is almost like a blind write though.
Someone might want to advise on this - use of optimistic lock + <version>


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Thu Feb 25, 2010 12:50 am 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
Hi tseringshrestha,

session.merge() did not work for me. I suppose in your example the first content object is still in the Hibernate session cache, so the second content gets merged with the first one. I don't think this solution will work if the first content has already been flushed to the database.

At this point it sounds like the only reliable solution is to do a look up before adding a new "child" to prevent unique key violation.

Thanks.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Thu Feb 25, 2010 1:51 am 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
Hi tseringshrestha,

I think the difference between what I am doing and your example is that in your example Content's primary key is it's name and in my case I have a surrogate primary key. So, if you add id field to your Content object as well as @UniqueConstraint(columnNames = {"id", "name"}), then it will match my use case.

I found this hint [url]https://www.hibernate.org/117.html#A8[/url] which suggests to manually flush the session between saves of objects with the same unique key, but it didn't work for me. My guess is that Hibernate uses @UniqueConstraint annotation when generating the DDL, but does not use it at runtime to prevent unique constraint violation.

Thoughts?

Thanks.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Thu Feb 25, 2010 10:35 am 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
Hi Alec,
Indeed, if u look into my previous post and the next one, I used a user-assigned id in the latter specifically to facilitate a merge. When we have a surrogate key, we are at a loss - a merge will never make sense. Seems like u will indeed have to manually fetch the element.
Now maybe we could urge the Hibernate team to treat <natural-id> the similar way as the <id> during merge - but I don't think anyone will vote for that.
:-(


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Thu Feb 25, 2010 11:00 pm 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
Hi tseringshrestha,

I am having trouble trying to implement a work-around Hibernate's inability to merge objects based on the unique key.

So, suppose we extend your example by adding a surrogate id to Content. Now, suppose before I add a content object to Container using container.getContents().add(content) I find an existing persistent content object with the same name. The problem I am having is merging the properties of the new content object with the existing one and saving the merged state in the database.

The only solution I found is to set the id of the new content object to the id of its persistent duplicate with the same name.

Content dupContent = DetachedCriteria.forClass(Content.class).add(Restrictions.eq("name", content.getName()));
if (dupContent != null) {
// Merge properties of content into dupContent
content.setId(dupContent.getId());
}
container.merge();

Setting the id is ugly, but does cause the merge of the content properties. However, after executing this code the container object is not persisted (unlike after createOrUpdate) which causes the code that uses it later on to fail.

Any ideas how to merge the properties of transient and persistent objects with the same unique key without setting the id of the transient object to the id of the persistent object?

Also, is there a way to extend the code above to make container persistent?

Thanks,

Alec


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Wed Mar 03, 2010 1:49 pm 
Beginner
Beginner

Joined: Tue Aug 25, 2009 11:42 am
Posts: 49
Hi Alec,
Quoting my previous comment -
Quote:
Please note that merge has another subtle nuance compared to save/update/saveOrUpdate:
The instance does not become associated with the session. (see https://www.hibernate.org/hib_docs/v3/api/)

Quote:
Also, is there a way to extend the code above to make container persistent?

So u will have to simply load the container object again with a query by example for instance.
Quote:
Any ideas how to merge the properties of transient and persistent objects with the same unique key without setting the id of the transient object to the id of the persistent object?

I think there is no easy alternative to this. I almost feel that it is such by-design.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Tue Mar 23, 2010 2:59 pm 
Newbie

Joined: Tue Mar 23, 2010 2:56 pm
Posts: 4
Hi.
I wanted to say that validating some type of constraints is not possible even conceptually in the application layer without locking tables that are involved in such validation.

Consider unique key validation. The only way that guaranties uniqueness and would not throw a unique constraint violation is to do the following sequence of actions.

1. Lock the table from modifications
2. Query this table for duplicates of an incoming data
3. If no duplicates found, insert incoming data
4. Unlock the table or commit transaction.

This is workable, but in my opinion cannot be a solution for concurrent multi-user enterprise level application.

Any other algorithm may render the assumptions of querying for duplicates invalid at the time of the insert.

Best regards,

- Vlad


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Tue Mar 23, 2010 3:39 pm 
Regular
Regular

Joined: Fri Mar 26, 2004 11:45 am
Posts: 75
You could use REPEATABLE_READ transaction isolation level to ensure that no row that violates the constraint was inserted after you checked uniqueness.


Top
 Profile  
 
 Post subject: Re: Best practices for handling unique constraint violation
PostPosted: Tue Mar 23, 2010 4:19 pm 
Newbie

Joined: Tue Mar 23, 2010 2:56 pm
Posts: 4
alecswan wrote:
You could use REPEATABLE_READ transaction isolation level to ensure that no row that violates the constraint was inserted after you checked uniqueness.


Isolation levels do not do that only locks do. I.e. isolation level only helps to restrict what you can see, not what others can modify. The purpose of the lock is not to have consistent check withing the given transaction, but to disallow other transactions to change the data during that period just before the transaction runs "do duplicates exist" query till it commits in a fashion that would violate the said check.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 15 posts ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.