Spring and Ibatis Tutorial

Continuing my series on series on object-relational mapping, Last week, I promised to discuss Ibatis. This is the first in a series of posts I'm planning to write on Ibatis and several of its poorly-documented advanced features.

Because of the various steps involved and my desire to keep these posts readably short, this post will do little more than explain the main features of Ibatis and then give you the sample data and code to connect to Ibatis and do some simple queries. Future posts will detail more advanced features once the foundation is set.

Ibatis is a library for retrieving and persisting objects to and from a database. Firstly, there are a few things it isn't or doesn't do:

  • It isn't an ORM in the strictest sense. It uses SQL. To me, a key requirement of being a "true" ORM is that a true ORM will generate the SQL for you thus (hopefully) providing some database vendor independence;
  • It does not implicitly persist objects like JPA does; and
  • It doesn't require any advanced Java features such as load-time weaving;

Now what it is and what it does:

  • It is extremely lightweight;
  • It can be viewed as a JDBC framework rather than an ORM;
  • It has what is probably the best dynamic SQL framework I've seen;
  • Easy to learn for anyone who has learnt SQL because it is SQL. There is no proprietary query language to learn;
  • It is a POJO persistence framework in the purest sense: there are no annotations; and
  • It stores queries in external XML files. Some will view this as a disadvantage. I do not. External XML files are easy to search (and filter by file type), easy to read, the queries aren't buried in code with a billion string concatenations (like Spring JDBC can end up) and it's relatively easy to cut and paste queries into a SQL tool to test them, typically much easier than SQL embedded in code is.

If you look around the Web you'll see some more claims made about Ibatis:

  • It has no object cache;
  • It does not do lazy loading of related entities; and
  • It does not handled mapped superclasses/discriminated types.

Each of the above claims is wrong. But more on that in later posts. First, the basics. You will need:

Note: The Spring with dependencies download includes all the above required libraries (and a whole lot more) with the versions validated for that particular Spring release.

Spring isn't strictly required for this and the examples will be in a J2SE environment but with Spring the examples can easily be dropped into a typical Web application environment where Spring is near-ubiquitous.

As an alternative, all of the above can be resolved with Maven, which is left as an exercise for the reader.

Create a user/schema in the Oracle admin tool (which is just a Web page). Make sure you give it the CONNECT and RESOURCE roles and the CREATE TABLE and CREATE SEQUENCE privileges.

I use the following conventions with Oracle:

  • Keywords are always in UPPERCASE;
  • Database object names are always in lowercase;
  • No spaces will be put in database object names;
  • Underscores will replace spaces (ie there won't be any camel case conventions that are common on, say, SQL Server);
  • Primary keys will pretty much always be named 'id';
  • Referential integrity will be enforced;
  • Integer values (including table ids) will generally always be NUMBER(19,0). The reason for this is that this will fit in a 64-bit signed integer thus allowing the Java long type to be used instead of the more awkward BigInteger;
  • Despite the misnomer of appending "_number" to some column names, the type of such columns will be VARCHAR2 not a number type. Number types are reserved for primary keys and columns you do arithmetic on;
  • I always use a technical primary keys (Item 3); and
  • Each table will have its own sequence for key generation. The name of that sequence will be <table_name>_seq.

The above is stated purely so you can more easily follow what I'm doing and I would certainly recommend such practices but opinions will vary. Use whatever standard you're comfortable with it. Just be consistent.

Schema and Sample Data

  party_type CHAR(1) NOT NULL,
  name VARCHAR2(200)

  title VARCHAR2(30),
  given_names VARCHAR2(80),
  last_name VARCHAR2(80),
  date_of_birth TIMESTAMP,
  FOREIGN KEY (id) REFERENCES party (id)

CREATE TABLE company (
  company_number VARCHAR2(20),
  state_of_incorporation VARCHAR2(20),
  FOREIGN KEY (id) REFERENCES party (id)

CREATE TABLE contact (
  party_id NUMBER(19,0) NOT NULL,
  contact_type CHAR(1) NOT NULL,
  details VARCHAR2(100) NOT NULL,
  FOREIGN KEY (party_id) REFERENCES party (id)

CREATE SEQUENCE contact_seq;

INSERT INTO party VALUES (party_seq.NEXTVAL, 'P', 'Mr Tom Smith');
INSERT INTO person VALUES (party_seq.CURRVAL, 'Mr', 'Tom', 'Smith', '11-JUL-1970');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'E', 'tom.smith@example.com');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'P', '919-223-1234');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'M', '919-223-1235');
INSERT INTO party VALUES (party_seq.NEXTVAL, 'P', 'Ms Susan Jones');
INSERT INTO person VALUES (party_seq.CURRVAL, 'Ms', 'Susan', 'Jones', '27-NOV-1955');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'E', 'susan.jones@example.com');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'P', '919-223-1236');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'M', '919-223-1237');
INSERT INTO party VALUES (party_seq.NEXTVAL, 'C', 'Acme Inc');
INSERT INTO company VALUES (party_seq.CURRVAL, '123 456 789', 'WA');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'E', 'roger@acme.com');
INSERT INTO contact VALUES (contact_seq.NEXTVAL, party_seq.CURRVAL, 'W', '919-223-1238');


It's typical (and good practice) to externalize things like connection details as they often vary between environments (eg development, test, production).



Top-level Ibatis configuration. Not much needed here because Spring will do most of it. For now we're just loading one set of queries in Party.xml.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMapConfig
        PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
    <sqlMap resource="com/cforcoding/Party.xml"/>


This contains some queries that we're doing on Party entities. The organization and naming of queries is entirely arbitrary. I suggest you pick a convention and stick with it. I put things related to an entity in a namespace and an XML file with that entity's name. Typically that file is in the same directory as the DAO that uses it.

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE sqlMap
        PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
<sqlMap namespace="Party">

    <select id="selectPerson" parameterClass="long" resultClass="com.cforcoding.Person">
        SELECT  ps.id,
                pt.party_type type,
                ps.given_names givenNames,
                ps.last_name lastName,
                ps.date_of_birth dateOfBirth
        FROM person ps
        JOIN party pt ON ps.id = pt.id
        WHERE ps.id = #value#

    <update id="updateParty" parameterClass="com.cforcoding.Person">
        UPDATE party
        SET name = #name#
        WHERE id = #id#

    <update id="updatePerson" parameterClass="com.cforcoding.Person">
        UPDATE person
        SET title = #title#,
            given_names = #givenNames#,
            last_name = #lastName#,
            date_of_birth = #dateOfBirth:DATE#
        WHERE id = #id#


There is one select and two updates in this file. You will note that apart from parameters, this is vanilla SQL.

Select Person

This query takes a long (being a person's id) and returns a com.cforcoding.Person object. When the parameter is a single value that can be passed to JDBC directly (eg a primitive, BigDecimal, BigInteger, String, Date) it is referred to with the special name 'value', hence #value# at the end of the select. Lastly, some of the columns selected are aliased to names that match the properties of the Person object (ignoring case). If you do this, Ibatis will automatically map them for you. This is called implicit result mapping.

There are a lot of things you can do with implicit result maps including:

  • Specifying the JDBC type;
  • Specifying the Java type. In this case we're using a concrete value object of known type but that isn't necessarily the case. We could've used a HashMap for the result instead, which obviously would not have automatic Java type discovery. Ibatis will fallback to default behaviour. This allows us to change it;
  • Controlling how NULLs are handled; and
  • Custom type conversion (eg for enums).

This can be taken a step further where explicit result maps are used. These are more verbose but give many more options. More on this in later posts.

Update Party

An extremely simple update statement. Rather than #value#, here you see #name# and #id#. The names within the hashes refer to the object's properties. This is implicit parameter mapping. As with result mapping, you can be more explicit and control things to a greater degree but it's optional.

Update Person

This update is slightly more complex but only adds one thing not yet seen: #dateOfBirth:DATE#. Here I'm being explicit about what JDBC type to use for the conversion from the Java type. This is because we're only interested in the day, month and year, not the hours, minutes and seconds.


<?xml version="1.0" encoding="UTF-8"?>
<beans xmlns="http://www.springframework.org/schema/beans"
     http://www.springframework.org/schema/beans http://www.springframework.org/schema/beans/spring-beans-2.5.xsd
     http://www.springframework.org/schema/tx http://www.springframework.org/schema/tx/spring-tx-2.5.xsd
     http://www.springframework.org/schema/aop http://www.springframework.org/schema/aop/spring-aop-2.5.xsd">

    <bean class="org.springframework.beans.factory.config.PropertyPlaceholderConfigurer">
        <property name="location" value="classpath:database.properties"/>

    <bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
        <property name="driverClassName" value="${database.class}"/>
        <property name="url" value="${database.url}"/>
        <property name="username" value="${database.username}"/>
        <property name="password" value="${database.password}"/>
    <bean id="transactionManager" class="org.springframework.jdbc.datasource.DataSourceTransactionManager">
        <property name="dataSource" ref="dataSource"/>


    <bean id="sqlMapClient" class="org.springframework.orm.ibatis.SqlMapClientFactoryBean">
        <property name="configLocation" value="sqlmap-config.xml"/>
        <property name="dataSource" ref="dataSource"/>
        <property name="useTransactionAwareDataSource" value="true"/>

    <bean id="partyDAO" class="com.cforcoding.PartyDAOImpl">
        <property name="sqlMapClient" ref="sqlMapClient"/>


What have we configured?

  • A PropertyPlaceholderConfigurer that will dynamically replace ${...} expressions in applicationContext.xml with the values from (in this case) database.properties.
  • A DBCP BasicDataSource;
  • A TransactionManager for Spring transactions (rather than, say, Ibatis transactions);
  • We will use Spring's annotation-driven declarative transactions. The next bean enables automatic consumption of @Transactional annotations (more on this later);
  • A factory for producing SqlMapClients; and
  • The PartyDAO for executing the queries and updates.


A value object for the Person entity.

package com.cforcoding;

import java.sql.Date;

public class Person {
    private Long id;
    private String type;
    private String name;
    private String title;
    private String givenNames;
    private String lastName;
    private Date dateOfBirth;

    public Long getId() { return id; } 
    public void setId(Long id) { this.id = id; }
    public String getType() { return type; }
    public void setType(String type) { this.type = type; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
    public String getGivenNames() { return givenNames; }
    public void setGivenNames(String givenNames) { this.givenNames = givenNames; }
    public String getLastName() { return lastName; }
    public void setLastName(String lastName) { this.lastName = lastName; }
    public Date getDateOfBirth() { return dateOfBirth; }
    public void setDateOfBirth(Date dateOfBirth) { this.dateOfBirth = dateOfBirth; }
    // equals(), hashCode() and toString() omitted for brevity


package com.cforcoding;

public interface PartyDAO {
    Person selectPerson(long id);
    void updatePerson(Person person);


package com.cforcoding;

import org.springframework.orm.ibatis.support.SqlMapClientDaoSupport;
import org.springframework.transaction.annotation.Transactional;

public class PartyDAOImpl extends SqlMapClientDaoSupport implements PartyDAO {
    public Person selectPerson(long id) {
        return (Person)getSqlMapClientTemplate().queryForObject("selectPerson", id);

    public void updatePerson(Person person) {
        getSqlMapClientTemplate().update("updateParty", person, 1);
        getSqlMapClientTemplate().update("updatePerson", person, 1);
        System.out.println("Person updated");

All Ibatis operationis are done through a SqlMapClient. PartyDAOImpl extends a Spring class (SqlMapClientDaoSupport) that provides helper functions, one of which is getSqlMapClientTemplate(). This replaces the standard Ibatis exceptions (being Java's checked SQLExceptions) with Spring's DataAccessExceptions (which are runtime exceptions).

I am a firm believer that checked exceptions are a failed experiment but that's another debate.

queryForObject() is passed the name of the query to run and a parameter (being the id). It returns an object that is cast to a Person. As simple as that.

updatePerson() has several features on it. One is the @Transactional annotation. This tells Spring that a transaction is required and to start one if it's not already executing within a transactional context (because the default propagattion is REQUIRED). That method body will execute as one transactional unit and will auto-commit if it compltes successfully. If a runtime exception is thrown, the transaction will be rolled back.

The update() method is very similar to queryForObject() where the first two parameters are the name of the query to run and the parameter to pass. A third optional argument has been specified. This tells Ibatis the number of rows affected should be 1. If the number of rows affected is 0 or more than 1, an exception will be thrown.


package com.cforcoding;

import org.springframework.context.support.ClassPathXmlApplicationContext;

public class Main {
    public static void main(String args[]) {
        ClassPathXmlApplicationContext ctx = new ClassPathXmlApplicationContext("applicationContext.xml");
        PartyDAO partyDAO = (PartyDAO) ctx.getBean("partyDAO");
        Person person = partyDAO.selectPerson(2);

This simple program:

  1. Manually creates a Spring application context by loading applicationContext.xml from the classpath;
  2. Gets the PartyDAO bean from that application context;
  3. Queries a person; and
  4. Makes a simple change to that person, persisting that to the database.


The above is a barebones Spring and Ibatis configuration that can easily be used inside or outside of an application server (or Web container such as Tomcat). The only change necessary for running inside a J(2)EE environment is that a JNDI data source will likely be used instead of a DBCP BasicDataSource but refer to your container documentation for specifics on data access.

The above configuration will be used in upcoming posts on more advanced topics of Ibatis but I hope the above is useful to you. It can be difficult to initially configure Spring if you're unfamiliar with one or more of the underlying frameworks.

Next: Ibatis Inheritance Strategies


Anonymous said...

Very nice article, iBatis rocks!

Dimitris Menounos said...

Nice article.

Maybe you can help me a bit with something. I have built a generic data access layer and implementations of it with hibrnate and jpa. I'd like to also support ibatis. The question is, can I dynamicaly generate the SQL statements at runtime or do I have to have them ready only at compile time?


jim said...
This comment has been removed by a blog administrator.
William Shields said...

@Dimitri: dynamically generating SQL, to me, is a bit like dynamically generating Java code. There's only so much it can do. Dynamic SQL generation is part of the reason JPA is so complex.

What I like about Ibatis is that it can do many of the things of JPA but is really simple and SQL is pretty easy to write.

Now I'm not totally against JPA. Let me make sure that's clear: it has its place. I just think it's probably more complex than it needs to be.

Art Vandalay said...

Bravo! I can respect advocacy posts that include actual, working code and configuration. The implicit SQL-to-object mapping, with the option for switching to explicit mapping, is very convention-over-configuration and choose-your-own-complexity!

For what it's worth, the Spring DAO support applies to other ORM too: http://www.jroller.com/dancruz/entry/spring_dao_integration . (For Hibernate, Spring has a PlatformTransactionManager in which the DAO support "just knows" it's there and uses it.)

I'm anticipating the future posts about accomplishing object caching, lazy fetching of associations, and mapping of inheritance hierarchies.

Eswar said...

Very nice tutorial.

Anonymous said...

Is it possible to have multiple datasources, each with it's own transaction manager?

Anonymous said...

This is what I was looking for!!!

CodingKnight said...

This does not work when outside the container. Outside the container you will not be able to get sqlMapClient

wincity said...

Good article.

Post a Comment