Installation

To install the iBATIS plugin, run this target within your Grails application:

$> grails install-plugin ibatis

Introduction

The MyBATIS project is a persistence framework developed by Clinton Begin. It's SQL-oriented; that is, developers write SQL statements to perform all of the ORM operations.

Prior to 2010, MyBatis was hosted at Apache and was known as iBATIS. You may still notice this legacy in the documentation and code (including the name of this plugin).

Why would I use MyBatis?

The question arises: why would we want to use another persistence framework when Grails already includes GORM? GORM is, arguably, the most productive ORM implementation available. The underlying Hibernate framework is extremely flexible and can be fit to any 'corner case' persistence issue.

GORM and MyBatis are complementary approaches. Generally, prefer GORM over MyBatis. However, there are a few situations where you may find MyBatis to express the concepts more clearly. Here are a few examples:

• Working with stored procedures, such as in a Transaction Script architecture
• Projects where database developers need to perform fine-grained tuning of SQL
• Porting of legacy applications to Grails

Where can I find out more about MyBatis?

A great source of information is the MyBatis User Guide.

Table Data Gateway architecture

This plugin implements the Table Data Gateway pattern, as described in the excellent Patterns of Enterprise Application Architecture book by Martin Fowler. To excerpt:

Mixing SQL in application logic can cause several problems.
Many developers aren't comfortable with SQL, and many who are comfortable may not write it well.
Database administrators need to be able to find SQL easily so they can figure out how to
tune and evolve the database.
A Table Data Gateway holds all the SQL for accessing a single table or view:
selects, inserts, updates, and deletes.
Other code calls its methods for all interaction with the database.

One of the consequences of this design is that our application logic must have knowledge of the underlying database schema. The class that fills this role is called the Gateway. The objects passed to and from the Gateway are POGOs (or POJOs).

Quick Start

Set up the DataSource and Test data

To get us started quickly, we'll use a preconfigured HSQLDB instance. Download the test files here and extract into your main project folder. You'll now have these files:

testDb.properties
testDb.script
accounts.csv

These files are typical for an HSQLDB instance. testDb.script defines the test table we'll use for this example.

The data file accounts.csv contains our test data

0,Matthew Bellamy,checking,2009-01-01
1,Matthew Bellamy,savings,2009-01-05
2,Christopher Wolstenholme,checking,2009-04-22
3,Dominic Howard,savings,2009-10-15

The MyBatis plugin uses the standard DataSource.groovy file to define the connections, just like GORM. Changing our application to use our test database is just a matter of modifying grails-app/conf/DataSource.groovy to set the proper connection properties:

environments {
        …
        test {
                dataSource {
                        dbCreate = "update"
                        url = "jdbc:hsqldb:file:testDb;shutdown=true"
                }
        }
        …
}

Create our POGO

Now we'll define a Groovy object to store our table data. Create the file src/groovy/com/example/AccountInfo.groovy :

package com.example
enum AccountType { checking, savings }
class AccountInfo {
    Long id
    String accountHolder
    AccountType accountType
    Date inceptionDate
}

Install the iBATIS plugin

Within your Grails application, install the MyBatis plugin using the command

grails install-plugin ibatis

You should now have a new Grails target 'create-gateway'.

Create a gateway class

The create-gateway target will generate our gateway class, iBATIS mapping file, and integration test.

grails create-gateway com.example.Account

Under grails-app/gateways , you should see the following files:

user ~ $  ls grails-app/gateways/com/example
  AccountGateway.groovy
  account.xml

If you look at the contents of AccoutGateway.groovy , you'll see only a class definition. We won't define our methods here. Rather, we'll add operations to the associated mapping file.

Add operations to the mapping file

Our iBATIS mapping file, account.xml , will define all of our database operations. Each operation defined here gets automatically exposed as a method on our gateway class.

This quick start is a bit contrived, given that all of the operations we will define can more easily be expressed in GORM. See the Introduction for tips about when you might want to use iBATIS over GORM.

<?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="com.example.account">
    <select id="getAccountByID" resultMap="accountResultMap">
        select id, account_holder, account_type, inception_date
        from account
        where id = #{value,jdbcType=NUMERIC}
    </select>
    <select id="getAccountsOfType" parameterType="string" resultMap="accountResultMap">
        select id, account_holder, account_type, inception_date
        from account
        where account_type = #{value,jdbcType=VARCHAR}
        order by account_holder
    </select>
    <select id="getAccountsOpenedSince" parameterType="date" resultMap="accountResultMap">
        select id, account_holder, account_type, inception_date
        from account
        where inception_date &gt; #{value,jdbcType=DATE}
        order by inception_date
    </select>
    <update id="updateAccount" parameterType="com.example.AccountInfo">
        update account
        set account_holder = #{accountHolder},
            account_type = #{accountType,jdbcType=VARCHAR},
            inception_date = #{inceptionDate}
        where id = #{id}
    </update>
    <resultMap id="accountResultMap" type="com.example.AccountInfo">
      <result column="account_holder" property="accountHolder"/>
      <result column="account_type" property="accountType"/>
      <result column="inception_date" property="inceptionDate"/>
    </resultMap>
</mapper>

The above syntax might seem slightly off to experienced iBATIS 2.x users. This is the new iBATIS 3.x format; you'll find it similar (but not totally compatible) with the 2.x syntax.

Integration Test

We'll put it all together in the integration test. The create-gateway Grails target created an integration test, test/integration/com/example/AccountGatewayTests.groovy :

package com.example
import grails.test.*
/* In these tests, the property 'gateway' is provided by the superclass */
class AccountGatewayTests extends GatewayIntegrationTest {
  /* Our simplest case: call the 'getAccountByID' operation to retrieve a single value */
  void testAccountById() {
    def chris = gateway.getAccountByID(2)
    // from our test data, "2,Christopher Wolstenholme,checking,2009-04-22"
    assert chris?.accountHolder == 'Christopher Wolstenholme'
    assert chris?.accountType == AccountType.checking
    assert !gateway.getAccountByID(-5)
  }
  /* Multiple result values: since we included the plural form 'Accounts'
     in our operation name, we expect multiple rows */
  void testAccountsOfType() {
    def checkingAccounts = gateway.getAccountsOfType(AccountType.checking)
    assert checkingAccounts?.size() == 2
  }
  /* Note that in the operation we had to escape the '>' sign */
  void testAccountsOpenedSince() {
    def referenceDate = Calendar.getInstance(TimeZone.getTimeZone('GMT'))
    referenceDate.clear()
    referenceDate.set(2009, Calendar.MARCH, 1)
    def newAccounts = gateway.getAccountsOpenedSince(referenceDate.time)
    assert newAccounts?.size() == 2
    newAccounts.each {
      assert referenceDate.time.before(it?.inceptionDate)
    }
  }
  /* We can also update */
  void testUpdate() {
    def dominic = gateway.getAccountByID(3)
    assert dominic?.accountType == AccountType.savings
    dominic.accountType = AccountType.checking
    gateway.updateAccount(dominic)
    assert gateway.getAccountByID(3).accountType == AccountType.checking
  }
}

Run the Grails target test-app and look at the generated file target/test-reports/html/index.html . You should see success for all our test cases.

What is iBATIS?

The iBATIS project is a persistence framework developed by Clinton Begin. It's SQL-oriented; that is, developers write SQL statements to perform all of the ORM operations.

Why would I use iBATIS?

The question arises: why would we want to use another persistence framework when Grails already includes GORM? GORM is, arguably, the most productive ORM implementation available. The underlying Hibernate framework is extremely flexible and can be fit to any 'corner case' persistence issue.

GORM and iBATIS are complementary approaches. Generally, prefer GORM over iBATIS. However, there are a few situations where you may find iBATIS to express the concepts more clearly. Here are a few examples:

• Working with stored procedures, such as in a Transaction Script architecture
• Projects where database developers need to perform fine-grained tuning of SQL
• Porting of legacy applications to Grails

Where can I find out more about iBATIS?

A great source of information is the iBATIS User Guide. There is also a large list of articles and books on iBATIS.

This plugin uses iBATIS version 3.x, so some of the 2.x materials may use a slightly different syntax.