Liz Douglass


leave a comment »

We are using iBATIS on my current project for XML-based object relational mapping. I downloaded the developer guide recently because I was interested to learn about the tool and how this part of our code works. This is what I found out:

1. Repositories
We have ten or so repositories in our code base that all look something like this:

import my.common.util.Maps;
import my.domain.Account;
import my.domain.OrderInfo;
import my.domain.OrderInfoState;
import org.springframework.orm.ibatis.SqlMapClientOperations;

import java.util.List;
import java.util.Map;

public class OrderRepository {

    private final SqlMapClientOperations template;

    public OrderRepository(SqlMapClientOperations template) {
        this.template = template;

    public void insert(OrderInfo orderInfo) {
        template.insert("insertOrder", orderInfo);

    public List findOtherPendingOrdersForAccount(Account account, String orderIdToExclude) {
        Map params = Maps.create();
        params.put("accountNumber", account.getNumber());
        params.put("orderIdToExclude", orderIdToExclude);
        params.put("orderRequestPendingStates", OrderInfoState.PENDINGSTATES);

        return template.queryForList("findPendingOrdersForAccount", params);

    public void setCompletedNotificationSent(String orderId) {
        template.update("setOrderAccountType", orderId);

Each repository has a dependency on SqlMapClientTemplate which according to the Spring API is a “helper class that simplifies data access via the iBATIS SqlMapClient API, converting checked SQLExceptions into unchecked DataAccessExceptions”. We use the template to execute SQL statements that are defined in XML SQL Map files – but let’s get back to that in a moment.

2. Next let’s have a look at how this is all wired together in Spring:

<bean id="OrderRepository">
 <constructor-arg ref="sqlMapClientTemplateForApp"/>

<bean id="sqlMapClientTemplateForApp">
 <property name="sqlMapClient" ref="sqlMapClientForApp"/>

<bean id="sqlMapClientForApp">
 <property name="configLocation" value="classpath:/sqlmap.xml"/>
 <property name="dataSource" ref="appDataSource"/>

<bean id="appDataSource" destroy-method="close">
 <property name="driverClassName" value="${my.datasource.driver}"/>
 <property name="url" value="${my.datasource.url}"/>
 <property name="username" value="${my.datasource.username}"/>
 <property name="password" value="${my.datasource.password}"/>

Bean sqlMapClientForApp has a property configLocation which has been set to "classpath:/sqlmap.xml". Inside sqlmap.xml we have a few custom handlers (more on that later too) as well as a list of XML SQL Map files (one for each repository):

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig PUBLIC "-// SQL Map Config 2.0//EN"

 <typeHandler javaType="my.domain.Money"

 <sqlMap resource="sqlmaps/orderinfo.xml"/>


4. The juicy bit – the SQL Map files

These are XML descriptor files that are used by the iBATIS Data Mapper to map JavaBeans to SQL prepared statements. As explained in the developer guide, iBATIS accepts parameters as input into prepared statements and builds result objects from the ResultSet returned. Below is a cut down version of a typical iBATIS SQL Map configuration file from our code. It has three statements defined – each corresponding to the methods in the repository class above. I have annotated the file with comments explaining the iBATIS syntax:

<sqlMap namespace="orderInfo">

 <!-- Type aliases for fully qualified class names. -->
 <typeAlias alias="orderInfo" type="my.domain.OrderInfo"/>
 <typeAlias alias="account" type="my.domain.Account"/>

 <resultMap id="orderInfoResult" class="orderInfo"> <!-- This class will be instantiated and populated from the data in the ResultSet -->
 <!-- This controls that data that is taken from the ResultSet and how it maps to columns -->
 <result property="orderId" column="order_id"/>
 <result property="orderDate" column="order_date"/>
 <result property="userId" column="user_id"/>
 <result property="account.number" column="account_id"/>
 <result property="account.type" column="account_type"/>
 <result property="" column="account_name"/>

 <sql id="order_info_attributes"> <!-- This fragment can be included in the SQL statements. See below -->,

 <!-- This select query uses the resultMap declared above-->
 <!-- This is a mapped statement. These can have param maps as input and result maps as output -->
 <select id="findPendingOrdersForAccount"
 resultMap="orderInfoResult"> <!--The parameterClass "map" is an alias for a java.util.Map -->
 <!--The SQL can be anything that is valid for the JDBC driver -->
 <include refid="order_info_attributes"/> <!-- This includes the order_info_attributes fragment above -->
 FROM order_info INNER JOIN order_request ON order_info.order_id = order_request.order_id
 WHERE order_info.account_id = #accountNumber#  <!-- This is an inline parameter map reference-->
 AND order_info.order_id != #orderIdToExclude#
 AND (
 order_request.state IN (
 <iterate property="orderRequestPendingStates" conjunction=",">
 </iterate> <!-- This is an iterate dynamic element. The property attribute defines the array or java.util.Collection or java.util.Iterator type to be iterated over -->

 <insert id="insertOrder"
 parameterClass="orderInfo">  <!-- The parameterClass is used to restrict the objects that can be passed as an input parameter -->
 INSERT INTO order_info(order_id, order_date, user_id, account_id, account_type, account_name)
 VALUES(#orderId#, #orderDate#, #userId#, #account.number#, #account.type#,
 <selectKey resultClass="int" keyProperty="id">  <!-- iBATIS supports auto-generated keys -->

 <update id="setOrderAccountType" parameterClass="string">
 UPDATE order_info SET account_type = blah WHERE order_id = #value#


5. Custom type handlers

Some of our repositories use the iBATIS custom type handlers declared in the sqlmap.xml file (above) to map columns to specific types in our domain. For example the sellPrice field on Product is Money type:

<resultMap id="productMapping">
 <result property="sellPrice" column="sell_price"/>
 <result property="status" column="status"/>

iBATIS will use the MoneyTypeHandler below to create a Money instance from the sell_price value. Each one of these handlers implements the TypeHandlerCallback interface.

import my.domain.Money;
import com.ibatis.sqlmap.client.extensions.ParameterSetter;
import com.ibatis.sqlmap.client.extensions.ResultGetter;
import com.ibatis.sqlmap.client.extensions.TypeHandlerCallback;

import java.math.BigDecimal;
import java.sql.SQLException;
import java.sql.Types;

public class MoneyTypeHandler implements TypeHandlerCallback {

    public void setParameter(ParameterSetter parameterSetter, Object obj) throws SQLException {
        if (obj == null) {
        } else {
            Money money = (Money) obj;

    public Object getResult(ResultGetter resultGetter) throws SQLException {
        BigDecimal value = resultGetter.getBigDecimal();
        return (value != null) ? new Money(value) : Money.ZERO;

    public Object valueOf(String value) {
        return value;

Written by lizdouglass

September 13, 2009 at 7:09 am

Posted in Uncategorized

Tagged with , ,

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: