Recovering from database constraint violations in Java

It is very difficult to recover from a database constraint violation in Java applications, due to the lacking SQL exception API. JaRB offers an exception translator that converts any constraint related SQL exception into a rich constraint violation exception, providing all desired information by API. It is even possible to map constraints to a custom exception, greatly simplifying the recovery of violations.

Whenever a database constraint is violated by a query in our application, the JDBC driver will throw a SQL exception. The SQL exception only offers us a vendor specific error code and error message, making it very difficult to determine what constraint was violated by our query. To make things even more difficult, each JDBC driver has different error messages. Due to this poor API design, we often just ignore and log these exceptions.

A common approach is to pre-check our query on possible violations. For example, we have a unique key constraint. Before inserting a new record, we first check if a similar record already exists with an EXISTS query. However, this approach is very error prone, as the record could be added between the EXISTS and INSERT query. Not to mention that it is very inefficient to perform an extra query before each modification. It seems more preferable to just let the violation occur.

In case a violation has occurred. We most likely want to recover from the exception, or at least inform the end-user about our problem. But in order to recover from our exception, we first need to understand what constraint was violated. Fortunately Hibernate recognized the lack of information inside SQL exceptions and translates the exception into a ConstraintViolationException with access to the constraint name. The constraint name is extracted from our exception message by a database specific Hibernate dialect. This is a great idea, but only named constraint violations are translated. Also, not every application wants to use Hibernate. Spring also offers some exception translation, using the error codes of an SQL exception and wrapping any Hibernate exception. While providing a nice exception hierarchy, the constraint violation exceptions of Spring are quite poor. The API offers no information about the constraint, just a message.

In order to properly recover from a database constraint violation, we created the JaRB exception translator. Translating SQL exceptions into rich database constraint violation exceptions, providing all violation information by API:
exception.getViolation().getConstraintName()
exception.getViolation().getConstraintType()

We made the exceptions as obvious as possible by creating an exception hierarchy for the different types of database constraints that can be violated:

  • UniqueKeyViolationException

  • ForeignKeyViolationException

  • CheckFailedException

  • LengthExceededException

  • InvalidTypeException

  • NotNullException


It is even possible to register custom exceptions for a named constraint:
@NamedConstraint("uk_cars_license_number")
class LicenseAlreadyExistsException extends UniqueKeyViolationException {
public LicenseAlreadyExistsException(
DatabaseConstraintViolation violation, Throwable cause) {
super(violation, cause);
}
}

Just a single line of configuration enables the exception translation:
<translate-exceptions data-source="dataSource" base-package="com.myproject"/>

By enabling exception translation, each SQL exception will automatically be translated. With our new exception it becomes very simple to recover from violations.
public class GarageService {
private CarRepository carRepository;
public Car storeCar(Car car) {
try {
carRepository.store(car);
} catch(LicenseAlreadyExistsException e) {
// Perform recovery
}
}

How does it work?

The exception translator consists of two main components: the violation resolver and exception factory. Our violation resolver is responsible for gathering violation information from the original exception. This is done by “digging” in the root cause message. Each supported driver has a custom resolver implementation. By specifying the data source we are able to determine what resolver needs to be used. As fallback we also have a Hibernate based resolver. The following drivers are supported:

  • Oracle

  • PostgreSQL

  • MySQL

  • HSQL

  • H2


When the violation is resolved, we can create a new exception with the exception factory. Inside JaRB we provide a default type based exception factory that creates the exception based on violation type. In addition, we scan the base package for any annotated custom exceptions or exception factories.

By enabling exception translation, a proxy is added to our repository beans, just as Spring performs its exception translations. Whenever a runtime exception occurs during method invocation, we will automatically attempt to translate the exception into a constraint violation exception. Exceptions that cannot be recognized are left unchanged.

Getting started

Include the following maven dependency to your project:
<dependency>
<groupId>org.jarbframework</groupId>
<artifactId>jarb-constraints</artifactId>
<version>1.4.0</version>
</dependency>

Include the jarb namespace in your application context:
http://www.jarbframework.org/schema/constraints/jarb-constraints.xsd

Now you can enable the translation, as previous stated:
<translate-exceptions data-source="dataSource" base-package="com.myproject"/>

Thats it!

Conclusion

JaRB's exception translator greatly simplifies the recovery of database constraint violations. The generated exceptions provide all constraint information by API and we can even map constraints to custom exceptions. Enabling exception translation only requires one line of configuration, making it very easy to integrate in your project.

More information and sources are available on jarbframework.org

If you want to try JaRB first, just clone our repository and check the sample application.
git clone git://github.com/42BV/jarb.git
mvn clean install
cd jarb-sample
mvn jetty:run