<< 3Working with MyBatis
(Quick Reference)

4 Plugin features - Reference Documentation

Authors: Franjo Žilić

Version: 0.0.3

Table of Contents

4.1Optimistic locking
4.2Multivendor database dynamic querry support
4.3Custom TypeHandlers

4 Plugin features

MyBatis plugin has extra features which can be used if needed

Features:

  • Optimistic Locking
  • Multivendor database dynamic querry support
  • Custom TypeHandlers

4.1 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.example
class 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.example
import 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'
  }
}

4.2 Multivendor database dynamic querry support

Configuration

Add following to your configuration if you want to support H2, Microsoft SQL and IBM Informix databases

mybatis.multivendor.enabled = true
mybatis.multivendor.mapping = [
    'Microsoft SQL Server': 'mssql',
    'H2': 'h2',
    'Informix Dynamic Server': 'informix'
]

Mapper support

Let's say you want to query a databse for persons full name. You have to concat two fields in legacy PERSON table, here is sample mapper to do just that.

<?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">
  <sql id="fullNameDatabaseSpecificQuery">
    <choose>
      <when test="_databaseId == 'mssql'">FIRST_NAME + ' ' + LAST_NAME</when>
      <when test="_databaseId == 'informix'">FIRST_NAME || ' ' || LAST_NAME</when>
      <otherwise>CONCAT(FIRST_NAME, ' ', LAST_NAME</otherwise>
    </choose>
  </sql>
  <select id="loadPersonFullNameById>" resultType="String" parameterType="Integer">
    SELECT <include refid="fullNameDatabaseSpecificQuery"/>
    FROM PERSON
    WHERE ID = #{value}
  </select>
</mapper>

4.3 Custom TypeHandlers

What are TypeHandlers

Type handlers are used for data transformation. For example, you are connecting to a legacy database that stores list of data in a single VARCHAR field, and each item is enclosed with brackets. You have to extract that data, and you can do it with a custom type handler.

Writing a TypeHandler

TypeHandlers are artefact classes which should be placed in grails-app/typeHandlers and end with "TypeHandler"

package org.grails.mybatis.example
import java.sql.CallableStatement
import java.sql.PreparedStatement
import java.sql.ResultSet
import java.sql.SQLException
import org.apache.ibatis.type.BaseTypeHandler
import org.apache.ibatis.type.JdbcType
import org.apache.ibatis.type.MappedJdbcTypes
import org.apache.ibatis.type.MappedTypes
@MappedJdbcTypes(JdbcType.VARCHAR)
@MappedTypes([List])
class ExampleTypeHandler extends BaseTypeHandler<List<String>>{
  def static dataSourceName = 'dataSource'
  @Override
  public void setNonNullParameter(PreparedStatement statement, int index, List<String> parameter, JdbcType jdbcType) throws SQLException {
    def builder = "" as StringBuilder
    parameter.each {
      builder += "[$it]"
    }
    statement.setString(index, builder.toString())
  }
  List<String> decodeValue(String value) {
    def matcher = value =~ /[(.*?)]/
    def results = []
    matcher.each {
      results << it[1]
    }
    return results
  }
  @Override
  public List<String> getNullableResult(ResultSet resultSet, String columnName) throws SQLException {
    def encoded = resultSet.getString(columnName)
    return decodeValue(encoded)
  }
  @Override
  public List<String> getNullableResult(ResultSet resultSet, int columnIndex) {
    def encoded = resultSet.getString(columnIndex)
    return decodeValue(encoded)
  }
  @Override
  public List<String> getNullableResult(CallableStatement statement, int columnIndex) throws SQLException {
    def encoded = statement.getString(columnIndex)
    return decodeValue(encoded)
  }
}

You have to register that type handler to a specific MyBatis result using typeHanlder attribute in mapper file.

Custom Enum type handlers

From time to time old databases store some enumerated information in a specific way (single character). If you need to map such values to your enums it can be done by extending AbstractEnumTypeHandler class provided in this plugin

package org.grails.plugins.mybatis.enums
enum SampleEnum {
  VALUE_ONE('O'), VALUE_TWO('T')
  String dbCode
  SampleEnum(dbCode){
    this.dbCode = dbCode
  }
}

package org.grails.plugins.mybatis.enums
class SampleEnumTypeHandler extends AbstractEnumTypeHandler<SampleEnum> {
  SampleEnumTypeHandler() {
    super(SampleEnum, "dbCode")
  }
}
<< 3Working with MyBatis
Quick Reference (hide)

Samples

Gateway
Mapper XML
MyBatisDomainClass
TypeHandlers