Dealing with SQLException with spring,hibernate & Postgres

Question

Hi im working on a project using HibernateDaoSUpport from my Daos from Spring & spring-ws & hibernate & postgres who will be used in a national application (means a lot of users)

Actually, every exception from hibernate is automatically transformed into some specific Spring dataAccesException.

I have a table with a keyword on the dabatase & a unique constraint on the keywords : no duplicate keywords is allowed.

I have found twows ways to deal with with that in the Insert Dao: 1- Check for the duplicate manually (with a select) prior to doing your insert. I means that the spring transaction will have a SERIALIZABLE isolation level. The obvious drawback is that we have now 2 queries for a simple insert.Advantage: independent of the database

2-let the insert gone & catch the SqlException & convert it to a userfriendly message & errorcode to the final consumer of our webservices.

Solution 2: Spring has developped a way to translate specific exeptions into customized exceptions. see http://www.oracle.com/technology/pub/articles/marx_spring.html

In my case i would have a ConstraintViolationException.

Ideally i would like to write a custom SQLExceptionTranslator to map the duplicate word constraint in the database with a DuplicateWordException.

But i can have many unique constraints on the same table. So i have to get the message of the SQLEXceptions in order to find the name of the constraint declared in the create table "uq_duplicate-constraint" for example. Now i have a strong dependency with the database.

Thanks in advance for your answers & excuse me for my poor english (it is not my mother tongue)

Answer 1

In my experience it is always better to have your data validation in your application rather than depend on the database. This keeps your database's role limited to that of a data store, meaning wont have business logic spread across two layers.

What happens when you have a database transaction that would break two constraints at the same time? In that case your exception mapping approach will only catch the first failure, rather than some validation code that can show all data validation issues on an attempt to save.