Friday, November 5, 2010

Optimistic locking on iBatis

I know that it's little outdated, but I needed this recently on one project
and couldn't find it anywhere in a Ctrl+C / Ctrl+V form.
How to implement optimistic locking under iBatis when there is no standard support for it.

It's quite simple. Implement it by your self.
  • implement custom OptimisticLockException or reuse the one from JPA package: javax.persistence
  • add new db field named 'version' into entity's table. it should be numerical non-null field with default value defined as 1
  • add new field into Entity's class: int version
  • Update DAO interface to throw OptimisticLockException (so anybody using this DAO can change his code accordingly)
public interface EntityDao {
    void createEntity(Entity entity);
    Entity selectEntryById(Long id);
    void updateEntity(Entity entity) throws OptimisticLockException;
    void deleteEntity(Entity entity);
  • Update DAO class to process version fields
public class EntityDaoImpl extends SqlMapClientDaoSupport implements EntityDao {

public void createEntity(final Entity entity) {
    getSqlMapClientTemplate().insert("insertEntity", entity);

public Entity selectEntryById(final Long id) {
    return (Entity) getSqlMapClientTemplate().queryForObject("findEntityById", id);

public void updateEntity(final Entity entity) throws OptimisticLockException {
    final int oldVersion = entity.getVersion();
    final int newVersion = oldVersion + 1;

    final Map<String, Object> params = new HashMap<String, Object>();
    params.put("entity", entity);
    params.put("oldVersion", oldVersion);
    params.put("newVersion", newVersion);

    int updateCount = getSqlMapClientTemplate().update("updateEntity", params);

    if (updateCount == 0) {
        throw new OptimisticLockException("trying to update database with obsolete Entity");


public void deleteEntity(Entity entity) {
    getSqlMapClientTemplate().delete("deleteEntity", entity);
  • Update statements file
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap PUBLIC "-// SQL Map 2.0//EN" "">

 <typeAlias alias="Entity" type="yourPackage.domain.Entity"/>

 <resultMap id="EntityMap" class="Entity">
     <result column="id" property="id"/>
     <result column="value" property="value"/>
     <result column="version" property="version"/>

 <insert id="insertEntity" parameterClass="Entity">
     INSERT INTO entity (id, value, version) VALUES (#id#, #value#, 1)

 <select id="findEntityById" parameterClass="java.lang.Long" resultClass="Entity" resultMap="EntityMap">
         SELECT id, value, version FROM entity WHERE id=#value#

 <update id="updateEntity" parameterClass="java.util.Map">
     UPDATE entity
         AND version=#oldVersion#

 <delete id="deleteEntity" parameterClass="Entity">
     DELETE FROM entity WHERE id=#id#