iBatis: SQL Mapper Framework

When it comes to persisting data in Java applications, developers often face a choice between full-blown Object-Relational Mapping (ORM) frameworks like Hibernate and raw JDBC. iBatis occupies a valuable middle ground, offering a SQL Mapper approach that gives you the control of hand-written SQL with the convenience of automated object mapping.

What Is iBatis?

iBatis is a persistence framework that couples objects with stored procedures or SQL statements using an XML descriptor or annotations. Unlike ORM frameworks that generate SQL for you, iBatis lets you write the SQL and simply handles the tedious work of mapping parameters and results.

Note: iBatis was later renamed MyBatis after moving from Apache to Google Code in 2010. The core concepts remain largely the same, so much of this article applies to both.

Why Choose a SQL Mapper?

Full ORM frameworks are powerful, but they can obscure what SQL is actually being executed. iBatis is a great fit when:

• You have existing, complex SQL that you want to preserve.
• You work with legacy databases whose schema you cannot change.
• You need fine-grained control over query performance.
• Your team has strong SQL skills and prefers explicit queries.

Core Concepts

1. The SqlMapConfig

The central configuration file wires up your data source, transaction manager, and maps.

<sqlMapConfig>
  <transactionManager type="JDBC">
    <dataSource type="SIMPLE">
      <property name="JDBC.Driver" value="com.mysql.jdbc.Driver"/>
      <property name="JDBC.ConnectionURL" value="jdbc:mysql://localhost/blog"/>
      <property name="JDBC.Username" value="root"/>
      <property name="JDBC.Password" value="secret"/>
    </dataSource>
  </transactionManager>

  <sqlMap resource="com/example/UserMapper.xml"/>
</sqlMapConfig>

2. SQL Map Files

This is where the magic happens. You define your SQL and map results to your domain objects.

<sqlMap namespace="User">

  <resultMap id="userResult" class="com.example.User">
    <result property="id"    column="user_id"/>
    <result property="name"  column="user_name"/>
    <result property="email" column="user_email"/>
  </resultMap>

  <select id="getUserById" parameterClass="int" resultMap="userResult">
    SELECT user_id, user_name, user_email
    FROM users
    WHERE user_id = #value#
  </select>

  <insert id="insertUser" parameterClass="com.example.User">
    INSERT INTO users (user_name, user_email)
    VALUES (#name#, #email#)
  </insert>

</sqlMap>

Notice the #value# and #name# placeholders. iBatis substitutes these with proper PreparedStatement parameters, protecting you from SQL injection.

Using iBatis in Code

Once your maps are defined, executing statements is straightforward:

Reader reader = Resources.getResourceAsReader("SqlMapConfig.xml");
SqlMapClient sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);

// Retrieve a single record
User user = (User) sqlMap.queryForObject("getUserById", 42);

// Insert a new record
User newUser = new User("Alice", "alice@example.com");
sqlMap.insert("insertUser", newUser);

// Retrieve a list
List<User> users = sqlMap.queryForList("getAllUsers");

Dynamic SQL

One of iBatis's standout features is dynamic SQL, which lets you build queries conditionally without messy string concatenation.

<select id="searchUsers" parameterClass="map" resultMap="userResult">
  SELECT user_id, user_name, user_email
  FROM users
  <dynamic prepend="WHERE">
    <isNotNull prepend="AND" property="name">
      user_name LIKE #name#
    </isNotNull>
    <isNotNull prepend="AND" property="email">
      user_email = #email#
    </isNotNull>
  </dynamic>
</select>

This generates only the WHERE clauses relevant to the parameters you provide—an elegant solution for search screens with optional filters.

Handling Relationships

iBatis supports mapping related objects through nested result maps:

<resultMap id="userWithPosts" class="com.example.User">
  <result property="id"   column="user_id"/>
  <result property="name" column="user_name"/>
  <result property="posts" column="user_id"
          select="getPostsByUser"/>
</resultMap>

Be mindful of the N+1 query problem when using nested selects—each parent row can trigger an additional query.

iBatis vs. Hibernate

Best Practices

1. Organize maps by domain entity to keep XML manageable.
2. Use namespaces to avoid statement ID collisions.
3. **Leverage `