discriminating between DataIntegrityViolationException

[mariustant]

I am using Spring with Hibernate to persist User entities. Users have roles and attributes. The Users table has many-to-many relationships with the Roles table and with the Attributes table. There are several cases in which I get a DataIntegrityViolationException while saving a User:
- when trying to persist a User with an already existing userName (violation of UNIQUE constraint)
- when trying to persist a User with a Role that has been deleted (in the meantime) (violation of FOREIGN KEY constraint)
- when trying to persist a User with an Attribute that has been deleted (in the meantime) (violation of FOREIGN KEY constraint)
I would like to know if there is a way to discriminate between these exceptions, in order to be able to display an appropriate message. My application has to support multiple databases (Oracle, MSSqlServer). Parsing the exception messages is not an option.

[Colin Sampaleanu]

It's not necessarilly a great option in all cases, but a lot of the time, if my service layer needs to throw a recoverable (checked) exception indicating something like a duplicate username exception, I will actually have the service layer do a query first to see if there is a dupe (throwing the checked exception if there is), and only then trying to do the actual save operation via the DAO. This allows you to be much more specific about which field cause the problem, and in a lot of cases the performance implications are negligible (for example, in most web apps users don't really get created very often).

On the other hand, if it's an error you think is going to happen reasonably often, in an operation that needs the utmost speed, you may have to resort to mapping the contained exceptions...

[mariustant]

Thx. Good idea, it helps :)

[stutchbury]

I usually try to catch all recoverable data exceptions in the service layer validator (pattern extended from org.springframework.samples.jpetstore.domain.logic .*Validator.java) - relying on database generated exceptions only for non-recoverable system type failures (e.g. database is not up etc).

[sjivan]

if my service layer needs to throw a recoverable (checked) exception indicating something like a duplicate username exception, I will actually have the service layer do a query first to see if there is a dupe (throwing the checked exception if there is), and only then trying to do the actual save operation via the DAO. This allows you to be much more specific about which field cause the problem, and in a lot of cases the performance implications are negligible

Colin,
I have gone back and forth on the approach to use for such duplicate key checks (try insert + logic in exception handling code versus explicitly querying for checking for existence). DataIntegrityViolationException gives us a fairly decent idea as to what went wrong but as you mention its not as specific in its check as explicitly querying for duplicates and I prefer the approach you've outlined above.

I noticed that the JPetStore example has been updated to prevent duplicates using the exception handling approach. I would have prefered it if called the service API to check for existence instead of using the DataIntegrityViolationException approach.

http://opensource.atlassian.com/projects/spring/browse/SPR-344

Thanks,
Sanjiv

[leathurman]

Hi,

I know this is an old thread but I am just about to implement some constraint checking and noticed the reply by Colin.

I have always favoured the complete opposite approach to that mentioned by Colin and wanted to know why it was recommended.

I agree that the liklehood of users entering duplicate username is not very likely, I think 10% at most. If this is the case why take the pain of doing additional selects for 90% of the users just to support the 10% where it is needed.

For this reason I have always thrown the insert in and handled the exception.

I realise doing the SELECT may not be that much pain (indexed etc) but why do it if its > 0 and catching the exception provides enough detail?

Have I got the wrong end of the stick?

Lea.

[Maarten]

besides, if you don't do any specific locking you always have the chance
that another transaction inserts the same userid just after your select-check but before your insert. (depending on db and isolation level)
Unlikely in the case of usernames picked by the user, but certainly possible in other cases.

[Colin Sampaleanu]

Well my main point (which maybe was not clear) is that it's relatively painful to just do the insert and propely map the resulting exception, perhaps based on the contained exception. In some cases, the contained exception may be identical (i.e. you inset a dupe email vs. a dupe username, both throw same exception with same contained sql exception).

So for most of these sorts of situaitons, unless I think it's a high throughput situation where the last bit of speed matters (and adding a new user is usuallys something that is done relatively infrequently in most apps), it's just easier to do the explicit checks, and thow the exception based on that, relying on the generic error to catch concurrency errors.