Working on a Grails project that needs to access a legacy database, it proved quite time-consuming to implement optimistic locking in a way that can be handled by Hibernate. Optimistic locking used to be implemented within (legacy) application code and should now be handled by Hibernate completely on its own. For each entity, the legacy schema uses two nullable columns to both trace creation and last updated times as well as to provide optimistic locking capabilities. TS_INSERT holds the Timestamp the entity has been created and TS_UPDATE (initially null) records subsequent mutations to an entity. Just disabling optimistic locking (as well being advertised here) keeps away run time errors but results in an application that cannot meet production quality demands without additional (awkward) application code.

Annotating the TS_UPDATE column with @Version almost did the trick, except it was unable to successfully update entities that had not yet been updated (TS_UPDATE is null). The reason for this behavior is burried within method toStatementString Hibernate Update- and Delete-Code that use TS_UPDATE=? as part of the sql (prepared) statements in order to check for matching old version values. Of course, this condition will always yield false for null values (remember: null = anything including null = null always yields false).

How to cause TS_UPDATE=? to yield true?

Short of changing Hibernate source code I designed a work-around, that effectively changes the TS_UPDATE=? fragment so that it yields true, even for null values. The work-around replaces the TS_UPDATE=? with COALESCE(TS_UPDATE,{ts ‘1900-01-01 00:00:00’})=COALESCE(?,{ts ‘1900-01-01 00:00:00’}). I use the COALESCE function, because it is specified by Ansi SQL 92 Standard and thus should be implemented by most database vendors.

How to alter Hibernate sql statements?

Hibernate provides a rather sophisticated set of interceptor methods, that will be called (back) at certain life cycle stages. Using the onPrepareStatement event, arbitrary changes can be applied to the sql statements that are then sent to JDBC for execution. This is what my interceptor looks like (note that it looks for and… as the version condition will always be appended to the primary key condition – todo: this still needs to be verified for persisting collections):

package com.company.fw.hibernate.util.centura;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.hibernate.EmptyInterceptor;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * Fixes a shortcoming within Hibernate: Hibernate cannot handle NULL values
 * within optimistic version columns. The where clause will always include
 * e.g. TS_UPDATE=?. We'll try to fix this by changing the sql to e.g.
 * COALESCE(TS_UPDATE,{ts '1900-01-01 00:00:00'})=COALESCE(?,{ts '1900-01-01 00:00:00'}),
 * so that NULL values match.
 */
public class DBcenturaInterceptor extends EmptyInterceptor {

    private static final String PS = "(and) (ts_update)=?";
    private static final String RS = "$1 COALESCE($2,{ts '1900-01-01 00:00:00'})=COALESCE(?,{ts '1900-01-01 00:00:00'})";
    private static final Pattern P = Pattern.compile(PS, Pattern.CASE_INSENSITIVE);

    private final static Logger LOG = LoggerFactory.getLogger(DBcenturaInterceptor.class);

    public DBcenturaInterceptor() {
        super();
        LOG.info("new DBcenturaInterceptor()");
    }

    /**
     * @param sql the sql to be prepared
     * @return and TS_UPDATE=? replaced by
     *         and COALESCE(TS_UPDATE,{ts '1900-01-01 00:00:00'})=COALESCE(?,{ts '1900-01-01 00:00:00'})
     */
    @Override
    public String onPrepareStatement(final String sql) {
        final Matcher m = P.matcher(sql);
        final String result = m.replaceAll(RS);
        if (LOG.isTraceEnabled() && !result.equals(sql)) {
            LOG.trace("onPrepareStatement(" + sql + ") = " + result);
        }
        return result;
    }

}

How to activate the interceptor?

When creating a session, an interceptor can by passed to the Hibernate SessionFactory.openSession(…). As my application is a Grails application, I choose to provide my Interceptor by passing it to the Grails Hibernate session factory, so my interceptor will be used for all and every Hibernate session created within my Grails application. Starting from Grails 1.2, just a single statement is needed within grails-app/conf/spring/resources.groovy:

import com.company.fw.hibernate.util.centura.DBcenturaInterceptor

// Place your Spring DSL code here
beans = {
  entityInterceptor(com.company.fw.hibernate.util.centura.DBcenturaInterceptor)
}

For native Spring applications, have a look at Using a Hibernate Interceptor To Set Audit Trail Properties.

That’s all, take care and have fun 🙂