I'm Rob Blake

JPA: Ensuring versioning of entities when using Query.executeUpdate

The persistence layer for our code at The Law Wizard makes use of optimistic locking to determine if an entity has changed during the period where a user has been working on it. This helps to ensure that the view the user sees is always consistent with what is in the database. 

One problem I came across is how to update multiple versioned entities in a single query whilst ensuring the benefits of optimistic locking are maintained. It turns out the answer is extremely simple, yet it took me while to find it. I thought I would share in case others have the same problem.

First off I’m assuming you’re using Hibernate as your persistence provider.

Imagine you have an entity such as this:

public class MyEntity 
{
       @ManyToOne(fetch=FetchType.Lazy, optional=true)
       private OtherEntity otherEntity;

       @SuppressWarnings("unused")
       @Version
       private long version;

       public OtherEntity getOtherEntity()
       {
               return otherEntity;
       }

       public void setOtherEntity(OtherEntity otherEntity)
       {
               this.otherEntity = otherEntity;
       }
}

The relationship between MyEntity and OtherEntity is uni-directional, i.e. OtherEntity doesn’t know about the MyEntitys that have a reference to it.

Elsewhere in your application it is possible to delete OtherEntity. If this happens, you would like all MyEntitys with a reference to the OtherEntity being deleted, to have their reference set to null. However you will note that MyEntity is versioned for optimistic locking purposes. If the reference to OtherEntity is set to null, you want the version of MyEntity to be incremented.

A horrible way to do this is to load all MyEntity into the PersistenceContext where the OtherEntity is the one that is being deleted, set the reference to null and then persist them back into the database. For example:

for(MyEntity m : em.createQuery("select m from MyEntity m where m.otherEntity=:otherEntity").setParameter("otherEntity",otherEntity).getResultList())
{
     m.setOtherEntity(null);
}

This ensures that versioning works, but depending on the number of MyEntity referencing OtherEntity, you’ve potentially got a lot of database traffic.

Another way to do this would be to use a query to update all OtherEntitys such as the following:

em.createQuery("update MyEntity m set m.otherEntity=null where m.otherEntity=:otherEntity).setParameter("otherEntity", otherEntity).executeUpdate();

That works, but with one major problem. The version of all MyEntity affected by the query is not updated. I was considering writing my own query to update the versions of all MyEntity when I found this little nugget buried deep in the Hibernate documentation:

In keeping with the EJB3 specification, HQL UPDATE statements, by default, do not effect the version or the timestamp property values for the affected entities. However, you can force Hibernate to reset the version or timestamp property values through the use of a versioned update.

So what does that mean? Well simply re-write the above query to the following:

em.createQuery("update versioned MyEntity m set m.otherEntity=null where m.otherEntity=:otherEntity).setParameter("otherEntity", otherEntity).executeUpdate();

The versions of any affected entities are incremented ensuring that your optimistic locking strategy will work without a hitch.