When writing SqlMaps, you often encounter duplicate fragments of SQL, for example a FROM-clause or constraint-statement. iBATIS offers a simple yet powerful tag to reuse them. For the sake of simplicity, let's assume we want to get some items and we want to do a count on them. Normally, you would write something like this :

<select id="SelectItemCount" resultClass="int">
SELECT COUNT(*) AS total
FROM items
WHERE parentid = 6
</select>

<select id="SelectItems" resultClass="Item">
SELECT id, name
FROM items
WHERE parentid = 6
</select>

To eliminate this duplication, we use the tags <sql> and <include>. The <sql> tag contains the fragment to reuse, the <include> tag includes such a fragment in a statement. For example:

<sql id="selectItem_fragment">
FROM items
WHERE parentid = 6
</sql>

<select id="selectItemCount" resultClass="int">
SELECT COUNT(*) AS total
<include refid="selectItem_fragment"/>
</select>

<select id="selectItems" resultClass="Item">
SELECT id, name
<include refid="selectItem_fragment"/>
</select>

The <include> tag is namespace aware so you can refer to fragments even when they are located in another map (however, due to the way iBATIS loads the SqlMaps, the included fragment should be loaded before the including statement). The fragments are included and processed on query-execution so parameters can be used too :

<sql id="selectItem_fragment">
FROM items
WHERE parentid = #value#
</sql>

<select id="selectItemCount" parameterClass="int" resultClass="int">
SELECT COUNT(*) AS total
<include refid="selectItem_fragment"/>
</select>

<select id="selectItems" parameterClass="int" resultClass="Item">
SELECT id, name
<include refid="selectItem_fragment"/>
</select>

	Note
	In many case, you can also use the extends attribute on statement tag to achieve the same goal.

Because you are combining SQL and XML in a single document, conflicts can occur. The most common conflict is the greater-than and less-than symbols (><). SQL statements use these symbols as operators, but they are reserved symbols in XML. A simple solution is to escape the SQL statements that uses XML reserved symbols within a CDATA element. Example 3.6 demonstrates this.

<statement id="SelectPersonsByAge" parameterClass="int" resultClass="Person">
  <![CDATA[ 
     SELECT * FROM PERSON WHERE AGE > #value# 
  ]]>
</statement>

Many database systems support auto-generation of primary key fields, as a vendor extension. Some vendors pre-generate keys (e.g. Oracle), some vendors post-generate keys (e.g. MS-SQL Server and MySQL). In either case, you can obtain a pre-generated key using a <selectKey> stanza within an <insert> element. Example 3.7 shows an <insert> statement for either approach.

<!—Oracle SEQUENCE Example using .NET 1.1 System.Data.OracleClient --> 
<insert id="insertProduct-ORACLE" parameterClass="product"> 
  <selectKey resultClass="int" type="pre" property="Id" > 
     SELECT STOCKIDSEQUENCE.NEXTVAL AS VALUE FROM DUAL
  </selectKey> 
  insert into PRODUCT (PRD_ID,PRD_DESCRIPTION) values (#id#,#description#) 
</insert>

<!— Microsoft SQL Server IDENTITY Column Example --> 
<insert id="insertProduct-MS-SQL" parameterClass="product"> 
  insert into PRODUCT (PRD_DESCRIPTION)
  values (#description#) 
 <selectKey resultClass="int" type="post" property="id" > 
   select @@IDENTITY as value
 </selectKey>
</insert>

<!-- MySQL Example -->
<insert id="insertProduct-MYSQL" parameterClass="product"> 
  insert into PRODUCT (PRD_DESCRIPTION)
  values (#description#) 
 <selectKey resultClass="int" type="post" property="id" > 
   select LAST_INSERT_ID() as value
 </selectKey>
</insert>

You can use iBATIS to execute any SQL statement your application requires. When the requirements for a statement are simple and obvious, you may not even need to write a SQL statement at all. The <generate> tag can be used to create simple SQL statements automatically, based on a <parameterMap> element. The four CRUD statement types (insert, select, update, and delete) are supported. For a select, you can select all or select by a key (or keys). Example 3.8 shows an example of generating the usual array of CRUD statements.

<parameterMaps>
  <parameterMap id="insert-generate-params">
    <parameter property="Name" column="Category_Name"/>
    <parameter property="Guid" column="Category_Guid" dbType="UniqueIdentifier"/>    
  </parameterMap>

  <parameterMap id="update-generate-params" extends="insert-generate-params">
    <parameter property="Id" column="Category_Id" />
  </parameterMap>

  <parameterMap id="delete-generate-params">
    <parameter property="Id" column="Category_Id" />
    <parameter property="Name" column="Category_Name"/>
  </parameterMap>

  <parameterMap id="select-generate-params">
    <parameter property="Id" column="Category_Id" />
    <parameter property="Name" column="Category_Name"/>
    <parameter property="Guid" column="Category_Guid" dbType="UniqueIdentifier"/>
  </parameterMap>

</parameterMaps>

<statements>

  <update id="UpdateCategoryGenerate" parameterMap="update-generate-params">
    <generate table="Categories" by="Category_Id"/>
  </update>

  <delete id="DeleteCategoryGenerate" parameterMap="delete-generate-params">
    <generate table="Categories" by="Category_Id, Category_Name"/>
  </delete>

  <select id="SelectByPKCategoryGenerate" resultClass="Category" parameterClass="Category" 
          parameterMap="select-generate-params">
    <generate table="Categories" by="Category_Id"/> 
  </select>

  <select id="SelectAllCategoryGenerate" resultClass="Category" 
          parameterMap="select-generate-params">
    <generate table="Categories" /> 
  </select>

  <insert id="InsertCategoryGenerate" parameterMap="insert-generate-params">
    <selectKey property="Id" type="post" resultClass="int">
      select @@IDENTITY as value
    </selectKey>
    <generate table="Categories" />
  </insert>

</statements>

	Note
	The SQL is generated when the DataMapper instance is built, so there is no performance impact at execution time.

The tag generates ANSI SQL, which should work with any compliant database. Special types, such as blobs, are not supported, and vendor-specific types are also not supported. But, the generate tag does keep the simple things simple.

Important

The intended use of the <generate> tag is to save developers the trouble of coding mundane SQL statements (and only mundane statements). It is not meant as a object-to-relational mapping tool. There are many frameworks that provide extensive object-to-relational mapping features. The <generate> tag is not a replacement for any of those. When the <generate> tag does not suit your needs, use a conventional statement instead.

The required id attribute provides a name for this statement, which must be unique within this <SqlMap>.

A Parameter Map defines an ordered list of values that match up with the "?" placeholders of a standard, parameterized query statement. Example 3.9 shows a <parameterMap> and a corresponding <statement>.

<parameterMap id="insert-product-param" class="Product">
  <parameter property="id"/>
  <parameter property="description"/>
</parameterMap>

<statement id="insertProduct" parameterMap="insert-product-param">
  insert into PRODUCT (PRD_ID, PRD_DESCRIPTION) values (?,?);
</statement>

In Example 3.9, the Parameter Map describes two parameters that will match, in order, two placeholders in the SQL statement. The first "?" is replaced by the value of the id property. The second is replaced with the description property.

iBATIS also supports named, inline parameters, which most developers seem to prefer. However, Parameter Maps are useful when the SQL must be kept in a standard form or when extra information needs to be provided. For more about Parameter Maps see Section 3.4.

If a parameterMap attribute is not specified, you may specify a parameterClass instead and use inline parameters (see Section 3.4.3 ). The value of the parameterClass attribute can be a Type Alias or the fully qualified name of a class. Example 3.10 shows a statement using a fully-qualified name versus an alias.

<!-- fully qualified classname -->
<statement id="statementName" parameterClass="Examples.Domain.Product, Examples.Domain">
  insert into PRODUCT values (#id#, #description#, #price#)
</statement>

<!-- typeAlias (defined elsewhere) -->
<statement id="statementName" parameterClass="Product">
  insert into PRODUCT values (#id#, #description#, #price#)
</statement>

A Result Map lets you control how data is extracted from the result of a query, and how the columns are mapped to object properties. Example 3.11 shows a <resultMap> element and a corresponding <statement> element.

Example 3.13. A <resultMap> and corresponding <statement>

<resultMap id="select-product-result" class="product">
  <result property="id" column="PRD_ID"/>
  <result property="description" column="PRD_DESCRIPTION"/>
</resultMap>

<statement id="selectProduct" resultMap="select-product-result">
  select * from PRODUCT
</statement>

In Example 3.11, the result of the SQL query will be mapped to an instance of the Product class using the "select-product-result" <resultMap>. The <resultMap> says to populate the id property from the PRD_ID column, and to populate the description property from the PRD_DESCRIPTION column.

	Tip
	In Example 3.11, note that using " select * " is supported. If you want all the columns, you don't need to map them all individually. (Though many developers consider it a good practice to always specify the columns expected.)

For more about Result Maps, see Section 3.5.

If a resultMap is not specified, you may specify a resultClass instead. The value of the resultClass attribute can be a Type Alias or the fully qualified name of a class. The class specified will be automatically mapped to the columns in the result, based on the result metadata. The following example shows a <statement> element with a resultClass attribute.

<statement id="SelectPerson" parameterClass="int" resultClass="Person">
  SELECT
  PER_ID as Id,
  PER_FIRST_NAME as FirstName,
  PER_LAST_NAME as LastName,
  PER_BIRTH_DATE as BirthDate,
  PER_WEIGHT_KG as WeightInKilograms,
  PER_HEIGHT_M as HeightInMeters
  FROM PERSON
  WHERE PER_ID = #value#
</statement>

In Example 3.12, the Person class has properties including: Id, FirstName, LastName, BirthDate, WeightInKilograms, and HeightInMeters. Each of these corresponds with the column aliases described by the SQL select statement using the "as" keyword –a standard SQL feature. When executed, a Person object is instantiated and populated by matching the object property names to the (aliased) column names from the query.

Using SQL aliases to map columns to properties saves defining a <resultMap> element, but there are limitations. There is no way to specify the types of the output columns (if needed), there is no way to automatically load related data such as complex properties, and there is a slight performance consequence from accessing the result metadata. Architecturally, using aliases this way mixes database logic with reporting logic, making the query harder to read and maintain. You can overcome these limitations with an explicit Result Map (Section 3.5).

In addition to providing the ability to return an IList of objects, the DataMapper supports the use of a strongly-typed custom collection: a class that implements the System.Collections.CollectionBase abstract class. The following is an example of a CollectionBase class that can be used with the DataMapper.

using System;
using System.Collections;

namespace WebShop.Domain 
{
 public class AccountCollection : CollectionBase 
 {
  public AccountCollection() {}

  public Account this[int index] 
  {
   get { return (Account)List[index]; }
   set { List[index] = value; }
  }

  public int Add(Account value) 
  {
   return List.Add(value);
  }

  public void AddRange(Account[] value) 
  {
   for (int i = 0; i < value.Length; i++) 
   {
    Add(value[i]);
   }
  }

  public void AddRange(AccountCollection value) 
  {
   for (int i = 0; i < value.Count; i++) 
   {
    Add(value[i]);
   }
  }

  public bool Contains(Account value) 
  {
   return List.Contains(value);
  }

  public void CopyTo(Account[] array, int index) 
  {
   List.CopyTo(array, index);
  }

  public int IndexOf(Account value) 
  {
   return List.IndexOf(value);
  }
  
  public void Insert(int index, Account value) 
  {
   Account.Insert(index, value);
  }
  
  public void Remove(Account value) 
  {
   Account.Remove(value);
  }
 }
}

A CollectionBase class can be specified for a select statement through the listClass attribute. The value of the listClass attribute can be a Type Alias or the fully qualified name of a class. The statement should also indicate the resultClass so that the DataMapper knows how to handle the type of objects in the collection. The resultClass specified will be automatically mapped to the columns in the result, based on the result metadata. The following example shows a <statement> element with a listClass attribute.

<statement id="GetAllAccounts"
 listClass="AccountCollection"
 resultClass="Account">
   select
   Account_ID as Id,
   Account_FirstName as FirstName,
   Account_LastName as LastName,
   Account_Email as EmailAddress
   from Accounts
   order by Account_LastName, Account_FirstName
</statement>

If you want to cache the result of a query, you can specify a Cache Model as part of the <statement> element. Example 3.15 shows a <cacheModel> element and a corresponding <statement>.

<cacheModel id="product-cache" implementation="LRU">
  <flushInterval hours="24"/>
  <flushOnExecute statement="insertProduct"/>
  <flushOnExecute statement="updateProduct"/>
  <flushOnExecute statement="deleteProduct"/>
  <property name="size" value="1000" />
</cacheModel>

<statement id="selectProductList" parameterClass="int" cacheModel="product-cache">
  select * from PRODUCT where PRD_CAT_ID = #value#
</statement>

When writing Sql, you often encounter duplicate fragments of SQL. iBATIS offers a simple yet powerful attribute to reuse them.

<select id="GetAllAccounts"
  resultMap="indexed-account-result">
select
  Account_ID,
  Account_FirstName,
  Account_LastName,
  Account_Email
from Accounts
</select>
		
<select id="GetAllAccountsOrderByName"
  extends="GetAllAccounts"
  resultMap="indexed-account-result">
    order by Account_FirstName
</select>