Importing SQLMap + sample + docs.

1 files changed, 941 insertions, 0 deletions

diff --git a/docs/sqlmap/latex/ch5.tex b/docs/sqlmap/latex/ch5.tex
new file mode 100644
index 00000000..076f6f5d
--- /dev/null
+++ b/docs/sqlmap/latex/ch5.tex
@@ -0,0 +1,941 @@
+\chapter{Result Maps}\label{section:3.5}

+Chapter~\ref{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~\ref{example:3.24} shows the structure

+of a \tt{<resultMap>} element.

+

+\begin{example}\label{example:3.24}

+The structure of a \tt{<resultMap>} element.

+\begin{verbatim}

+<resultMap id="resultMapIdentifier"

+           [class="class.name"]

+           [extends="[sqlMapNamespace.]resultMapId"]>

+

+   <result property="propertyName"

+           column="columnName"

+           [columnIndex="columnIndex"]

+           [dbType="databaseType"]

+           [type="propertyCLRType"]

+           [resultMapping="resultMapName"]

+           [nullValue="nullValueReplacement"]

+           [select="someOtherStatementName"]

+           [lazyLoad="true|false"]

+           [typeHandler="class.name"]

+   />

+   <result ... .../>

+   <result ... .../>

+</resultMap>

+\end{verbatim}

+\end{example}

+

+In Example~\ref{example:3.24}, the [brackets] indicate optional attributes.

+The \tt{id} attribute is required and provides a name for the statement to

+reference. The \tt{class} attribute is also required, and specifies the full

+name of a PHP class. This is the class that will be instantiated and populated

+based on the result mappings it contains.

+

+The \tt{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.

+Maintaining the element order ensures consistent results between different

+drivers and providers.

+

+\begin{mybox}{Note:}

+As with parameter classes, the result class must be a PHP class object or

+array instance.

+\end{mybox}

+

+\section{Extending \tt{resultMaps}}

+The optional \tt{extends} attribute can be set to the name of another

+\tt{resultMap} upon which to base this \tt{resultMap}. All properties of the

+``super'' \tt{resultMap} will be included as part of this \tt{resultMap}, and

+values from the ``super'' \tt{resultMap} are set before any values specified

+by this \tt{resultMap}. The effect is similar to extending a class.

+

+\begin{mybox}{Tip:}

+The ``super'' \tt{resultMap} must be defined in the file before the extending

+\tt{resultMap}. The classes for the super and sub \tt{resultMaps} need not be

+the same, and do not need to be related in any way.

+\end{mybox}

+

+\section{\tt{<resultMap>} attributes}

+The \tt{<resultMap>} element accepts three attributes: \tt{id} (required),

+\tt{class} (optional), and \tt{extends} (optional).

+

+\subsection{\tt{id} attribute}

+The required \tt{id} attribute provides a unique identifier for the

+\tt{<resultMap>} within this Data Map.

+

+\subsection{\tt{class} attribute}

+The optional \tt{class} attribute specifies an object class to use with this

+\tt{<resultMap>}. The full classname must be specified. Any class can be used.

+

+\begin{mybox}{Note:}

+As with parameter classes, the result class must be a PHP class object or

+array instance.

+\end{mybox}

+

+\subsection{\tt{extends} attribute}

+The optional \tt{extends} attribute allows the result map to inherit all of

+the properties of the ``super'' \tt{resultMap} that it extends.

+

+\section{\tt{<result>} Elements}

+

+The \tt{<resultMap>} element holds one or more \tt{<result>} child elements

+that map SQL result sets to object properties.

+

+\subsection{\tt{property} attribute}

+The \tt{property} attribute is the name of 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.

+

+\subsection{\tt{column} attribute}

+The \tt{column} attribute value is the name of the column in the result set

+from which the value will be used to populate the property.

+

+\subsection{\tt{columnIndex} attribute}

+The \tt{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.

+

+\subsection{\tt{dbType} attribute}

+The \tt{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 \tt{VarChar},

+\tt{Char} or \tt{CLOB}, so specifying the type might be needed in those cases

+too.

+

+\subsection{\tt{type} attribute}

+The \tt{type} attribute is used to explicitly specify the property type of the

+parameter to be set. If the attribute \tt{type} is not set and the framework

+cannot otherwise determine the type, the type is assumed to be \tt{StdObject}.

+

+\subsection{\tt{resultMapping} attribute}

+The \tt{resultMapping} attribute can be set to the name of another

+\tt{resultMap} used to fill the property. If the \tt{resultMap} is in an other

+mapping file, you must specified the fully qualified name as :

+

+\begin{verbatim}

+resultMapping="[namespace.sqlMap.]resultMappingId"

+

+resultMapping="Newspaper"

+<!--resultMapping with a fully qualified name.-->

+resultMapping="LineItem.LineItem"

+\end{verbatim}

+

+\subsection{\tt{nullValue} attribute}

+The \tt{nullValue} attribute can be set to any valid value (based on property

+type). The \tt{nullValue} attribute is used to specify an outgoing null value

+replacement. What this means is that when the value is detected in the object

+property, a NULL will be written to the database (the opposite behavior of an

+inbound null value replacement). 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). When these types of properties contain a matching null

+value (say, $-9999$), a NULL will be written to the database instead of the

+value.

+

+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~\ref{example:3.25}.

+

+\begin{example}\label{example:3.25}

+Specifying a \tt{nullvalue} attribute in a Result Map

+\begin{verbatim}

+<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>

+\end{verbatim}

+\end{example}

+

+In Example~\ref{example:3.25}, if PRD\_SUB\_CODE is read as NULL, then the

+\tt{subCode} property will be set to the value of $-9999$. This allows you to

+use a primitive type 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 \tt{nullValue} in the Parameter Map (see,

+Section~\ref{section:nullValueParameter}).

+

+\subsection{\tt{select} attribute}

+The \tt{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 \tt{lazyLoad} attribute can be specified with the

+\tt{select}.

+

+\subsection{\tt{lazyLoad} attribute}

+Use the \tt{lazyLoad} attribute with the \tt{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.

+

+\subsection{\tt{typeHandler} attribute}

+The \tt{typeHandler} attribute allows the use of a Custom Type Handler (see

+the Custom Type Handler in the following 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 from your database for example.

+

+\section{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 SQLMap for PHP DataMapper provides an interface,

+\tt{ITypeHandlerCallback}, for you to use in implementing your custom type

+handler.

+

+\begin{example}\label{example:3.26}

+\tt{ITypeHandlerCallback} interface

+\begin{verbatim}

+interface ITypeHandlerCallback

+{

+    public function getParameter($object);

+

+    public function getResult($string);

+

+    public function createNewInstance();

+}

+\end{verbatim}

+\end{example}

+

+The \tt{getParameter} method allows you to process a \tt{<statement>}

+parameter's value before it is added as an parameter. This enables you to do

+any necessary type conversion and clean-up before the DataMapper gets to work.

+

+The \tt{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

+\tt{resultClass}, \tt{resultMap}, or \tt{listClass}.

+

+The \tt{createNewInstance} method allows the DataMapper to create new instance

+of a particular type handled by this callback.

+

+One scenario where custom type handlers are useful are the when you want to

+use date time values in the database. First, consider a very basic TDateTime

+class.

+

+\begin{verbatim}

+class TDateTime

+{

+    private $_datetime;

+

+    public function __construct($datetime=null)

+    {

+        if(!is_null($datetime))

+            $this->setDatetime($datetime);

+    }

+

+    public function getTimestamp()

+    {

+        return strtotime($this->getDatetime());

+    }

+

+    public function getDateTime()

+    {

+        return $this->_datetime;

+    }

+

+    public function setDateTime($value)

+    {

+        $this->_datetime = $value;

+    }

+}

+\end{verbatim}

+

+We can use a custom type handler to intercept result and parameter mapping

+that uses the say ``data'' as one of its property type. The handler can be

+written as follows.

+

+\begin{example}\label{example:3.27}

+A \tt{TDateTime} Type Handler

+\begin{verbatim}

+class TDateTimeHandler implements ITypeHandlerCallback

+{

+    public function getResult($string)

+    {

+        return new TDateTime($string);

+    }

+

+    public function getParameter($parameter)

+    {

+        if($parameter instanceof TDateTime)

+            return $parameter->getTimestamp();

+        else

+            return $parameter;

+    }

+

+    public function createNewInstance()

+    {

+        return new TDateTime;

+    }

+}

+\end{verbatim}

+\end{example}

+

+With our custom type handler we can use the handler in our SqlMaps. To do

+that, we specify it as a basic \tt{<typeHandler>} for all \tt{date} types

+mapped in our SqlMap files

+

+\begin{example}\label{example:3.29}

+\tt{<typeHandler>} in SqlMap.config

+\begin{verbatim}

+[Our SqlMap.config]

+

+<typeHandlers>

+ <typeHandler type="date" callback="TDateTimeHandler"/>

+</typeHandlers>

+

+

+[One of our SqlMap.xml files]

+ <parameterMap id="boc-params">

+  <parameter property="releasedDate" type="date"/>

+ </parameterMap>

+

+ <resultMap id="boc-result"  class="BudgetObjectCode">

+  <result property="releasedDate" column="BOC_DATE" type="date"/>

+ </resultMap>

+\end{verbatim}

+\end{example}

+

+%3.5.5. 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.

+%

+%\begin{verbatim}

+%// 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

+%)

+%\end{verbatim}

+%

+%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.30. Documents, Books, and Newspapers!

+%

+%\begin{verbatim}

+%// 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; }

+%  }

+%}

+%\end{verbatim}

+%

+%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.

+%

+%\begin{verbatim}

+%// 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>

+%\end{verbatim}

+%

+%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.31. Complex disciminator usage with Custom Type Handler

+%

+%\begin{verbatim}

+%<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>

+%\end{verbatim}

+%

+%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.32. Example ITypeHandlerCallback interface implementation

+%

+%\begin{verbatim}

+%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

+%}

+%\end{verbatim}

+

+\section{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~\ref{example:3.33}, the column names and property names

+already match, so a result map is not needed.

+

+\begin{example}\label{example:3.33}

+A Mapped Statement that doesn't need a Result Map

+\begin{verbatim}

+<statement id="selectProduct" resultClass="Product">

+  select

+    id,

+    description

+  from PRODUCT

+  where id = #value#

+</statement>

+\end{verbatim}

+\end{example}

+

+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~\ref{example:3.34}.

+

+\begin{example}\label{example:3.34}

+A Mapped Statement using column aliasing instead of a Result Map

+\begin{verbatim}

+<statement id="selectProduct" resultClass="Product">

+  select

+  PRD_ID as id,

+  PRD_DESCRIPTION as description

+  from PRODUCT

+  where PRD_ID = #value#

+</statement>

+\end{verbatim}

+\end{example}

+

+Of course, these techniques will not work if you need to specify a column

+type, a null value, or any other property attributes.

+

+\section{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, SQLMap can return one of the primitive types instead. If

+you just need the value, you can use a primitive type as a result class, as

+shown in Example~\ref{example:3.35}.

+

+\begin{example}\label{example:3.35}

+Selecting a primitive type

+\begin{verbatim}

+<select id="selectProductCount" resultClass="integer">

+  select count(1)

+  from PRODUCT

+</select>

+\end{verbatim}

+\end{example}

+

+\begin{example}\label{example:3.36}

+Loading a simple list of product descriptions

+\begin{verbatim}

+<resultMap id="select-product-result" resultClass="System.String">

+  <result property="value" column="PRD_DESCRIPTION"/>

+</resultMap>

+\end{verbatim}

+\end{example}

+

+\section{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 an array instance as easily as property objects. The syntax

+for using an array is identical to the rich object syntax. As shown in Example

+~\ref{example:3.37}, only the result object changes.

+

+\begin{example}\label{example:3.37}

+Result Maps can use arrays.

+\begin{verbatim}

+<resultMap id="select-product-result" class="array">

+  <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>

+\end{verbatim}

+\end{example}

+

+In Example~\ref{example:3.37}, an array instance would be created for each row

+in the result set and populated with the Product data. The property name

+attributes, like \tt{id}, \tt{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~\ref{example:3.38}, you can also use an implicit Result

+Map with an array type.

+

+\begin{example}\label{example:3.38}

+Implicit Result Maps can use arrays too.

+\begin{verbatim}

+<statement id="selectProductCount" resultClass="array">

+  select * from PRODUCT

+</statement>

+\end{verbatim}

+\end{example}

+

+What set of entries is returned by Example~\ref{example:3.38} 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.

+

+\begin{mybox}{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 key name

+in the expected case.

+\end{mybox}

+

+\section{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 complex 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~\ref{example:3.39}, the ``category'' property of the

+``select-product-result'' element is a complex property.

+

+\begin{example}\label{example:3.39}

+A Result Map with a Complex Property

+\begin{verbatim}

+  <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>

+

+  <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>

+\end{verbatim}

+\end{example}

+

+In Example~\ref{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.

+

+\section{Avoiding N+1 Selects (1:1)}

+A problem with Example~\ref{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~\ref{example:3.40} will always be

+N+1: 100+1=101.

+

+\begin{example}\label{example:3.40}

+N+1 Selects (1:1)

+\begin{verbatim}

+  <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>

+

+  <!-- 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>

+

+\end{verbatim}

+\end{example}

+

+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 Chapter~\ref{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~\ref{example:3.41} solves the same problem as

+Example~\ref{example:3.40}, but uses a join instead of nested properties.

+

+\begin{example}\label{example:3.41}

+Resolving complex properties with a join

+\begin{verbatim}

+  <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>

+

+  <statement id="selectProduct" parameterClass="int" resultMap="select-product-result">

+    select *

+    from PRODUCT, CATEGORY

+    where PRD_CAT_ID=CAT_ID

+    and PRD_ID = #value#

+  </statement>

+\end{verbatim}

+\end{example}

+

+\begin{mybox}{Lazy Loading vs. Joins (1:1):}

+It's important to note that using a join is not always better. If you are in a

+situation where it is rare to access the related object (e.g. the category

+property of the Product class) then it might actually be faster to avoid the

+join and the unnecessary loading of all category properties. This is

+especially true for database designs that involve outer joins or nullable

+and/or non-indexed columns. In these situations it might be better to use the

+sub-select solution with lazy loading enabled. The general rule of thumb is:

+use the join if you're more likely going to access the associated properties

+than not. Otherwise, only use it if lazy loading is not an option.

+\\

+\\

+If you're having trouble deciding which way to go, don't worry. No matter

+which way you go, you can always change it without impacting your application

+source code. Example~\ref{example:3.40} and \ref{example:3.41} result in

+exactly the same object graph and are loaded using the exact same method call

+from the application. The only consideration is that if you were to enable

+caching, then the using the separate select (not the join) solution could

+result in a cached instance being returned. But more often than not, that

+won't cause a problem (your application shouldn't be dependent on instance

+level equality i.e. ``==='').

+\end{mybox}

+

+\section{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 a \tt{TList} of objects, there is no change to the statement

+(see example above). The only difference required to cause the SQLMap

+DataMapper framework to load the property as a \tt{TList} is that the property

+on the business object must be of type \tt{TList}. For example, if a Category

+has a \tt{TList} of Product instances, the mapping would look like this

+(assuming Category has a property called "ProductList" of \tt{TList}.):

+

+\begin{example}\label{example:3.42}

+Mapping that creates a list of complex objects

+\begin{verbatim}

+<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>

+\end{verbatim}

+\end{example}

+

+\section{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.

+

+\begin{example}\label{example:3.43}

+N+1 Select Lists (1:M and M:N)

+\begin{verbatim}

+<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>

+

+  <!-- 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>

+\end{verbatim}

+\end{example}

+

+\subsection{1:N \& M:N Solution?}

+Currently the feature that resolves this issue not implemented, but the

+development discussions are active, and we expect it to be included in a

+future release.

+

+\begin{mybox}{Lazy Loading vs. Joins (1:M and M:N):}

+As with the 1:1 situation described previously, it's important to note that

+using a join is not always better. This is even more true for collection

+properties than it was for individual value properties due to the greater

+amount of data. If you are in a situation where it is rare to access the

+related object (e.g. the ProductList property of the Category class) then it

+might actually be faster to avoid the join and the unnecessary loading of the

+list of products. This is especially true for database designs that involve

+outer joins or nullable and/or non-indexed columns. In these situations it

+might be better to use the sub-select solution with the lazy loading. The

+general rule of thumb is: use the join if you're more likely going to access

+the associated properties than not. Otherwise, only use it if lazy loading is

+not an option.

+\\

+\\

+As mentioned earlier, if you're having trouble deciding which way to go, don't

+worry. No matter which way you go, you can always change it without impacting

+your PHP code. The two examples above would result in exactly the same object

+graph and are loaded using the exact same method call. The only consideration

+is that if you were to enable caching, then the using the separate select (not

+the join) solution could result in a cached instance being returned. But more

+often than not, that won't cause a problem (your application should not be

+dependent on instance level equality i.e. ``==='').

+\end{mybox}

+

+\section{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 \tt{resultMap} by the \tt{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 \tt{\#value\#}. The alternate

+syntax for the column attribute is simply \{param1=column1, param2=column2,

+$\cdots$, paramN=columnN\}. Consider the example below where the PAYMENT table

+is keyed by both Customer ID and Order ID:

+

+\begin{example}\label{example:3.44}

+Mapping a composite key

+\begin{verbatim}

+<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>

+\end{verbatim}

+\end{example}

+

+Optionally you can just specify the column names as long as they're in the

+same order as the parameters. For example:

+\begin{verbatim}

+{ORD_ID, ORD_CST_ID}

+\end{verbatim}

+

+\begin{mybox}{Important!}

+Currently the SQLMap DataMapper framework does not automatically resolve

+circular relationships. Be aware of this when implementing parent/child

+relationships (trees). An easy work around 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.

+\end{mybox}

+

+\begin{mybox}{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 \tt{<sqlMap>} root element.

+\end{mybox}