-->
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.  [ 6 posts ] 
Author Message
 Post subject: How to deal with duplicate unique fields?
PostPosted: Sun Mar 23, 2008 10:55 pm 
Regular
Regular

Joined: Mon Mar 10, 2008 6:40 pm
Posts: 114
What is the best way to deal with potential duplicate fields being inserted into a table where that is not allowed? For example:

CREATE TABLE Person (Id INT, Name VARCHAR(255), Email VARCHAR(255), UNIQUE (Email));

Then we try to save 2 different Person objects with the same email:
session.save(new Person("John", "a@b.com"));
session.save(new Person("Adam", "a@b.com"));

I will, of course, get an exception. It's pretty ugly and the messages are vague:
Code:
org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.jdbc.UncategorizedSQLException: Hibernate flushing: Could not execute JDBC batch update; uncategorized SQLException for SQL [insert into Person (Id, Name, Email) values (?, ?, ?)]; SQL state [null]; error code [0]; failed batch; nested exception is java.sql.BatchUpdateException: failed batch

With the following root cause:
Code:
java.sql.BatchUpdateException: failed batch


My question is what is the best way to handle this? Obviously one option is to check the database before performing a session.save(person). In my particular application and in this particular situation I think I can get away with that, but in general that's a little ugly as an exception can still happen if both checks happen near the same time. Instead I would think catching the appropriate exception would be a more elegant solution. But which exception do I catch for this? How do I know duplicate unique fields was the error?


Top
 Profile  
 
 Post subject: Re: How to deal with duplicate unique fields?
PostPosted: Mon Mar 24, 2008 10:51 am 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
Each SQLException has an error code which is db specific. You may get the error code and translate it to an exception you like. If you are not already using spring, I suggest you take a look at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator's source code to see how it translates error codes to exceptions. In addition, sql-error-codes.xml in the same package has db specific error codes in case you need a start point.



Farzad-


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 24, 2008 12:52 pm 
Regular
Regular

Joined: Mon Mar 10, 2008 6:40 pm
Posts: 114
Thank you for the reply farzad. I am in fact using Spring. But the exception Spring translates the original exception to is: org.springframework.jdbc.UncategorizedSQLException. The original SQLException is java.sql.BatchUpdateException. Neither of which tell me what's going on really. This is situation seems so common, isn't there a better way to handle this?


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 24, 2008 12:54 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
mueller wrote:
Neither of which tell me what's going on really. This is situation seems so common, isn't there a better way to handle this?



Nothing that I know of. What is your database? What is the returned error code?



Farzad-


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 24, 2008 12:57 pm 
Regular
Regular

Joined: Mon Mar 10, 2008 6:40 pm
Posts: 114
I don't seem to get an error code. For development I'm using hsqldb, but production is mysql.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Mar 24, 2008 1:01 pm 
Expert
Expert

Joined: Wed Apr 11, 2007 11:39 am
Posts: 735
Location: Montreal, QC
mueller wrote:
I don't seem to get an error code. For development I'm using hsqldb, but production is mysql.



Hmm, give it a try with MySQL and see if that makes any difference. You won't have much luck if you can't get the exception. Your last resort might be to look up an entity before inserting but that's just too bad.


Farzad-


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 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.