Unique constraint violation error - How should it be trated?

[ndijkstra]

Hi, i have the following question.
Given a table, suppose City, with an id field (primary key) and a name name field (with a unique constraint). If i insert twice a city named X, an SQLException will be thrown in the second insertion due to unique constraint being violated. This error should be treated as an application rather than a system error, right?

I suppose, the SQLException will be wrapped in a DataAccessException wich is an unchecked exception (therefore a system error). Is there a way to distinguish an application error from a system error given a DataAccessException?

Thanks in advance.

[irbouho]

In case of Primary Key / unique Key violation, Spring wrapps SQLException in a DataIntegrityViolationException. DataIntegrityViolationException subclasses DataAccessException.
So you can catch DataIntegrityViolationException in your code to report the application error.

[ndijkstra]

Thanks Omar. Is DataIntegrityViolationException going to be thrown when a foreign key violation error is genereated while trying to delete a row referenced by another table?

[irbouho]

well, this depends on the database / jdbc driver version you are using. I did some testing and it worked for hsqldb 1.7.2 but not postgresql 7.2 (SQLState 'null' and errorCode '0', not very clear how to translate this into DataIntegrityViolationException)
Hopefuly it will work for your environment.

[trisberg]

One solution is to use a custom translation defined in sql-error-codes.xml. First create your own exception class that is a sub class of DataAccessException.

import org.springframework.dao.DataIntegrityViolationException;

public class UniqueConstraintException extends DataIntegrityViolationException {

	public UniqueConstraintException(String msg) {
		super(msg);
	}

	public UniqueConstraintException(String msg, Throwable ex) {
		super(msg, ex);
	}
}

Then make a copy of sql-error-codes.xml and modify it to fit your needs. Add a "customTranslations" entry for your custom exceptions class. This modified version of sql-error-codes.xml must be placed on the classpath to get picked up.

<!DOCTYPE beans PUBLIC  "-//SPRING//DTD BEAN//EN" "http&#58;//www.springframework.org/dtd/spring-beans.dtd">
	
<beans>

  <bean id="Oracle" class="org.springframework.jdbc.support.SQLErrorCodes">
    <property name="badSqlGrammarCodes"><value>900,903,904,917,936,942,17006</value></property>
    <property name="dataIntegrityViolationCodes"><value>1400,1722,2291</value></property>
    <property name="cannotAcquireLockCodes"><value>54</value></property>
    <property name="customTranslations">
      <list>
        <bean class="org.springframework.jdbc.support.CustomSQLErrorCodesTranslation">
          <property name="errorCodes"><value>1</value></property>
          <property name="exceptionClass"><value>UniqueConstraintException</value></property>
        </bean>
      </list>
    </property>
  </bean>

</beans>

Now you can catch this custom exception and handle it or throw an application exception.

For PostgreSQL - they do provide error codes starting with version 7.4.

[fvtec]

I've used this approach of defining a UniqueConstraintException and then throwing it from my DAO and Service layer. The exception is handled in my JSF Backing Bean and an appropriate message is displayed.

I would like to know if this is a good practice, or if there is a better approach?