title: Handling database constraint errors nicely
body: <p>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:</p>
  
  <pre class="lang-java prettyprint-override"><code>public class Location {
      // more fields
  
      @Column(unique = true)
      @NotNull
      private String locationName;
  
      // getters and setters
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre class="lang-none prettyprint-override"><code>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
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>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"
      }
  }
  </code></pre>
  
  <p>I think there <strong>must</strong> 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.</p>
  
  <p>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....</p>
  
  <p>It feels like I am overlooking something terribly obvious.</p>
  
  <p>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 <code>ConstraintViolations</code> defined in there...</p>