IBatis.Net与IBatisNet中ExecuteQueryForMap和QueryForDictionary的用法-优快云博客

详细资料请参考：

什么是iBatis.net ?

以SQLMapping为核心，提供基于ado.net之上的一层简单抽象，将数据库中数据映射到领域模型的持久层框架

iBatis.net的原理

新人指路

新建工程，添加2个引用文件

添加并修改配置3个配置文件

根据数据库的表结构及业务设计领域模型（可以手动，或利用codesmith等生成器半自动完成
由模板或手动生成SQL Mapping文件(e.g.Account.xml),并根据业务需求撰写sql语句

更详细的使用过程，可参见视频。

iBatis.net的优缺点

优点

缺点

· 集中管理，聚焦，减少代码量

· 传入参数仅能一个，多于一个需要进行包装

· 细粒度控制

· 混合型解决方案

· 思维方式

· 非强类型，运行时，非编译时

SQL Mapping金典案例

情景一基本标签(insert,update ,select)及属性(parameterClass,resultClass)

在iBatis.net原理一节示例了使用ibatis.net进行insert和select操作，下面展示介绍如何具体的进行SqlMapping。

在SqlMapping的配置文件本例中为Account.xml文件中，添加如下的标签段

并将此Account.xml文件的路径加入到sqlmap.config中

在iBatis.net中提供了五种常用的标签如下表示

表1 基本SQLMapping标签

标签	说明
<insert></insert>	增加sql语句
<update></update>	更新sql语句
<select></select>	选取sql语句
<delete></delete>	删除sql语句
<procedure></procedure>	调用储存过程

在这些容器标签中有一些常用的属性如下所示

表2 基本SQL Mapping标签属性

属性	说明
Id	用于唯一标识一条sql语句
resultClass	用于将sql语句返回的字段和resultClass提定的类进行隐式的自动映射
parameterClass	用于指定输入参数的类型，如果输入参数有多个应使用Hashtable或是类进行包装

下面是关于这两条SQL语句的调用方法和测试

情景二子标签(<sql/><include />)及转义处理

在一些复杂的情景中，为了简化SQL的编写工作，ibatis.net提供了一些标签用于简化SQL的编写操作。可以将可复用的sql语句写在<sql></sql>标签中，然后<select/><insert/><update/><delete/>等标签中使用<includerefid="xxx" />进行引用，其中refid属性指向<sql/>子标签的标识id。

如下例中所示

在此例中，我们还使用了了<![CDATA[]]>,主要是在sql语句中有些特殊的字符如<>等，在xml中不能直接使用，因为我们将包含特殊字符的 SQL语句放入XML的CDATA块中

在此例中，通过Hashtable传入了两个参数，下面介绍了程序中是如何调用上面这段代码的

情景三 高阶标签(<dynamic/>)及属性(resultMap)

当在进行一些组合查询等需要灵活组装sql的情景时，需要用到dynamic标签。如下所示。

这段代码可以大至翻译为如下的伪代码

Accountacc=new Account();

acc.Item="买菜";

if(acc.Item!=null)

sql+="whereitem like '%"+acc.Item+"%'";

If(acc.Money!=null)

sql+="orMoney like '%20%';

this.Response.Write(sql)

表3 <dynamic>元素

名称	描述
isEqual	如果参数相等于值则查询条件有效
isNotEqual	如果参数不等于值则查询条件有效
isGreaterThan	如果参数大于值则查询条件有效
isGreaterEqual	如果参数大于等于值则查询条件有效
isLessEqual	如果参数小于值则查询条件有效
isPropertyAvaiable	如果参数可用则查询条件有效
isNotPropertyAvaiable	如果参数不可用则查询条件有效
isNull	如果参数为Null则查询条件有效
isNotNull	如果参数不为Null则查询条件有效
isEmpty	如果参数为空则查询条件有效
isNotEmpty	如果参数不为空则查询条件有效。参数的数据类型为Collection、string时参数不为 NULLo ""
isParameterPresent	如果参数类为Null则查询条件有效
isNotParameterPresent	如果参数类不为Null则查询条件有效

在上面的属性中使用了resultMap属性，原先使用resultClass是自动隐式的将sql语句映射出的字段。而resultMap属性用于将select输出的语句进行显示的映射，如下所示

将sql语句取出数据的字段名名称和领域模型进行一个显示的映射。

情景四储存过程标<procedure/>及属性parameterMap

在SQL Mapping中专门为储存过程提供了标签，如下所示：

其调用方法是在容器标签中撰写储存过程的名称，此写法适合SQL Server,针对其它数据库的方法参见储存过程调用方法。

注意到在这里我们使用了一个新的属性parameterMap,它的作用是对输入的参数进行显示的映射，之前在情景一中介绍过parameterClass,它和parameterMap类似均是用来对传入sql的参数时行映射，不同的是对parameterClass是隐式映射的，而parameterMap是显示的映射。

注意到，有一个direction属性默认为储存过程的in,可以特别指定用于其它的方式如out或inout等。

在这里我们也使用了resultMap属性，该属性已在情景三中进行了介绍，读者忘了可以再温习一下

关于储存过程的具体的调用方法参见如下的示例，该储存过程的作用是对Accounts表进行分页返回指定页面的记录数和总的记录数。

IBatisNet的ExecuteQueryForMap和QueryForDictionary用法的官方完全详细实例教程

详细文档请参考：

http://blog.youkuaiyun.com/zfrong/article/details/3882213 ExecuteQueryForMap和QueryForDictionary用法

http://www.uml.org.cn/j2ee/200806111.asp UML软件工程组织

#regionQueryForDictionary
/// <summary>
/// Test ExecuteQueryForDictionary
/// </summary>
[Test]
public void TestExecuteQueryForDictionary()
{
IDictionary<string, Account> map = sqlMap.QueryForDictionary<string,Account>("GetAllAccountsViaResultClass", null,"FirstName");

Assert.AreEqual(5, map.Count);
AssertAccount1(map["Joe"]);

Assert.AreEqual(1, map["Joe"].Id);
Assert.AreEqual(2, map["Averel"].Id);
Assert.AreEqual(3, map["William"].Id);
Assert.AreEqual(4, map["Jack"].Id);
Assert.AreEqual(5, map["Gilles"].Id);
}

/// <summary>
/// Test ExecuteQueryForDictionary With Cache.
/// </summary>
[Test]
public void TestExecuteQueryQueryForDictionaryWithCache()
{
IDictionary<string, Account> map = sqlMap.QueryForDictionary<string,Account>("GetAllAccountsCache", null, "FirstName");

int firstId = HashCodeProvider.GetIdentityHashCode(map);

Assert.AreEqual(5, map.Count);
AssertAccount1(map["Joe"]);

Assert.AreEqual(1, map["Joe"].Id);
Assert.AreEqual(2, map["Averel"].Id);
Assert.AreEqual(3, map["William"].Id);
Assert.AreEqual(4, map["Jack"].Id);
Assert.AreEqual(5, map["Gilles"].Id);

map = sqlMap.QueryForDictionary<string,Account>("GetAllAccountsCache", null, "FirstName");

int secondId = HashCodeProvider.GetIdentityHashCode(map);

Assert.AreEqual(firstId, secondId);
}

/// <summary>
/// Test ExecuteQueryForMap : Hashtable.
/// </summary>
/// <remarks>
/// If the keyProperty is an integer, you must acces the map
/// by map[integer] and not by map["integer"]
/// </remarks>
[Test]
public void TestExecuteQueryForDictionary2()
{
IDictionary<string, Order> map = sqlMap.QueryForDictionary<string,Order>("GetAllOrderWithLineItems", null, "PostalCode");

Assert.AreEqual(11, map.Count);
Order order = map["T4H 9G4"];

Assert.AreEqual(2, order.LineItemsIList.Count);
}

/// <summary>
/// Test ExecuteQueryForMap with value property :
/// "FirstName" as key, "EmailAddress" as value
/// </summary>
[Test]
public void TestExecuteQueryForDictionaryWithValueProperty()
{
IDictionary<string, string> map = sqlMap.QueryForDictionary<string,string>("GetAllAccountsViaResultClass", null,"FirstName", "EmailAddress");

Assert.AreEqual(5, map.Count);

Assert.AreEqual("Joe.Dalton@somewhere.com", map["Joe"]);
Assert.AreEqual("Averel.Dalton@somewhere.com",map["Averel"]);
Assert.IsNull(map["William"]);
Assert.AreEqual("Jack.Dalton@somewhere.com", map["Jack"]);
Assert.AreEqual("gilles.bayon@nospam.org", map["Gilles"]);
}

#endregion

-----------------------------------------------------------------------------------------------------------------------

#region Map Tests

/// <summary>
/// Test ExecuteQueryForMap : Hashtable.
/// </summary>
[Test]
public void TestExecuteQueryForMap()
{
IDictionary map = sqlMap.QueryForMap("GetAllAccountsViaResultClass",null, "FirstName");

Assert.AreEqual(5, map.Count);
AssertAccount1(((Account)map["Joe"]));

Assert.AreEqual(1, ((Account)map["Joe"]).Id);
Assert.AreEqual(2, ((Account)map["Averel"]).Id);
Assert.AreEqual(3, ((Account)map["William"]).Id);
Assert.AreEqual(4, ((Account)map["Jack"]).Id);
Assert.AreEqual(5, ((Account)map["Gilles"]).Id);
}

/// <summary>
/// Test ExecuteQueryForMap With Cache : Hashtable.
/// </summary>
[Test]
public void TestExecuteQueryForMapWithCache()
{
IDictionary map = sqlMap.QueryForMap("GetAllAccountsCache", null,"FirstName");

int firstId = HashCodeProvider.GetIdentityHashCode(map);

Assert.AreEqual(5, map.Count);
AssertAccount1(((Account)map["Joe"]));

Assert.AreEqual(1, ((Account)map["Joe"]).Id);
Assert.AreEqual(2, ((Account)map["Averel"]).Id);
Assert.AreEqual(3, ((Account)map["William"]).Id);
Assert.AreEqual(4, ((Account)map["Jack"]).Id);
Assert.AreEqual(5, ((Account)map["Gilles"]).Id);

map = sqlMap.QueryForMap("GetAllAccountsCache", null,"FirstName");

int secondId = HashCodeProvider.GetIdentityHashCode(map);

Assert.AreEqual(firstId, secondId);
}

/// <summary>
/// Test ExecuteQueryForMap : Hashtable.
/// </summary>
/// <remarks>
/// If the keyProperty is an integer, you must acces the map
/// by map[integer] and not by map["integer"]
/// </remarks>
[Test]
public void TestExecuteQueryForMap2()
{
IDictionary map = sqlMap.QueryForMap("GetAllOrderWithLineItems",null, "PostalCode");

Assert.AreEqual(11, map.Count);
Order order = ((Order)map["T4H 9G4"]);

Assert.AreEqual(2, order.LineItemsIList.Count);
}

/// <summary>
/// Test ExecuteQueryForMap with value property :
/// "FirstName" as key, "EmailAddress" as value
/// </summary>
[Test]
public void TestExecuteQueryForMapWithValueProperty()
{
IDictionary map = sqlMap.QueryForMap("GetAllAccountsViaResultClass",null, "FirstName", "EmailAddress");

Assert.AreEqual(5, map.Count);

Assert.AreEqual("Joe.Dalton@somewhere.com", map["Joe"]);
Assert.AreEqual("Averel.Dalton@somewhere.com",map["Averel"]);
Assert.IsNull(map["William"]);
Assert.AreEqual("Jack.Dalton@somewhere.com", map["Jack"]);
Assert.AreEqual("gilles.bayon@nospam.org", map["Gilles"]);
}

/// <summary>
/// Test ExecuteQueryForWithJoined
/// </remarks>
[Test]
public void TestExecuteQueryForWithJoined()
{
Order order = sqlMap.QueryForObject("GetOrderJoinWithAccount", 10) asOrder;

Assert.IsNotNull(order.Account);

order = sqlMap.QueryForObject("GetOrderJoinWithAccount", 11) asOrder;

Assert.IsNull(order.Account);
}

/// <summary>
/// Better support for nested result maps when using dictionary
/// </remarks>
[Test]
[Category("JIRA-254")]
public void Better_Support_For_Nested_Result_Maps_When_Using_Dictionary()
{
IDictionary order = (IDictionary)sqlMap.QueryForObject("JIRA-254",10);

Assert.IsNotNull(order["Account"]);

order = (IDictionary)sqlMap.QueryForObject("JIRA-254", 11);

Assert.IsNull(order["Account"]);
}

/// <summary>
/// Test ExecuteQueryFor With Complex Joined
/// </summary>
/// <remarks>
/// A->B->C
/// ->E
/// ->F
/// </remarks>
[Test]
public void TestExecuteQueryForWithComplexJoined()
{
A a = sqlMap.QueryForObject("SelectComplexJoined", null) as A;

Assert.IsNotNull(a);
Assert.IsNotNull(a.B);
Assert.IsNotNull(a.B.C);
Assert.IsNull(a.B.D);
Assert.IsNotNull(a.E);
Assert.IsNull(a.F);
}
#endregion

IBatisNet/IBatis动态条件查询XMl映射配置终极解决方案实例

<?xmlversion="1.0" encoding="utf-8" ?>
<sqlMap namespace="Account"
xmlns="http://ibatis.apache.org/mapping"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

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

<statements>

<sql id="includeComplex">
<dynamic prepend="where">
<isParameterPresent>
<isNotEmpty prepend="and" property="Id">
Account_ID = #Id#
</isNotEmpty>
<isNotEmpty prepend="and" property="FirstName">
Account_FirstName = #FirstName#
</isNotEmpty>
<isNotEmpty prepend="and" property="LastName">
Account_LastName = #LastName#
</isNotEmpty>
</isParameterPresent>
</dynamic>
</sql>

<select id="DynamicWithPrepend"
parameterClass="Account"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<include refid="includeComplex"/>
</select>

<statement id="DynamicJIRA168"
parameterClass="Query"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="where">
<isParameterPresent>
<isNotEmpty prepend="and" property="DataObject.Id" >
Account_ID = #DataObject.Id#
</isNotEmpty>
<isNotEmpty prepend="and"property="DataObject.FirstName" >
Account_FirstName = #DataObject.FirstName#
</isNotEmpty>
<isNotEmpty prepend="and" property="DataObject.LastName">
Account_LastName = #DataObject.LastName#
</isNotEmpty>
</isParameterPresent>
</dynamic>
</statement>


<statement id="DynamicSqlOnColumnSelection"
parameterClass="Account"
resultClass="Account"
remapResults="true">
SELECT
Account_ID as Id,
<dynamic>
<isEqual property="LastName" compareValue="Dalton" >
Account_FirstName as FirstName,
</isEqual>
<isEqual property="LastName" compareValue="Dalton" >
Account_LastName as LastName,
</isEqual>
</dynamic>

Account_Email as EmailAddress
FROM
Accounts
</statement>

<statement id="DynamicIsEqual"
parameterClass="string"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isEqual compareValue="Joe" >
where Account_FirstName = 'Joe'
</isEqual>
</statement>

<statement id="DynamicIsParameterPresent"
parameterClass="integer"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isParameterPresent >
where Account_ID = #value#
</isParameterPresent>
</statement>

<statement id="DynamicIsNotEmpty"
parameterClass="string"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isNotEmpty>
where Account_FirstName = #value#
</isNotEmpty>
</statement>

<statement id="DynamicIsGreater"
parameterClass="int"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isGreaterThan compareValue="3" >
where Account_ID = 1
</isGreaterThan>
</statement>

<statement id="DynamicIsGreaterEqual"
parameterClass="int"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isGreaterEqual compareValue="3" >
where Account_ID = 1
</isGreaterEqual>
</statement>

<statement id="DynamicIsLess"
parameterClass="int"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isLessThan compareValue="3" >
where Account_ID = 1
</isLessThan>
</statement>

<statement id="DynamicIsLessEqual"
parameterClass="int"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isLessEqual compareValue="3" >
where Account_ID = 1
</isLessEqual>
</statement>

<statement id="DynamicIsNotNull"
parameterClass="string"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isNotNull>
where Account_ID = 1
</isNotNull>
</statement>

<statement id="DynamicIsPropertyAvailable"
parameterClass="string"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<isPropertyAvailable property="Id" >
where Account_ID = 1
</isPropertyAvailable>
</statement>

<statement id="DynamicSubst"
parameterClass="map"
resultClass="Account">
<dynamic>
$statement$
</dynamic>
</statement>

<statement id="DynamicIterate"
parameterClass="list"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
WHERE Account_ID IN
<iterate open="(" close=")" conjunction=",">
#[]#
</iterate>
</statement>

<statement id="DynamicIterate2"
parameterClass="Account"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
WHERE Account_ID IN
<iterate property="Ids" open="(" close=")"conjunction="," >
#Ids[]#
</iterate>
</statement>

<statement id="MultiDynamicIterate"
parameterClass="list"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
WHERE Account_ID IN
<iterate open="(" close=")"conjunction=",">
#[]#
</iterate>
and Account_ID IN
<iterate open="(" close=")"conjunction=",">
#[]#
</iterate>
</statement>

<select id="DynamicAll"
parameterClass="Account"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
</select>

<statement id="DynamicQueryByExample"
parameterClass="Account"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="WHERE" >
<isGreaterThan prepend="AND" property="Id"compareValue="0" >
Account_ID = #Id#
</isGreaterThan>
<isNotNull prepend="AND" property="Ids" >
Account_ID in
<iterate property="Ids" open="(" close=")"conjunction="," >
#Ids[]#
</iterate>
</isNotNull>
<isNotEmpty prepend="AND" property="FirstName" >
Account_FirstName = #FirstName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="LastName" >
Account_LastName = #LastName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="EmailAddress" >
<isEqual property="EmailAddress"compareValue="gilles">
Account_Email = 'gilles.bayon@nospam.org'
</isEqual>
<isNotEqual property="EmailAddress"compareValue="gilles">
Account_Email = #EmailAddress#
</isNotEqual>
</isNotEmpty>
</dynamic>
</statement>

<statement id="DynamicQueryByExampleViaPrivateField"
parameterClass="Account"
resultClass="Account">
select
Account_ID as id,
Account_FirstName as _firstName,
Account_LastName as _lastName,
Account_Email as _emailAddress
from Accounts
<dynamic prepend="WHERE" >
<isGreaterThan prepend="AND" property="id"compareValue="0" >
Account_ID = #id#
</isGreaterThan>
<isNotNull prepend="AND" property="Ids" >
Account_ID in
<iterate property="_ids" open="(" close=")"conjunction="," >
#_ids[]#
</iterate>
</isNotNull>
<isNotEmpty prepend="AND" property="_firstName" >
Account_FirstName = #FirstName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="_lastName" >
Account_LastName = #LastName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="EmailAddress" >
<isEqual property="_emailAddress"compareValue="gilles">
Account_Email = 'gilles.bayon@nospam.org'
</isEqual>
<isNotEqual property="_emailAddress"compareValue="gilles">
Account_Email = #_emailAddress#
</isNotEqual>
</isNotEmpty>
</dynamic>
</statement>

<select id="DynamicWithExtend"
extends="DynamicAll"
parameterClass="Account"
resultClass="Account">
<dynamic prepend="WHERE" >
<isGreaterThan prepend="AND" property="Id"compareValue="0" >
Account_ID = #Id#
</isGreaterThan>
<isNotNull prepend="AND" property="Ids" >
Account_ID in
<iterate property="Ids" open="(" close=")"conjunction="," >
#Ids[]#
</iterate>
</isNotNull>
<isNotEmpty prepend="AND" property="FirstName" >
Account_FirstName = #FirstName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="LastName" >
Account_LastName = #LastName#
</isNotEmpty>
<isNotEmpty prepend="AND" property="EmailAddress" >
<isEqual property="EmailAddress" compareValue="Joe">
Account_Email = 'clinton.begin@ibatis.com'
</isEqual>
<isNotEqual property="EmailAddress"compareValue="Joe">
Account_Email = #EmailAddress#
</isNotEqual>
</isNotEmpty>
</dynamic>
</select>

<statement id="DynamicIterateWithPrepend1"
parameterClass="list"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="where" >
Account_ID IN
<iterate open="(" close=")" conjunction=",">
#[]#
</iterate>
</dynamic>
</statement>

<statement id="DynamicIterateWithPrepend2"
parameterClass="list"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="where" >
<iterate open="(" close=")"conjunction="OR">
Account_ID = #[]#
</iterate>
</dynamic>
</statement>

<statement id="DynamicIterateWithPrepend3"
parameterClass="list"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="where" >
<isParameterPresent prepend="BLAH!" >
<iterate open="(" close=")"conjunction="OR">
Account_ID = #[]#
</iterate>
</isParameterPresent>
</dynamic>
</statement>

<statement id="DynamicWithTwoDynamicElements"
parameterClass="Account"
resultClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="where">
<isNotEmpty prepend="BLAH!" property="Id" >
Account_ID = #Id#
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isNotEmpty prepend="BLAH!" property="FirstName" >
Account_FirstName = #FirstName#
</isNotEmpty>
<isNotEmpty prepend="and" property="LastName" >
Account_LastName = #LastName#
</isNotEmpty>
</dynamic>
</statement>

<statement id="ComplexDynamicStatement"
cacheModel="account-cache"
resultClass="Account"
parameterClass="Account">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="WHERE">
<isNotNull prepend="AND" property="FirstName">
(Account_FirstName = #FirstName#
<isNotNull prepend="OR" property="LastName">
Account_LastName = #LastName#
</isNotNull>
)
</isNotNull>
<isNotNull prepend="AND" property="EmailAddress">
Account_Email like #EmailAddress#
</isNotNull>
<isGreaterThan prepend="AND" property="Id"compareValue="0">
Account_ID = #Id#
</isGreaterThan>
</dynamic>
order by Account_LastName
</statement>

<statement id="Jira-IBATISNET-11"
resultClass="Account"
parameterClass="Search">
select
Account_ID as Id,
Account_FirstName as FirstName,
Account_LastName as LastName,
Account_Email as EmailAddress
from Accounts
<dynamic prepend="where">
<isNotNull prepend="and" property="NumberSearch">
((Account_ID $Operande$ #NumberSearch#) or
(Account_ID $Operande$ #NumberSearch#))
</isNotNull>
<isEqual prepend="and" property="StartDate"compareValue="25/12/2004">
<![CDATA[Account_FirstName >= #Operande# ]]>
</isEqual>
<isEqual prepend="and" property="StartDateAnd"compareValue="true">
<![CDATA[Account_LastName >= #Operande# ]]>
</isEqual>
</dynamic>

order by Account_LastName
</statement>

<insert id="SelectKeyWithDynamicSql">
INSERT INTO $AccountsTableName$
(
Account_ID,
Account_FirstName,
Account_LastName
)
VALUES
(
#Account.Id#,
#Account.FirstName#,
#Account.LastName#
)
<selectKey property="AccountId" type="post"resultClass="int">
SELECT Account_ID FROM $AccountsTableName$ WHERE Account_ID = #Account.Id#
</selectKey>
</insert>

</statements>
</sqlMap>