Validate DB constraints: multiple unique columns

[kariem]

This question must have been asked already, but I could not find anything that fits the need. I use EntityHome objects to persist my entities. The main question is: How can I validate the input with the contents of the database before I actually call persist()?

Is there a nice way, to do this? How do I do that with a unique constraint on multiple columns?

The problem is that I receive a GenericJDBCException instead of a persistence exception:

org.hibernate.exception.GenericJDBCException: Could not execute JDBC batch update
  at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:103)
  at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:91)
  at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:253)
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:237)
  at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:141)
  at org.hibernate.event.def.AbstractFlushingEventListener.performExecutions(AbstractFlushingEventListener.java:298)
  at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:27)
  at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1000)
  at org.hibernate.ejb.AbstractEntityManagerImpl.flush(AbstractEntityManagerImpl.java:296)
  at org.jboss.seam.persistence.EntityManagerProxy.flush(EntityManagerProxy.java:90)
  at org.jboss.seam.framework.EntityHome.persist(EntityHome.java:85)
  ... 20 lines omitted (proxy)
  at org.jboss.seam.framework.EntityHome_$$_javassist_17.persist(EntityHome_$$_javassist_17.java)
  at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
  at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
  at java.lang.reflect.Method.invoke(Method.java:585)
  at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:329)
  at org.jboss.el.util.ReflectionUtil.invokeMethod(ReflectionUtil.java:342)
  at org.jboss.el.parser.AstPropertySuffix.invoke(AstPropertySuffix.java:58)
  at org.jboss.el.parser.AstValue.invoke(AstValue.java:96)
  at org.jboss.el.MethodExpressionImpl.invoke(MethodExpressionImpl.java:276)
  at com.sun.facelets.el.TagMethodExpression.invoke(TagMethodExpression.java:68)
  at javax.faces.component.MethodBindingMethodExpressionAdapter.invoke(MethodBindingMethodExpressionAdapter.java:77)
  at com.sun.faces.application.ActionListenerImpl.processAction(ActionListenerImpl.java:91)
  at javax.faces.component.UICommand.broadcast(UICommand.java:383)
  ... 44 more
Caused by: java.sql.BatchUpdateException: failed batch
  at org.hsqldb.jdbc.jdbcStatement.executeBatch(Unknown Source)
  at org.hsqldb.jdbc.jdbcPreparedStatement.executeBatch(Unknown Source)
  at org.jboss.resource.adapter.jdbc.WrappedStatement.executeBatch(WrappedStatement.java:519)
  at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:48)
  at org.hibernate.jdbc.AbstractBatcher.executeBatch(AbstractBatcher.java:246)
  ... 86 more

This is for the case where I have a unique constraint on two columns in that table. It used to work for simple constraints and I could catch a javax.persistence.EntityExistsException in pages.xml.

I use a managed hibernate session. Should I be using something else?

[kariem]

Sorry, forgot to add some information. It used to work before I had to use sequences for the generation of the entity's surrogate ID. I assume this is the reason for the BatchUpdateException.

The generated SQL statements are:

1. select next value for …


2. insert into …

Instead of only a single insert. Any hints?

[kariem]

I just came up with a (rather hacked) solution. I am a little bit constrained in time, so I could not wait for a good hint. If somebody could provide feedback I would be very grateful.

Because most of this stuff is implemented in EntityHome, I thought I could start there and extended this class. In this new class I override persist() and all my EntityHome instances inherit now from the new class instead of EntityHome.

The new persist method checks all potential unique constraints in my entities. This means I check @Table, @Column, @JoinColumn in my entity classes (don't have any other unique constraints than those).

If I have found unique constraints in the entity to persist, I create a Criteria for exactly these constraints and populate it with the properties from my instance. If this criteria finds any entities in the DB, I add an appropriate faces message and return "" instead of "persist".

I know, it looks like a hack, but it works even for multiple columns in a single constraint (the criteria API is great). Anybody with pointer to a better solution?

[nickarls]

I think it is impossible to validate something so completely that you can guarantee successful insert. In some cases, no matter what you do, someone else can insert something into the DB between validation and insert causing a constraint violation.

In most cases I would just recommend to handle the exception like any other.