(Quick Reference)

4 Optimistic locking - Reference Documentation

Authors: Franjo Žilić

Version: 0.0.2

4 Optimistic locking

Optimistic locking

Optimistic locking in this plugin is based on Hibernate optimistic locking idea.

To enable optimistic locking for our Person class we have to add id and version properties and static that enables optimistic locking for this class.

package org.grails.mybatis.exampleclass Person {
  def static useOptimisticLocking = true
  Integer id
  Integer version
  String firstName
  String lastName
  Integer age
}

We have to modify our mapping xml to support optimistic locking and new properties. Simply querying for object version is not enough so we should also add an insert and update query.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="org.grails.mybatis.example.person">  <select id="loadCurrentVersionOfPersonById" resultType="integer" parameterType="integer">
    SELECT VERSION
    FROM PERSON
    WHERE ID = #{value}
  </select>
  <select id="loadAllPersonList" resultMap="basicPersonMap">
    SELECT * FROM PERSON
  </select>
  <select id="loadPersonById" resultMap="basicPersonMap">
    SELECT * FROM PERSON WHERE ID = #{value}
  </select>
  <resultMap type="org.grails.mybatis.example.Person" id="basicPersonMap">
    <id property="id" column="ID" javaType="Integer" />
    <result property="version" column="VERSION" javaType="Integer"/>
    <result property="firstName" column="FIRST_NAME" />
    <result property="lastName" column="LAST_NAME"/>
    <result property="age" column="AGE"/>
  </resultMap>
  <insert id="insertPerson" parameterType="org.grails.mybatis.example.Person">
    <selectKey keyProperty="id" resultType="integer" order="BEFORE">
      SELECT COALESCE(MAX(ID), 0) + 1 FROM PERSON
    </selectKey>
    INSERT INTO PERSON (ID, VERSION, FIRST_NAME, LAST_NAME, AGE)
    VALUES (#{id}, #{version}, #{firstName}, #{lastName}, #{age})
  </insert>
  <update id="updatePerson" parameterType="org.grails.mybatis.example.Person">
    UPDATE PERSON
    SET VERSION = #{version}, FIRST_NAME = #{firstName}, LAST_NAME = #{lastName}, AGE = #{age}
    WHERE ID = #{id}
  </update>
</mapper>

Now we can modify PersonGatewayTests

package org.grails.mybatis.exampleimport groovy.sql.Sql
class PersonGatewayTests extends GroovyTestCase {
  /**
   * Dependency injection is used to test Gateway artefacts
   */
  def dataSource
  def personGateway
  protected void setUp() throws Exception {
    def sql = new Sql(dataSource)
    sql.execute("""CREATE TABLE PERSON (
    ID INTEGER NOT NULL,
    VERSION INTEGER NOT NULL,
    FIRST_NAME VARCHAR(30),
    LAST_NAME VARCHAR(30),
    AGE INTEGER,
    PRIMARY KEY (ID)
    )""")
  }
  protected void tearDown() throws Exception {
    def sql = new Sql(dataSource)
    sql.execute("""DROP TABLE IF EXISTS PERSON""")
  }
  void testDependencies() {
    assert dataSource
    assert personGateway
  }
  void testPersonInsert() {
    Person data = new Person(firstName: 'John', lastName: 'Doe', age: 50)
    personGateway.insertPerson(data);
    def databaseData = new Sql(dataSource).firstRow("SELECT * FROM PERSON")
    assert databaseData["ID"] == 1
    assert databaseData["VERSION"] == 1
    assert databaseData["FIRST_NAME"] == 'John'
    assert databaseData["LAST_NAME"] == 'Doe'
    assert databaseData["AGE"] == 50
    assert data.id == 1
    assert data.version == 1
  }
  void testLoadPersonById() {
    Person data = new Person(firstName: 'John', lastName: 'Doe', age: 50)
    personGateway.insertPerson(data);
    Person fromDb = personGateway.loadPersonById(data.id)
    assert fromDb
    assert fromDb.id == data.id
    assert fromDb.version == data.version
    assert fromDb.firstName == data.firstName
  }
  void testPersonUpdate() {
    Person data = new Person(firstName: 'John', lastName: 'Doe', age: 50)
    personGateway.insertPerson(data);
    assert data.version == 1
    assert data.age == 50
    Person fromDb = personGateway.loadPersonById(data.id)
    assert fromDb.id == 1
    assert fromDb.version == 1
    assert fromDb.age == 50
    data.age = 33
    personGateway.updatePerson(data)
    assert data.version == 2
    assert data.age == 33
    fromDb = personGateway.loadPersonById(data.id)
    assert fromDb.version == 2
    assert fromDb.age == 33
    new Sql(dataSource).execute("UPDATE PERSON SET VERSION = 7, AGE = 29 WHERE ID = 1")
    shouldFail {
      data.firstName = 'Mark'
      personGateway.updatePerson(data)
    }
  }
  void testLoadAllPersonList() {
    personGateway.insertPerson(new Person(firstName: 'John', lastName: 'Doe', age: 20))
    personGateway.insertPerson(new Person(firstName: 'Mark', lastName: 'Miles', age: 23))
    def results = personGateway.loadAllPersonList()
    assert results
    assert results.size() == 2
    assert results[0] instanceof Person
    assert results[0].firstName == 'John'
    assert results[1].firstName == 'Mark'
  }
}

<< 3Working with MyBatis

5Multivendor database support >>

4 Optimistic locking - Reference Documentation

4 Optimistic locking

Optimistic locking

Samples