Plugins You can find out about all the publicly available Grails plugins.

MyBatis / iBATIS

  • Authors: null
5 votes
compile "org.grails.plugins:ibatis:1.3.1"





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

$> grails install-plugin ibatis


Impatient? See Quick Start


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:

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

table ACCOUNT    
primary keyvarchar(100)varchar(10), one of 'savings' or 'checking'date

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

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 "-// Mapper 3.0//EN"

<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.