3.5. Result Maps

Section 3.4 describes Parameter Maps and Inline parameters, which map object properties to parameters in a database query. Result Maps finish the job by mapping the result of a database query (a set of columns) to object properties. Next to Mapped Statements, the Result Map is probably one of the most commonly used and most important features to understand.

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. A Result Map can describe the column type, a null value replacement, and complex property mappings including Collections. Example 3.24 shows the structure of a <resultMap> element.

Example 3.26. The structure of a <resultMap> element.

<resultMap id="resultMapIdentifier" 
           [class="fullyQualifiedClassName, assembly|typeAlias"] 
           [extends="[sqlMapNamespace.]resultMapId"]>

   <constructor > 
       <argument property="argumentName" 
           column="columnName"
           [columnIndex="columnIndex"] 
           [dbType="databaseType"] 
           [type="propertyCLRType"]
           [resultMapping="resultMapName"]
           [nullValue="nullValueReplacement"] 
           [select="someOtherStatementName"] 
           [typeHandler="fullyQualifiedClassName, assembly|typeAlias"] />
   </constructor > 

   <result property="propertyName" 
           column="columnName"
           [columnIndex="columnIndex"] 
           [dbType="databaseType"] 
           [type="propertyCLRType"]
           [resultMapping="resultMapName"]
           [nullValue="nullValueReplacement"] 
           [select="someOtherStatementName"] 
           [lazyLoad="true|false"]
           [typeHandler="fullyQualifiedClassName, assembly|typeAlias"]
   />
   <result ... .../>
   <result ... .../>
    // Inheritance support
   <discriminator column="columnName" 
                     [type|typeHandler="fullyQualifiedClassName, assembly|typeAlias"]
   />
    <subMap value="discriminatorValue" 
               resultMapping="resultMapName"
   />
   <subMap .../> 
</resultMap>


In Example 3.24, the [brackets] indicate optional attributes. The id attribute is required and provides a name for the statement to reference. The class attribute is also required, and specifies a Type Alias or the fully qualified name of a class. This is the class that will be instantiated and populated based on the result mappings it contains.

The resultMap can contain any number of property mappings that map object properties to the columns of a result element. The property mappings are applied, and the columns are read, in the order that they are defined. Mainting the element order ensures consistent results between different drivers and providers.

[Note]Note

As with parameter classes, the result class must be a .NET object or IDictionary instance.

3.5.1. Extending resultMaps

The optional extends attribute can be set to the name of another resultMap upon which to base this resultMap. All properties of the "super" resultMap will be included as part of this resultMap, and values from the "super" resultMap are set before any values specified by this resultMap. The effect is similar to extending a class.

[Tip]Tip

The "super" resultMap must be defined in the file before the extending resultMap. The classes for the super and sub resultMaps need not be the same, and do not need to be related in any way.

3.5.2. <resultMap> attributes

The <resultMap> element accepts three attributes: id (required), class (optional), and extends (optional).

3.5.2.1. id

The required id attribute provides a unique identifier for the <resultMap> within this Data Map.

3.5.2.2. class

The optional class attribute specifies an object class to use with this <resultMap>. The full classname or an alias must be specified. Any class can be used.

[Note]Note

As with parameter classes, the result classes must be a .NET object or IDictionary instance.

3.5.2.3. extends

The optional extends attribute allows the result map to inherit all of the properties of the "super" resultMap that it extends.

3.5.2.4. groupBy

The optional groupBy attribute specifies a list of .NET property names of the result object build by the resultMap. They are used to identify unique rows in the returned result set. Rows with equal values for the specified properties will only generate one result object. Use groupBy in combination with nested resultMaps to solve the N+1 query problem. Exemple : "Id" or "Desciption, Date".(see paragraph 3.5.13).

3.5.3. <constructor> element

The <constructor> element must match the signature of one of the result class constructor. If specify, this element is used by iBATIS to instanciate the result object.

The <constructor> element holds one or more <argument> child elements that map SQL resultsets to object argument constructor.

Example 3.27. Constructor element example

<resultMap id="account-result-constructor" class="Account" >
	 <constructor>
		<argument argumentName="id" column="Account_ID"/>
		<argument argumentName="firstName" column="Account_FirstName"/>
		<argument argumentName="lastName" column="Account_LastName"/>
	</constructor>
	<result property="EmailAddress" column="Account_Email" nullValue="no_email@provided.com"/>
	<result property="BannerOption" column="Account_Banner_Option" dbType="Varchar" type="bool"/>
	<result property="CartOption"	column="Account_Cart_Option" typeHandler="HundredsBool"/>
</resultMap>


3.5.3.1. argumentName

The argumentName attribute is the name of a constructor argument of the result object that will be returned by the Mapped Statement.

3.5.3.2. column

The column attribute value is the name of the column in the result set from which the value will be used to populate the argument.

3.5.3.3. columnIndex

As an optional (minimal) performance enhancement, the columnIndex attribute value is the index of the column in the ResultSet from which the value will be used to populate the object argument. This is not likely needed in 99% of applications and sacrifices maintainability and readability for speed. Some providers may not realize any performance benefit, while others will speed up dramatically.

3.5.3.4. dbType

The dbType attribute is used to explicitly specify the database column type of the ResultSet column that will be used to populate the argument. Although Result Maps do not have the same difficulties with null values, specifying the type can be useful for certain mapping types such as Date properties. Because an application language has one Date value type and SQL databases may have many (usually at least 3), specifying the date may become necessary in some cases to ensure that dates (or other types) are set correctly. Similarly, String types may be populated by a VarChar, Char or CLOB, so specifying the type might be needed in those cases too.

3.5.3.5. type

The type attribute is used to explicitly specify the CLR argument type. Normally this can be derived from a argument through reflection, but certain mappings that use objects such as a Map cannot provide the type to the framework. If the attribute type is not set and the framework cannot otherwise determine the type, the type is assumed to be Object. Section 6 details the CLR types and available aliases that are supported by the framework.

3.5.3.6. resultMapping

The resultMapping attribute can be set to the name of another resultMap used to fill the argument. If the resultMap is in an other mapping file, you must specified the fully qualified name.

3.5.3.7. nullValue

The nullValue attribute can be set to any valid value (based on argument type). The result element's nullValue attribute is used to specify an inbound null value replacement. What this means is that when the value is detected in a query's result column, the corresponding object argument will be set to the the nullValue attribute's value. This allows you to use a "magic" null number in your application for types that do not support null values (such as int, double, float).

3.5.3.8. select

The select attribute is used to describe a relationship between objects and to automatically load complex (i.e. user defined) property types. The value of the statement property must be the name of another mapped statement. The value of the database column (the column attribute) that is defined in the same property element as this statement attribute will be passed to the related mapped statement as the parameter. More information about supported primitive types and complex property mappings/relationships is discussed later in this document. The lazyLoad attribute can be specified with the select

3.5.3.9. typeHandler

The typeHandler attribute allows the use of a Custom Type Handler (see the Custom Type Handler section). This allows you to extend the DataMapper's capabilities in handling types that are specific to your database provider, are not handled by your database provider, or just happen to be a part of your application design. You can create custom type handlers to deal with storing and retrieving booleans and Guids from your database for example.

3.5.4. <result> Elements

The <resultMap> element holds one or more <result> child elements that map SQL resultsets to object properties.

3.5.4.1. property

The property attribute is the name of a field or a property of the result object that will be returned by the Mapped Statement. The name can be used more than once depending on the number of times it is needed to populate the results.

3.5.4.2. column

The column attribute value is the name of the column in the result set from which the value will be used to populate the property.

3.5.4.3. columnIndex

As an optional (minimal) performance enhancement, the columnIndex attribute value is the index of the column in the ResultSet from which the value will be used to populate the object property. This is not likely needed in 99% of applications and sacrifices maintainability and readability for speed. Some providers may not realize any performance benefit, while others will speed up dramatically.

3.5.4.4. dbType

The dbType attribute is used to explicitly specify the database column type of the ResultSet column that will be used to populate the object property. Although Result Maps do not have the same difficulties with null values, specifying the type can be useful for certain mapping types such as Date properties. Because an application language has one Date value type and SQL databases may have many (usually at least 3), specifying the date may become necessary in some cases to ensure that dates (or other types) are set correctly. Similarly, String types may be populated by a VarChar, Char or CLOB, so specifying the type might be needed in those cases too.

3.5.4.5. type

The type attribute is used to explicitly specify the CLR property type of the parameter to be set. Normally this can be derived from a property through reflection, but certain mappings that use objects such as a Map cannot provide the type to the framework. If the attribute type is not set and the framework cannot otherwise determine the type, the type is assumed to be Object. Section 6 details the CLR types and available aliases that are supported by the framework.

3.5.4.6. resultMapping

The resultMapping attribute can be set to the name of another resultMap used to fill the property. If the resultMap is in an other mapping file, you must specified the fully qualified name as :

resultMapping="[namespace.sqlMap.]resultMappingId"

resultMapping="Newspaper"
<!--resultMapping with a fully qualified name.-->
resultMapping="LineItem.LineItem"

3.5.4.7. nullValue

The nullValue attribute can be set to any valid value (based on property type). The result element's nullValue attribute is used to specify an inbound null value replacement. What this means is that when the value is detected in a query's result column, the corresponding object property will be set to the the nullValue attribute's value. This allows you to use a "magic" null number in your application for types that do not support null values (such as int, double, float).

If your database has a NULLABLE column, but you want your application to represent NULL with a constant value, you can specify it in the Result Map as shown in Example 3.25.

Example 3.28. Specifying a nullvalue attribute in a Result Map

<resultMap id="get-product-result" class="product"> 
  <result property="id" column="PRD_ID"/>
  <result property="description" column="PRD_DESCRIPTION"/>
  <result property="subCode" column="PRD_SUB_CODE" nullValue="-9999"/>
</resultMap>

In Example 3.25, if PRD_SUB_CODE is read as NULL, then the subCode property will be set to the value of -9999. This allows you to use a primitive type in your .NET class to represent a NULLABLE column in the database. Remember that if you want this to work for queries as well as updates/inserts, you must also specify the nullValue in the Parameter Map (discussed earlier in this document).

3.5.4.8. select

The select attribute is used to describe a relationship between objects and to automatically load complex (i.e. user defined) property types. The value of the statement property must be the name of another mapped statement. The value of the database column (the column attribute) that is defined in the same property element as this statement attribute will be passed to the related mapped statement as the parameter. More information about supported primitive types and complex property mappings/relationships is discussed later in this document. The lazyLoad attribute can be specified with the select

3.5.4.9. lazyLoad

Use the lazyLoad attribute with the select attribute to indicate whether or not the select statement's results should be lazy loaded. This can provide a performance boost by delaying the loading of the select statement's results until they are needed/accessed.

Lazy loading is supported transparently for IList and IList<T> implementation.

Lazy loading is supported on strongly typed collection via Castle.DynamicProxy component. In this case you must set the listClass attribute and declare all methods/properties of the typed collection that you want to proxy as virtual.

Lazy loading is supported on concrete class via Castle.DynamicProxy component. In this case, you must declare all methods/properties of the class that you want to proxy as virtual.

Example 3.29. Sample of strongly typed collection used with proxy call

[C#]

[Serializable]
public class LineItemCollection : CollectionBase 
{
	public LineItemCollection() {}

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

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

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

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

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

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

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

	public new virtual int Count
	{
		get {return this.List.Count;}
	}
}


Example 3.30. Concrete class

[C#]

[Serializable]
public class Person
{
...
	public virtual string Name
	{
		get {return _name;}
	}
...
}


3.5.4.10. typeHandler

The typeHandler attribute allows the use of a Custom Type Handler (see the Custom Type Handler section). This allows you to extend the DataMapper's capabilities in handling types that are specific to your database provider, are not handled by your database provider, or just happen to be a part of your application design. You can create custom type handlers to deal with storing and retrieving booleans and Guids from your database for example.

3.5.5. Custom Type Handlers

A custom type handler allows you to extend the DataMapper's capabilities in handling types that are specific to your database provider, not handled by your database provider, or just happen to be part of your application design. The .NET DataMapper provides an interface, IBatisNet.DataMappers.TypeHandlers.ITypeHandlerCallback, for you to use in implementing your custom type handler.

Example 3.31. ITypeHandlerCallback interface

using System.Data;
using IBatisNet.DataMapper.Configuration.ParameterMapping;


namespace IBatisNet.DataMapper.TypeHandlers
{
 public interface ITypeHandlerCallback
 {
  void SetParameter(IParameterSetter setter, object parameter);

  object GetResult(IResultGetter getter);

  object ValueOf(string s);
 }
}


The SetParameter method allows you to process a <statement> parameter's value before it is added as an IDbCommand parameter. This enables you to do any necessary type conversion and clean-up before the DataMapper gets to work. If needed, you also have access to the underlying IDataParameter through the setter.DataParameter property.

The GetResult method allows you to process a database result value right after it has been retrieved by the DataMapper and before it is used in your resultClass, resultMap, or listClass. If needed, you also have access to the underlying IDataReader through the getter.DataReader property.

The ValueOf method allows you to compare a string representation of a value with one that you are expecting and can handle appropriately. Typically, this is useful for translating a null value, but if your application or database will not support a null value, you can basically return the given string. When presented with an unexpected value, you can throw an appropriate exception.

One scenario that is familiar to .NET developers is the handling of a Guid type/structure. Many providers do not handle Guid class properties well, and developers may be faced with littering their domain objects with an additional set of property accessors to translate Guid properties into strings and strings into Guids.

public class BudgetObjectCode
{
 private string _code;
 private string _description;
 private Guid _guidProperty;

...

 public Guid GuidProperty {
  get { return _guidProperty; }
  set { _guidProperty = value; }
 }

 public string GuidPropertyString {
  get { return _guidProperty.ToString(); }
  set { 
  if (value == null) {
    _guidProperty = Guid.Empty;
   }
   else {
    _guidProperty = new Guid(value.ToString());
   }
  }
 }
...
}

We can use a custom type handler to clean up this domain class. First, we define a string that will represent a null Guid value (Guid.Empty). We can then use that constant in our ValueOf null value comparison for the DataMapper to eventually use in setting our domain class' Guid properties. Implementing the GetResult and SetParameter methods is straightforward since we had been basically doing the same translation in our domain class' GuidPropertyString accessors.

Example 3.32. Guid String Type Handler

using System;
using IBatisNet.DataMapper.TypeHandlers;

namespace BigApp.Common.TypeHandlers
{
 /// <summary>
 /// GuidVarcharTypeHandlerCallback.
 /// </summary>
 public class GuidVarcharTypeHandlerCallback : ITypeHandlerCallback
 {
  private const string GUIDNULL = "00000000-0000-0000-0000-000000000000";

  public object ValueOf(string nullValue)
  {
   if (GUIDNULL.Equals(nullValue)) 
   {
    return Guid.Empty;
   } 
   else 
   {
    throw new Exception(
     "Unexpected value " + nullValue + 
     " found where "+GUIDNULL+" was expected to represent a null value.");
   }  
  }

  public object GetResult(IResultGetter getter)
  {
   try {
    Guid result = new Guid(getter.Value.ToString());
    return result;
   } 
   catch
   {
     throw new Exception(
     "Unexpected value " + getter.Value.ToString() + 
     " found where a valid GUID string value was expected.");
   }
  }

  public void SetParameter(IParameterSetter setter, object parameter)
  {
   setter.Value = parameter.ToString();
  }

 }
}


With our custom type handler, we can clean up our domain class and use the handler in our SqlMaps. To do that, we have two options in configuring our custom type handler to be used by the DataMapper. We can simply add it as a <typeAlias> and use it when needed in a parameterMap or resultMap.

Example 3.33. Aliased Custom Type Handler in a SqlMap.xml file

<alias>
 <typeAlias alias="GuidVarchar" 
         type="BigApp.Common.TypeHandlers.GuidVarcharTypeHandlerCallback,
               BigApp.Common"/>
</alias>
 
<resultMaps>                                    
 <resultMap id="boc-result"  class="BudgetObjectCode">
  <result property="Code" column="BOC_CODE" dbType="Varchar2"/>
  <result property="Description" column="BOC_DESC" dbType="Varchar2"/>
  <result property="GuidProperty" column="BOC_GUID" typeHandler="GuidVarchar"/>
 </resultMap>
</resultMaps>


Or we can specify it as a basic <typeHandler> for all Guid types mapped in our SqlMap files. <typeHandler> in SqlMap.config

Example 3.34. <typeHandler> in SqlMap.config

[Our SqlMap.config]
<alias>
 <typeAlias alias="GuidVarchar" 
         type="BigApp.Common.TypeHandlers.GuidVarcharTypeHandlerCallback,
               BigApp.Common"/>
</alias>

<typeHandlers>
 <typeHandler type="guid" dbType="Varchar2" callback="GuidVarchar"/>
</typeHandlers> 


[One of our SqlMap.xml files]
<parameterMaps>
 <parameterMap id="boc-params">
  <parameter property="Code" dbType="Varchar2" size="10"/>
  <parameter property="Description" dbType="Varchar2" size="100"/>
  <parameter property="GuidProperty" dbType="Varchar2" type="guid"/>
 </parameterMap>
</parameterMaps>

<resultMaps>                                    
 <resultMap id="boc-result"  class="BudgetObjectCode">
  <result property="Code" column="BOC_CODE" dbType="Varchar2"/>
  <result property="Description" column="BOC_DESC" dbType="Varchar2"/>
  <result property="GuidProperty" column="BOC_GUID" dbType="Varchar2" type="guid"/>
 </resultMap>
</resultMaps>


3.5.6. Inheritance Mapping

The iBATIS DataMapper supports the implementation of object-oriented inheritance (subclassing) in your object model. There are several developer options for mapping entity classes and subclasses to database results:

  • resultMap for each class
  • resultMap with submaps for a class hierarchy
  • resultMap with extended resultMaps for each subclass

You can use the most efficient mapping strategies from a SQL and query performance perspective when using the inheritance mappings of the DataMapper. To implement an inheritance mapping, the resultMap must define one or more columns in your query's resultset that will serve to identify which resultMap should be used to map each result record to a specific subclass. In many cases, you will use one column value for the DataMapper to use in identifying the proper resultMap and subclass. This column is known as a discriminator.

For example, we have a table defined in a database that contains Document records. There are five table columns used to store Document IDs, Titles, Types, PageNumbers, and Cities. Perhaps this table belongs to a legacy database, and we need to create an application using this table with a domain model that defines a class hierarchy of different types of Documents. Or perhaps we are creating a new application and database and just want to persist the data found in a set of related classes into one table. In either case, the DataMapper's inheritance mapping feature can help.

// Database table Document
CREATE TABLE [Documents] (
    [Document_ID] [int] NOT NULL ,
    [Document_Title] [varchar] (32) NULL ,
    [Document_Type] [varchar] (32)  NULL ,
    [Document_PageNumber] [int] NULL  ,
    [Document_City] [varchar] (32)  NULL
)

To illustrate this, let's take a look at a few example classes shown below that have a relationship through inheritance and whose properties can be persisted into our Documents table. First, we have a base Document class that has Id and Title properties. Next, we have a Book class that inherits from Document and contains an additional property called PageNumber. Last, we have a Newspaper class that also inherits from Document and contains a City property.

Example 3.35. Documents, Books, and Newspapers!

// C# class
public class Document
{
  private int _id = -1;
  private string _title = string.Empty;

  public int Id
  {
    get { return _id; }
    set { _id = value; }
  }

  public string Title
  {
    get { return _title; }
    set { _title = value; }
  }
}

public class Book : Document
{
  private int _pageNumber = -1;

  public int PageNumber
  {
    get { return _pageNumber; }
    set { _pageNumber = value; }
  }
}

public class Newspaper : Document
{
  private string _city = string.Empty;

  public string City
  {
    get { return _city; }
    set { _city = value; }
  }
}


Now that we have our classes and database table, we can start working on our mappings. We can create one <select> statement that returns all columns in the table. To help the DataMapper discriminate between the different Document records, we're going to indicate that the Document_Type column holds values that will distinguish one record from another for mapping the results into our class hierarchy.

// Document mapping file
<select id="GetAllDocument" resultMap="document"> 
   select 
     Document_Id, Document_Title, Document_Type,
     Document_PageNumber, Document_City
   from Documents 
   order by Document_Type, Document_Id
</select>

<resultMap id="document" class="Document"> 
  <result property="Id" column="Document_ID"/>
  <result property="Title" column="Document_Title"/>
  <discriminator column="Document_Type" type="string"/>
  <subMap value="Book" resultMapping="book"/>
  <subMap value="Newspaper" resultMapping="newspaper"/>
</resultMap>

<resultMap id="book" class="Book" extends="document"> 
  <property="PageNumber" column="Document_PageNumber"/>
</resultMap>

<resultMap id="newspaper" class="Newspaper"  extends="document"> 
  <property="City" column="Document_City"/>
</resultMap>

The DataMapper compares the data found in the discriminator column to the different <submap> values using the column value's string equivalence. Based on this string value, iBATIS DataMapper will use the resultMap named "Book" or "Newspaper" as defined in the <submap> elements or it will use the "super" resultMap "Document" if neither of the submap values satisfy the comparison. With these resultMaps, we can implement an object-oriented inheritance mapping to our database table.

If you want to use custom logic, you can use the typeHandler attribute of the <discriminator> element to specify a custom type handler for the discriminator column.

Example 3.36. Complex disciminator usage with Custom Type Handler

<alias>
  <typeAlias alias="CustomInheritance" 
  type="IBatisNet.DataMapper.Test.Domain.CustomInheritance, IBatisNet.DataMapper.Test"/>
</alias>

<resultMaps>
  <resultMap id="document-custom-formula" class="Document">
    <result property="Id" column="Document_ID"/>
    <result property="Title" column="Document_Title"/>
    <discriminator column="Document_Type" typeHandler="CustomInheritance"/>
    <subMap value="Book" resultMapping="book"/>
    <subMap value="Newspaper" resultMapping="newspaper"/>
  </resultMap>
</resultMaps>

The value of the typeHandler attribute specifies which of our classes implements the ITypeHandlerCallback interface. This interface furnishes a GetResult method for coding custom logic to read the column result value and return a value for the DataMapper to use in its comparison to the resultMap's defined <submap> values.

Example 3.37. Example ITypeHandlerCallback interface implementation

public class CustomInheritance : ITypeHandlerCallback
{
  #region ITypeHandlerCallback members

  public object ValueOf(string nullValue)
  {
    throw new NotImplementedException();
  }

  public object GetResult(IResultGetter getter)
  {
   string type = getter.Value.ToString();

   if (type=="Monograph" || type=="Book")
   {
     return "Book";
   }
   else if (type=="Tabloid" || type=="Broadsheet" || type=="Newspaper")
   {
     return "Newspaper";
   }
   else
   {
     return "Document";
   }

  }

  public void SetParameter(IParameterSetter setter, object parameter)
  {
   throw new NotImplementedException(); 
  }
  #endregion
}

3.5.7. Implicit Result Maps

If the columns returned by a SQL statement match the result object, you may not need an explicit Result Map. If you have control over the relational schema, you might be able to name the columns so they also work as property names. In Example 3.33, the column names and property names already match, so a result map is not needed.

Example 3.38. A Mapped Statement that doesn't need a Result Map

<statement id="selectProduct" resultClass="Product">
  select
    id,
    description
  from PRODUCT
  where id = #value#
</statement>


Another way to skip a result map is to use column aliasing to make the column names match the properties names, as shown in Example 3.34.

Example 3.39. A Mapped Statement using column alaising instead of a Result Map

<statement id="selectProduct" resultClass="Product">
  select
  PRD_ID as id,
  PRD_DESCRIPTION as description
  from PRODUCT
  where PRD_ID = #value#
</statement>

Of course, these techniques will not work if you need to specify a column type, a null value, or any other property attributes.

Case sensitivity can also be an issue with implicit result maps. Conceivably, you could have an object with a "FirstName" property and a "Firstname" property. When iBATIS tries to match property and column, the heurstic is case-insensitive and we cannot guarantee which property would match. (Of course, very few developers would have two property names that were so simiilar.)

A final issue is that there is some performance overhead when iBATIS has to map the column and property names automatically. The difference can be dramatic if using a third-party NET database provider with poor support for ResultSetMetaData.

3.5.8. Primitive Results (i.e. String, Integer, Boolean)

Many times, we don't need to return an object with multiple properties. We just need a String, Integer, Boolean, and so forth. If you don't need to populate an object, iBATIS can return one of the primitive types instead. If you just need the value, you can use a standard type as a result class, as shown in Example 3.35.

Example 3.40. Selecting a standard type

<select id="selectProductCount" resultClass="System.Int32">
  select count(1)
  from PRODUCT
</select>


If need be, you can refer to the standard type using a marker token, "value", as shown by Example 3.36.

Example 3.41. Loading a simple list of product descriptions

<resultMap id="select-product-result" resultClass="System.String">
  <result property="value" column="PRD_DESCRIPTION"/>
</resultMap>

3.5.9. Maps with ResultMaps

Instead of a rich object, sometimes all you might need is a simple key/value list of the data, where each property is an entry on the list. If so, Result Maps can populate a IDictionary instance as easily as property objects. The syntax for using a IDictionary is identical to the rich object syntax. As shown in Example 3.37, only the result object changes.

Example 3.42. Result Maps can use generic "entry-type" objects

<resultMap id="select-product-result" class="HashTable">
  <result property="id" column="PRD_ID"/>
  <result property="code" column="PRD_CODE"/>
  <result property="description" column="PRD_DESCRIPTION"/>
  <result property="suggestedPrice" column="PRD_SUGGESTED_PRICE"/>
</resultMap>

In Example 3.37, an instance of HashTable would be created for each row in the result set and populated with the Product data. The property name attributes, like id, code, and so forth, would be the key of the entry, and the value of the mapped columns would be the value of the entry.

As shown in Example 3.38, you can also use an implicit Result Map with a IDictionary type.

Example 3.43. Implicit Result Maps can use "entry-type" objects too

<statement id="selectProductCount" resultClass="HashTable">
  select * from PRODUCT
</statement>

What set of entries is returned by Example xx depends on what columns are in the result set. If the set of column changes (because columns are added or removed), the new set of entries would automatically be returned.

[Note]Note

Certain providers may return column names in upper case or lower case format. When accessing values with such a provider, you will have to pass the Hashtable or HashMap key name in the expected case.

3.5.10. Complex Properties

In a relational database, one table will often refer to another. Likewise, some of your business objects may include another object or list of objects. Types that nest other types are called "complex types". You may not want a statement to return a simple type, but a fully-formed compex type.

In the database, a related column is usually represented via a 1:1 relationship, or a 1:M relationship where the class that holds the complex property is from the "many side" of the relationship and the property itself is from the "one side" of the relationship. The column returned from the database will not be the property we want; it is a key to be used in another query.

From the framework's perspective, the problem is not so much loading a complex type, but loading each "complex property". To solve this problem, you can specify in the Result Map a statement to run to load a given property. In Example 3.39, the "category" property of the "select-product-result" element is a complex property.

Example 3.44. A Result Map with a Complex Property

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

  <resultMap id="select-category-result" class="category">
    <result property="id" column="CAT_ID"/>
    <result property="description" column="CAT_DESCRIPTION"/>
  </resultMap>
</resultMaps>

<statements>
  <select id="selectProduct" parameterClass="int" resultMap="select-product-result">
   select * from PRODUCT where PRD_ID = #value#
  </select>

  <select id="selectCategory" parameterClass="int" resultMap="select-category-result">
   select * from CATEGORY where CAT_ID = #value#
  </select>
</statements>
      


In Example 3.39, the framework will use the "selectCategory" statement to populate the "category" property. The value of each category is passed to the "selectCategory" statement, and the object returned is set to the category property. When the process completes, each Product instance will have the the appropriate category object instance set.

3.5.11. Avoiding N+1 Selects (1:1)

A problem with Example 3.39 may be that whenever you load a Product, two statements execute: one for the Product and one for the Category. For a single Product, this issue may seem trivial. But if you load 10 products, then 11 statements execute. For 100 Products, instead of one statement product statement executing, a total of 101 statements execute. The number of statements executing for Example 3.40 will always be N+1: 100+1=101.

Example 3.45. N+1 Selects (1:1)

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

  <resultMap id="select-category-result" class="category">
    <result property="id" column="CAT_ID"/>
    <result property="description" column="CAT_DESCRIPTION"/>
  </resultMap>
</resultMaps>

<statements>
  <!-- This statement executes 1 time -->
  <select id="selectProducts" parameterClass="int" resultMap="select-product-result">
   select * from PRODUCT
  </select>

  <!-- This statement executes N times (once for each product returned above) -->
  <select id="selectCategory" parameterClass="int" resultMap="select-category-result">
   select * from CATEGORY where CAT_ID = #value#
  </select>
</statements>
      

One way to mitigate the problem is to cache the "selectCategory" statement . We might have a hundred products, but there might only be five categories. Instead of running a SQL query or stored procedure, the framework will return the category object from it cache. A 101 statements would still run, but they would not be hitting the database. (See Section 3.8 for more about caches.)

Another solution is to use a standard SQL join to return the columns you need from the another table. A join can bring all the columns we need over from the database in a single query. When you have a nested object, you can reference nested properties using a dotted notation, like "category.description".

Example 3.41 solves the same problem as Example 3.40, but uses a join instead of nested properties.

Example 3.46. Resolving complex properties with a join

<resultMaps>
  <resultMap id="select-product-result" class="product">
    <result property="id" column="PRD_ID"/>
    <result property="description" column="PRD_DESCRIPTION"/>
    <result property="category" resultMapping="Category.CategoryResult" />
  </resultMap>
</resultMaps>

<statements>
  <statement id="selectProduct" parameterClass="int" resultMap="select-product-result">
    select *
    from PRODUCT, CATEGORY
    where PRD_CAT_ID=CAT_ID
    and PRD_ID = #value#
  </statement>
</statements>
      

3.5.12. Complex Collection Properties

It is also possible to load properties that represent lists of complex objects. In the database the data would be represented by a M:M relationship, or a 1:M relationship where the class containing the list is on the "one side" of the relationship and the objects in the list are on the "many side". To load an IList of objects, there is no change to the statement (see example above). The only difference required to cause the iBATIS DataMapper framework to load the property as an IList is that the property on the business object must be of type System.Collections.IList. For example, if a Category has a IList of Product instances, the mapping would look like this (assuming Category has a property called "ProductList" of System.Collections.IList.):

Example 3.47. Mapping that creates a list of complex objects

<resultMaps>

  <resultMap id="select-category-result" class="Category">
    <result property="Id" column="CAT_ID"/>
    <result property="Description" column="CAT_DESCRIPTION"/>
    <result property="ProductList" column="CAT_ID" select="selectProductsByCatId"/>
  </resultMap>

  <resultMap id="select-product-result" class="Product">
    <result property="Id" column="PRD_ID"/>
    <result property="Description" column="PRD_DESCRIPTION"/>
  </resultMap>
<resultMaps>

<statements>

  <statement id="selectCategory" parameterClass="int" resultMap="select-category-result">
    select * from CATEGORY where CAT_ID = #value#
  </statement>

  <statement id="selectProductsByCatId" parameterClass="int" resultMap="select-product-result">
    select * from PRODUCT where PRD_CAT_ID = #value#
  </statement>
</statements>

3.5.13. Avoiding N+1 Select Lists (1:M and M:N)

This is similar to the 1:1 situation above, but is of even greater concern due to the potentially large amount of data involved. The problem with the solution above is that whenever you load a Category, two SQL statements are actually being run (one for the Category and one for the list of associated Products). This problem seems trivial when loading a single Category, but if you were to run a query that loaded ten (10) Categories, a separate query would be run for each Category to load its associated list of Products. This results in eleven (11) queries total: one for the list of Categories and one for each Category returned to load each related list of Products (N+1 or in this case 10+1=11). To make this situation worse, we're dealing with potentially large lists of data.

Example 3.48. N+1 Select Lists (1:M and M:N), example of problem

<resultMaps>

  <resultMap id="select-category-result" class="Category">
    <result property="Id" column="CAT_ID"/>
    <result property="Description" column="CAT_DESCRIPTION"/>
   <result property="ProductList" column="CAT_ID" select="selectProductsByCatId"/>
  </resultMap>

  <resultMap id="Product-result" class="Product">
    <result property="Id" column="PRD_ID"/>
    <result property="Description" column="PRD_DESCRIPTION"/>
  </resultMap>
<resultMaps>

<statements>

  <!-- This statement executes 1 time -->
  <statement id="selectCategory" parameterClass="int" resultMap="select-category-result">
    select * from CATEGORY where CAT_ID = #value#
  </statement>

  <!-- This statement executes N times (once for each category returned above) 
       and returns a list of Products (1:M) -->
  <statement id="selectProductsByCatId" parameterClass="int" resultMap="select-product-result">
    select * from PRODUCT where PRD_CAT_ID = #value#
  </statement>
</statements>

iBATIS fully solves the N+1 selects problem. Here is the same example solved :

Example 3.49. N+1 Select Lists (1:M and M:N) resolution

<sqlMap namespace="ProductCategory">
<resultMaps>

  <resultMap id="Category-result" class="Category" groupBy="Id">
    <result property="Id" column="CAT_ID"/>
    <result property="Description" column="CAT_DESCRIPTION"/>
    <result property="ProductList" resultMapping="ProductCategory.Product-result"/>
  </resultMap>

  <resultMap id="Product-result" class="Product">
    <result property="Id" column="PRD_ID"/>
    <result property="Description" column="PRD_DESCRIPTION"/>
  </resultMap>
<resultMaps>

<statements>

  <!-- This statement executes 1 time -->
  <statement id="SelectCategory" parameterClass="int" resultMap="Category-result">
    select C.CAT_ID, C.CAT_DESCRIPTION, P.PRD_ID, P.PRD_DESCRIPTION
    from CATEGORY C
    left outer join PRODUCT P
    on C.CAT_ID = P.PRD_CAT_ID
    where CAT_ID = #value#
  </statement>


When you call...

IList myList = sqlMap.QueryForList("SelectCategory", 1002);

...the main query is executed, and the results are stored in the myList variable containing .NET type "Category" element . Each object in that List will have a "ProductList" property that is also a List populated from the same query, but using the "Product-result" result map to populate the element in the child list. So, you end up with a list containing sub-lists, and only one database query is executed.

The important items here are the...

groupBy="Id"

...attribute and the...

<result property="ProductList" resultMapping="ProductCategory.Product-result"/>

...property mapping in the "Category-result" result map. One other important detail is that the result mapping for the ProductList property is namespace aware - had it been simply "Product-result" it would not work. Using this approach, you can solve any N+1 problem of any depth or breadth.

3.5.14. Composite Keys or Multiple Complex Parameters Properties

You might have noticed that in the above examples there is only a single key being used as specified in the resultMap by the column attribute. This would suggest that only a single column can be associated to a related mapped statement. However, there is an alternate syntax that allows multiple columns to be passed to the related mapped statement. This comes in handy for situations where a composite key relationship exists, or even if you simply want to use a parameter of some name other than #value#. The alternate syntax for the column attribute is simply {param1=column1, param2=column2, …, paramN=columnN}. Consider the example below where the PAYMENT table is keyed by both Customer ID and Order ID:

Example 3.50. Mapping a composite key

<resultMaps>
  <resultMap id="select-order-result" class="order">
    <result property="id" column="ORD_ID"/>
    <result property="customerId" column="ORD_CST_ID"/>
    ...
    <result property="payments" column="itemId=ORD_ID, custId=ORD_CST_ID"
      select="selectOrderPayments"/>
  </resultMap>
<resultMaps>

<statements>

  <statement id="selectOrderPayments" resultMap="select-payment-result">
    select * from PAYMENT
    where PAY_ORD_ID = #itemId#
    and PAY_CST_ID = #custId#
  </statement>
</statements>

Optionally you can just specify the column names as long as they're in the same order as the parameters. For example:

ORD_ID, ORD_CST_ID

As usual, this is a slight performance gain with an impact on readability and maintainability.

Important! Currently the iBATIS DataMapper framework does not automatically resolve circular relationships. Be aware of this when implementing parent/child relationships (trees). An easy workaround is to simply define a second result map for one of the cases that does not load the parent object (or vice versa), or use a join as described in the "N+1 avoidance" solutions.

[Note]Note

Result Map names are always local to the Data Map definition file that they are defined in. You can refer to a Result Map in another Data Map definition file by prefixing the name of the Result Map with the namespace of the SqlMap set in the <sqlMap> root element.