Handling database constraint errors nicely

Question

Yet again it was supposed to be simple.... I was tasked with placing a certain field in the domain-objects of our application with a unique constraint. That in itself wasn't much of a challenge. I just did the following:

public class Location {
    // more fields

    @Column(unique = true)
    @NotNull
    private String locationName;

    // getters and setters
}

So far so good, all went well and I got an error on running some tests against the database. Now I went ahead and started to implement Controller code, that was supposed to handle edits of the domain object.

And then all hell broke loose, because what I got back from my hibernate was some stack-trace that simply seems to prevent any sane handling of this:

18:30:20,173 ERROR [org.jboss.ejb3.invocation] (http-localhost-127.0.0.1-8443-2) JBAS014134: EJB Invocation failed on component LocationService for method public abstract long company.project.services.IService.update(java.lang.Object) throws novatec.crm.DataLayerLockException: javax.ejb.EJBTransactionRolledbackException: Transaction rolled back
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.handleEndTransactionException(CMTTxInterceptor.java:115) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.endTransaction(CMTTxInterceptor.java:95) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.invokeInOurTx(CMTTxInterceptor.java:232) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.required(CMTTxInterceptor.java:304) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.processInvocation(CMTTxInterceptor.java:190) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at org.jboss.invocation.InterceptorContext.proceed(InterceptorContext.java:288) [jboss-invocation-1.1.1.Final.jar:1.1.1.Final]
    at org.jboss.as.ejb3.component.interceptors.CurrentInvocationContextInterceptor.processInvocation(CurrentInvocationContextInterceptor.java:41) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) [rt.jar:1.6.0_45]
    ... 125 more
Caused by: javax.transaction.RollbackException: ARJUNA016053: Could not commit transaction.
    at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1177)
    at com.arjuna.ats.internal.jta.transaction.arjunacore.BaseTransaction.commit(BaseTransaction.java:117)
    at com.arjuna.ats.jbossatx.BaseTransactionManagerDelegate.commit(BaseTransactionManagerDelegate.java:75)
    at org.jboss.as.ejb3.tx.CMTTxInterceptor.endTransaction(CMTTxInterceptor.java:92) [jboss-as-ejb3-7.1.1.Final.jar:7.1.1.Final]
... 79 more
Caused by: javax.persistence.PersistenceException: org.hibernate.exception.ConstraintViolationException: Eindeutiger Index oder Primärschlüssel verletzt: "CONSTRAINT_INDEX_9 ON PUBLIC.LOCATION(LOCATIONNAME)"
Unique index or primary key violation: "CONSTRAINT_INDEX_9 ON PUBLIC.LOCATION(LOCATIONNAME)"; SQL statement:
update Location set housenumber=?, locationName=?, numberSuffix=?, street=?, town=?, version=?, zipcode=? where id=? and version=? [23505-161]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1361) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1289) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1295) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.ejb.AbstractEntityManagerImpl$CallbackExceptionMapperImpl.mapManagedFlushFailure(AbstractEntityManagerImpl.java:1481) [hibernate-entitymanager-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.transaction.synchronization.internal.SynchronizationCallbackCoordinatorImpl.beforeCompletion(SynchronizationCallbackCoordinatorImpl.java:109) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.transaction.synchronization.internal.RegisteredSynchronization.beforeCompletion(RegisteredSynchronization.java:53) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at com.arjuna.ats.internal.jta.resources.arjunacore.SynchronizationImple.beforeCompletion(SynchronizationImple.java:76)
    at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.beforeCompletion(TwoPhaseCoordinator.java:273)
    at com.arjuna.ats.arjuna.coordinator.TwoPhaseCoordinator.end(TwoPhaseCoordinator.java:93)
    at com.arjuna.ats.arjuna.AtomicAction.commit(AtomicAction.java:164)
    at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.commitAndDisassociate(TransactionImple.java:1165)
    ... 82 more
Caused by: org.hibernate.exception.ConstraintViolationException: Eindeutiger Index oder Primärschlüssel verletzt: "CONSTRAINT_INDEX_9 ON PUBLIC.LOCATION(LOCATIONNAME)"
Unique index or primary key violation: "CONSTRAINT_INDEX_9 ON PUBLIC.LOCATION(LOCATIONNAME)"; SQL statement:
update Location set housenumber=?, locationName=?, numberSuffix=?, street=?, town=?, version=?, zipcode=? where id=? and version=? [23505-161]
    at org.hibernate.exception.internal.SQLStateConversionDelegate.convert(SQLStateConversionDelegate.java:128) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:47) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:129) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at com.sun.proxy.$Proxy79.executeUpdate(Unknown Source)
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3010) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.updateOrInsert(AbstractEntityPersister.java:2908) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.persister.entity.AbstractEntityPersister.update(AbstractEntityPersister.java:3237) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.action.internal.EntityUpdateAction.execute(EntityUpdateAction.java:113) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.spi.ActionQueue.execute(ActionQueue.java:272) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:264) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.spi.ActionQueue.executeActions(ActionQueue.java:187) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:326) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.event.internal.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:52) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.flush(SessionImpl.java:1081) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.internal.SessionImpl.managedFlush(SessionImpl.java:315) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    at org.hibernate.engine.transaction.synchronization.internal.SynchronizationCallbackCoordinatorImpl.beforeCompletion(SynchronizationCallbackCoordinatorImpl.java:104) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    ... 88 more
Caused by: org.h2.jdbc.JdbcSQLException: Eindeutiger Index oder Primärschlüssel verletzt: "CONSTRAINT_INDEX_9 ON PUBLIC.LOCATION(LOCATIONNAME)"
Unique index or primary key violation: "CONSTRAINT_INDEX_9 ON PUBLIC.LOCATION(LOCATIONNAME)"; SQL statement:
update Location set housenumber=?, locationName=?, numberSuffix=?, street=?, town=?, version=?, zipcode=? where id=? and version=? [23505-161]
    at org.h2.message.DbException.getJdbcSQLException(DbException.java:329)
    at org.h2.message.DbException.get(DbException.java:169)
    at org.h2.message.DbException.get(DbException.java:146)
    at org.h2.index.BaseIndex.getDuplicateKeyException(BaseIndex.java:81)
    at org.h2.index.PageBtree.find(PageBtree.java:121)
    at org.h2.index.PageBtreeLeaf.addRow(PageBtreeLeaf.java:146)
    at org.h2.index.PageBtreeLeaf.addRowTry(PageBtreeLeaf.java:100)
    at org.h2.index.PageBtreeIndex.addRow(PageBtreeIndex.java:105)
    at org.h2.index.PageBtreeIndex.add(PageBtreeIndex.java:96)
    at org.h2.table.RegularTable.addRow(RegularTable.java:121)
    at org.h2.table.Table.updateRows(Table.java:439)
    at org.h2.command.dml.Update.update(Update.java:128)
    at org.h2.command.CommandContainer.update(CommandContainer.java:73)
    at org.h2.command.Command.executeUpdate(Command.java:219)
    at org.h2.server.TcpServerThread.process(TcpServerThread.java:302)
    at org.h2.server.TcpServerThread.run(TcpServerThread.java:137)
    at java.lang.Thread.run(Unknown Source)

    at org.h2.engine.SessionRemote.done(SessionRemote.java:538)
    at org.h2.command.CommandRemote.executeUpdate(CommandRemote.java:183)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdateInternal(JdbcPreparedStatement.java:143)
    at org.h2.jdbc.JdbcPreparedStatement.executeUpdate(JdbcPreparedStatement.java:129)
    at org.jboss.jca.adapters.jdbc.WrappedPreparedStatement.executeUpdate(WrappedPreparedStatement.java:493)
    at sun.reflect.GeneratedMethodAccessor391.invoke(Unknown Source) [:1.6.0_45]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) [rt.jar:1.6.0_45]
    at java.lang.reflect.Method.invoke(Method.java:597) [rt.jar:1.6.0_45]
    at org.hibernate.engine.jdbc.internal.proxy.AbstractStatementProxyHandler.continueInvocation(AbstractStatementProxyHandler.java:122) [hibernate-core-4.0.1.Final.jar:4.0.1.Final]
    ... 102 more

I don't want anything to do with that, and I want to save my poor user from such an exception which he can't ever be expected to handle when it crashes into his face. So I though: Hey I should hide this.. what I did was the following:

try {
    service.update(object);
} catch (EJBTransactionRolledbackException e) {
    Throwable root = e.getCause();
    while (root.getCause() != null) {
        root = root.getCause();
    }
    if (root instanceof java.sql.SQLException) {
        // duplicate location-name
        resources.produceFacesContext().addMessage(
            "code",
            new FacesMessage(FacesMessage.SEVERITY_WARN, "Fehler:",
                    "Der Standortname muss einmalig sein!"));
        // german for: "Error:", "LocationName must be unique"
    }
}

I think there must be a better way to handle this. There shouldn't be a necessity to revert to such hackish workarounds, just to find out, what exactly broke so you can inform the user of the mistake he's made.

Of course I could try to run the Domain Object against a Validator, that runs a defensive SELECT-statement. But it feels even more dirty to enforce database constraints in application logic, than traversing the Exception root-causes....

It feels like I am overlooking something terribly obvious.

P.S. If I don't catch the exception everything breaks... there is no nice handling of the ConstraintViolationException. I guess it's because there are no ConstraintViolations defined in there...

for anyone who tries to recommend moving this to [codereview.se]: No I will not go to CR with this, because it's off-topic there! — Vogel612, Sep 09 '14 at 06:32
Even after further research it seems that the only way to accomplish this nicely is to create a custom annotation to reiterate the unique constraint on application side... If someone provides an answer proving the opposite, I will gladly accept that, but as of now, the accepted answer is the way to go. — Vogel612, Sep 10 '14 at 13:44
Have a look at https://github.com/42BV/jarb - [you said it was awesome in chat](http://chat.stackexchange.com/transcript/message/18071177#18071177), so maybe it will help others — Pimgd, Oct 09 '14 at 11:52

score 0 · Accepted Answer · answered Sep 09 '14 at 07:05

0

Parsing exceptions is always a bad idea. Especially what you are doing here because you assume any SQL exception to be a consequence of the constraint violation where in reality an SQLException can be many many things.

If you set a database unique constraint on a field, then try to enter it twice, this is what you will get, the obvious solution is not to try to catch the error when it occurs but to manually check if it exists in the database before inserting it.

answered Sep 09 '14 at 07:05

nablex

4,635
4
36
51

2

I am well aware that this is a possibility... But as written in my question, it feels even weirder to enforce database constraints in application logic. – Vogel612 Sep 09 '14 at 07:08
3

yeah right, enforcing database constraints in application logic is a terrible idea and the cost is also usually very high – Neeraj B. Oct 13 '17 at 15:46
1

In other words, don't rely on the database to enforce integrity, but on the business logic. Why use a database at all in the first place ? – Mansour Jul 06 '19 at 17:57

score 0 · Answer 2 · answered Sep 09 '14 at 08:01

0

I would use an interceptor that unwraps the root cause, so it's easier to handle the exception in the upper layer.

To unwrap the root cause you can use ExceptionUtils:

org.apache.commons.lang3.exception.ExceptionUtils#getRooutCause

The business layer interceptor should catch all runtime exceptions, unwrap them and throw them further.

You can take a look on Spring PersistenceExceptionTranslator, which takes a RuntimeException and translates it into a DataAccessException which isolates you from various framework specific exception types.

answered Sep 09 '14 at 08:01

Vlad Mihalcea

142,745
71
566
911

unfortunately I am not using Spring Framework for this and it'd probably be a less intelligent idea to add something that powerful to my dependencies for that single piece of functionality. – Vogel612 Sep 09 '14 at 09:42
Right, but you can take a look on the PersistenceExceptionTranslator to get an idea of how the exception translation might be implemented. – Vlad Mihalcea Sep 09 '14 at 10:27

Handling database constraint errors nicely

2 Answers2

Linked