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

6 comments:

  1. Hi,

    Will it work with JPA.

    ReplyDelete
  2. @seenukarthi Yes, it works with JPA. My sample project also uses the full Spring, Hibernate, JPA stack. See: https://github.com/42BV/jarb/tree/master/jarb-sample

    ReplyDelete
  3. Spring offers something like this since the beginning (SQLExceptionTranslator) and nowadays even the dreaded java.sql.SqlException comes along with some meaningful sub-classes.
    Then again, I like to use database constraint to the fullest extent, i.e. trying to express all model invariants as database constraints. Databases are usually better at handling concurrency than homegrown code, too.
    As a logical extension, an SQLException shouldn't be mapped to a generic exception but to an application-specific one, say CityInAddressMissingException. That's were a pluggable translator comes in handy.

    Is the JaRB mapping pluggable?

    There are two difficulties associated with recovering from constraint violations you didn't mention:
    1. When you need to act on a violation (i.e. sending a JMS message), you can't rollback the transaction, so you have to roll back the transaction manually, so cleaning up everything you've done so far in the transaction is your job, not the database's one. Explicitly design your code for this.
    2. Some constraints are deferred, so the exception will be thrown only after the commit of the transaction. Usually, that happens after the business layer call - being the natural UoW - has returned. A dynamic proxy or aspect can deal with this. Now, if the application exceptions are checked, that leads to the somewhat weird situation, that the business layer declares to throw exceptions, which are never thrown by the business code itself, but by the proxy surrounding it.

    ReplyDelete
  4. @Frisian I totally agree that constraint violations should throw application specific exceptions, rather than a generic exception. Specific exceptions are obvious, making the code understandable and they also closely relate to the domain you are programming for.

    JaRB mappings are fully pluggable. We link application specific exceptions to database constraints by annotation, allowing multiple type of matching strategies:
    - Exact
    - Exact ignore case
    - Contains
    - Starts with
    - Ends with
    - Regex

    During startup we scan for annotated exceptions and automatically register them in our mapping.

    I'm also aware of the SQLExceptionTranslator, but the translated DataAccessException still only provide a message and cause. JaRB generic exceptions provide full constraint information by API.

    ReplyDelete
  5. I'm constantly searching on the internet for posts that will help me. Too much is clearly to learn about this. I believe you created good quality items in Functions also. Keep working, congrats! Tucson Code Enforcement Violations

    ReplyDelete