引用請(qǐng)注明http://www.rzrgm.cn/13590/archive/2013/03/14/2958735.html 

摘要：查詢是數(shù)據(jù)庫SQL語言的核心，本文介紹了通過iBatis.Net對(duì)數(shù)據(jù)庫的簡單查詢、條件查詢、動(dòng)態(tài)查詢和多表查詢。

關(guān)鍵詞：iBatis.Net；動(dòng)態(tài)查詢；多表查詢；數(shù)據(jù)映射

查詢是數(shù)據(jù)庫SQL語言的核心，SQL語言只提供唯一一個(gè)用于數(shù)據(jù)庫查詢的語句，即SELECT語句。用于表達(dá)SQL查詢的SELECT語句是功能最強(qiáng)也是最復(fù)雜的SQL語句。而在實(shí)際的項(xiàng)目開發(fā)過程中，查詢占了一個(gè)很大的比重，通常衡量一個(gè)框架的好壞也很大程度上取決于該框架對(duì)查詢的靈活性和效率。本節(jié)介紹在iBatis.Net中提供的數(shù)據(jù)庫查詢方式。

在上節(jié)建立的項(xiàng)目文件中新添加Maps/Test3.xml和Test3.aspx項(xiàng)，分別記錄XML數(shù)據(jù)映射信息和相應(yīng)的程序調(diào)用信息。

1、簡單查詢

獲取一個(gè)表的內(nèi)容，如獲取DEAN.SYSUSER表的用戶信息，XML數(shù)據(jù)映射配置信息為：

<selectid="SelectSysuser"resultMap="SysuserResult">

SELECT * FROM DEAN.SYSUSER

</select>

調(diào)用代碼為：

protectedvoid Button1_Click(object sender, EventArgs e)

{

//簡單查詢

try

{

ISqlMapper mapper = Mapper.Instance(); //得到ISqlMapper實(shí)例

IList<iBatisTest.Domain.Sysuser> plist = mapper.QueryForList<iBatisTest.Domain.Sysuser>("Test3Map.SelectSysuser",null);//調(diào)用QueryForList方法

if (plist != null && plist.Count > 0)

{

GridView1.DataSource = plist;

GridView1.DataBind();

}

Label1.Text = "簡單查詢成功";

}

catch (Exception ex)

{

Label1.Text = ex.Message;

}

}

這種查詢結(jié)果返回的是整張表的所有記錄，無需傳入查詢參數(shù)，在調(diào)用QueryForList方法時(shí)把參數(shù)置為null。考慮到系統(tǒng)的效率，在實(shí)際開發(fā)中，對(duì)記錄數(shù)少的小表才使用。

2、條件查詢

根據(jù)條件來查詢結(jié)果，條件可以是一個(gè)或者多個(gè)。這種方式在實(shí)際查詢中被廣泛使用，是應(yīng)用最多的一種查詢。如根據(jù)登錄用戶名查詢?cè)撚脩粜畔ⅲ瑪?shù)據(jù)映射配置信息為：

<selectid="SelectSysuserByUserName"parameterClass="string"resultMap="SysuserResult">

SELECT * FROM DEAN.SYSUSER WHERE LOGINNAME=#value#

</select>

iBatis.Net通過使用#或者$符號(hào)來占位，即標(biāo)識(shí)參數(shù)。在XML數(shù)據(jù)映射文件的SQL語句中引入?yún)?shù)有2種方式，一種是內(nèi)聯(lián)參數(shù)方式，即使用parameterClass，如上面的配置就采用這種方式。一種是參數(shù)映射方式，使用parameterMap。采用內(nèi)聯(lián)方式時(shí)，允許我們把屬性名稱、屬性類型、空置方式配置在SQL語句中。

使用內(nèi)聯(lián)參數(shù)方式時(shí)，傳入的參數(shù)有3種類型，分別為：

（1）基本參數(shù)類型，很多SQL語句在查詢時(shí)只接受一個(gè)參數(shù)，如int、string，使用#value#來引用，這個(gè)value是關(guān)鍵字，不可變，上面的示例就采用這種方式。也可以采用#keyName#來引用，keyName為鍵名，注意要區(qū)分大小寫。

（2）字典類型參數(shù)，使用IDictionary類型的對(duì)象作為參數(shù)。通常可以使用Hashtable。如上面配置可修改為

<select id="SelectSysuserByUserName" parameterClass="System.Collections.IDictionary" resultMap="SysuserResult">

SELECT * FROM DEAN.SYSUSER WHERE LOGINNAME=#LOGINNAME#

</select>

（3）對(duì)象類型參數(shù)，可以是一個(gè)類或者是哈希表Hashtable，在使用哈希表時(shí)，某一個(gè)鍵值可以是一個(gè)列表List類型。

調(diào)用代碼如下：

protectedvoid Button2_Click(object sender, EventArgs e)

{

//條件查詢

try

{

string UserName = TextBox1.Text;//獲取查詢參數(shù)用戶名

ISqlMapper mapper = Mapper.Instance(); //得到ISqlMapper實(shí)例

IList<iBatisTest.Domain.Sysuser> plist = mapper.QueryForList<iBatisTest.Domain.Sysuser>("Test3Map.SelectSysuserByUserName", UserName);//調(diào)用QueryForList方法

if (plist != null && plist.Count > 0)

{

GridView1.DataSource = plist;

GridView1.DataBind();

}

Label1.Text = "條件查詢成功";

}

catch (Exception ex)

{

Label1.Text = ex.Message;

}

}

如果采用字典類型參數(shù)傳入，只需要把調(diào)用中的參數(shù)修改為：

Hashtable hash = new Hashtable();//聲明哈希表

hash.Add("LOGINNAME ", TextBox1.Text); //獲取查詢參數(shù)用戶名

3、動(dòng)態(tài)查詢

iBatis.Net提供查詢的靈活性主要體現(xiàn)在支持動(dòng)態(tài)查詢上，即可以動(dòng)態(tài)的生成SQL語句。也只有掌握好動(dòng)態(tài)查詢，才能充分的感受iBatis框架所帶來的便捷和高效。這也是很多軟件公司和開發(fā)人員選擇該框架的重要原因。

在開發(fā)中經(jīng)常遇到這種查詢，當(dāng)用戶沒有輸入查詢條件時(shí)查詢所有記錄，如果用戶輸入了查詢條件將根據(jù)查詢條件進(jìn)行查詢。比如上面提到的條件查詢，如果沒有輸入用戶名信息將返回所有用戶信息。這個(gè)時(shí)候就需要用到動(dòng)態(tài)查詢，根據(jù)參數(shù)值是否為空，生成兩條不同的SQL語句。XML數(shù)據(jù)映射配置信息為：

<selectid="SelectSysuserDynamic1"parameterClass="System.Collections.IDictionary"resultMap="SysuserResult">

<![CDATA[ SELECT * FROM DEAN.SYSUSER ]]>

<dynamicprepend="WHERE">

<isNotEmptyprepend="AND"property="LOGINNAME">

<![CDATA[ LOGINNAME = #LOGINNAME# ]]>

</isNotEmpty>

</dynamic>

</select>

dynamic元素用來區(qū)分SQL語句的動(dòng)態(tài)部分，dynamic是一個(gè)可選項(xiàng)，它中間可以包含任意數(shù)據(jù)的條件元素。上面的配置信息會(huì)根據(jù)輸入?yún)?shù)LOGINNAME的值是否為空生成兩條SQL語句。如果為空SELECT * FROM DEAN.SYSUSER，如果不為空SELECT * FROM DEAN.SYSUSER WHERE LOGINNAME = #LOGINNAME#。

調(diào)用程序代碼為：

protectedvoid Button3_Click(object sender, EventArgs e)

{

//動(dòng)態(tài)查詢1

try

{

Hashtable hash = newHashtable();//聲明哈希表

hash.Add("LOGINNAME", TextBox2.Text); //獲取查詢參數(shù)用戶名

ISqlMapper mapper = Mapper.Instance(); //得到ISqlMapper實(shí)例

IList<iBatisTest.Domain.Sysuser> plist = mapper.QueryForList<iBatisTest.Domain.Sysuser>("Test3Map.SelectSysuserDynamic1", hash);//調(diào)用QueryForList方法

if (plist != null && plist.Count > 0)

{

GridView1.DataSource = plist;

GridView1.DataBind();

}

Label1.Text = "動(dòng)態(tài)查詢1成功";

}

catch (Exception ex)

{

Label1.Text = ex.Message;

}

}

}

在iBatis.Net中，動(dòng)態(tài)查詢的條件元素包含以下幾種：二元條件元素、一元條件元素和其他條件元素：

3.1二元條件元素

將一個(gè)屬性值和靜態(tài)值或另一個(gè)屬性值比較，如果條件為真，元素將被包容在查詢SQL語句中。

二元條件元素的屬性：

perpend——可被覆蓋的SQL語句組成部分，添加在語句的前面，該屬性為可選。

property——是比較的屬性，該屬性為必選。

compareProperty——另一個(gè)用于和前者比較的屬性(必選或選擇compareValue屬性)

compareValue——用于比較的值(必選或選擇compareProperty屬性)

二元條件元素為：

<isLessEqual prepend="AND" property="age"              compareValue="18">

ADOLESCENT = 'TRUE'

</isLessEqual>

二元條件元素多用在數(shù)字的區(qū)間選擇上，如年齡、價(jià)格、面積等選擇上面，也可以用在日期、字符串等類型的比較。如只顯示ID<=10的指定ID的用戶信息，如果輸入值大于10則顯示全部用戶信息。XML數(shù)據(jù)映射配置信息為：

<selectid="SelectSysuserDynamic2"parameterClass="System.Collections.IDictionary"resultMap="SysuserResult">

<![CDATA[ SELECT * FROM DEAN.SYSUSER ]]>

<dynamicprepend="WHERE">

<isLessEqualprepend="AND"property="USERID"compareValue="10">

USERID = #USERID#

</isLessEqual>

</dynamic>

</select>

調(diào)用代碼為：

protectedvoid Button4_Click(object sender, EventArgs e)

{

//動(dòng)態(tài)查詢2：二元條件元素查詢

try

{

Hashtable hash = newHashtable();//聲明哈希表

int ID = 0;

if (!string.IsNullOrWhiteSpace(TextBox3.Text))

{

ID = Convert.ToInt32(TextBox3.Text);

}

hash.Add("USERID", ID); //獲取查詢參數(shù)

ISqlMapper mapper = Mapper.Instance(); //得到ISqlMapper實(shí)例

IList<iBatisTest.Domain.Sysuser> plist = mapper.QueryForList<iBatisTest.Domain.Sysuser>("Test3Map.SelectSysuserDynamic2", hash);//調(diào)用QueryForList方法

if (plist != null && plist.Count > 0)

{

GridView1.DataSource = plist;

GridView1.DataBind();

}

else

{

GridView1.DataSource = null;

GridView1.DataBind();

}

Label1.Text = "動(dòng)態(tài)查詢2成功";

}

catch (Exception ex)

{

Label1.Text = ex.Message;

}

}

3.2一元條件元素

一元條件元素檢查屬性的狀態(tài)是否符合特定的條件。即檢查屬性值是否滿足條件，如果滿足則查詢條件有效。

一元條件元素的屬性和二元條件元素一樣，具有prepend和property屬性，其中property為必選屬性。

一元條件元素為：

比如下面的配置例子：

<selectid="SelectSysuserDynamic3"resultMap="SysuserResult"parameterClass="System.Collections.IDictionary">

<![CDATA[ SELECT * FROM DEAN.SYSUSER ]]>

<dynamicprepend="WHERE">

<isPropertyAvailableproperty="SEX">

<isNotNullproperty="SEX"prepend="AND">

SEX=#SEX#

</isNotNull>

</isPropertyAvailable>

<isPropertyAvailableproperty="STATUS">

<isNotNullproperty="STATUS"prepend="AND">

STATUS=#STATUS#

</isNotNull>

</isPropertyAvailable>

</dynamic>

</select>

先判斷傳入?yún)?shù)集是否有SEX參數(shù)，如果沒有則不執(zhí)行SEX=#SEX#查詢條件，再判斷該參數(shù)是否為null，不為null才執(zhí)行查詢條件。isPropertyAvailable元素最大的好處是，如果輸入的參數(shù)集不包括設(shè)置的參數(shù)時(shí)程序不會(huì)報(bào)錯(cuò)，直接跳過該元素設(shè)置內(nèi)容。

3.3其他元素條件

其他元素條件有兩個(gè)元素，一個(gè)為ParameterPresent，該元素檢查參數(shù)對(duì)象是否存在，一個(gè)為Iterate，該元素為遍歷整個(gè)集合。

(1) ParameterPresent

ParameterPresent元素屬性只有prepend一個(gè)屬性，表示可被覆蓋的SQL語句組成部分，添加在語句的前面，為可選屬性。

<isParameterPresent prepend="AND">

  EMPLOYEE_TYPE = #empType#

</isParameterPresent>

<isNotParameterPresent prepend="AND">

  EMPLOYEE_TYPE = 'DEFAULT'

</isNotParameterPresent>

(2) Iterate：遍歷整個(gè)集合元素，為List集合中的元素重復(fù)元素體的內(nèi)容。

Iterate的屬性：

prepend——可被覆蓋的SQL語句組成部分，添加在語句的前面，該屬性為可選。

property——類型為List的用于遍歷的元素屬性，該屬性為必選。

open——整個(gè)遍歷內(nèi)容體開始的字符串，用于定義括號(hào)，該屬性為可選。

close ——整個(gè)遍歷內(nèi)容體結(jié)束的字符串，用于定義括號(hào)，該屬性為可選。

conjunction——每次遍歷內(nèi)容之間的字符串，用于定義AND或OR，該屬性為可選。

Iterate元素在生成sql語句時(shí)，標(biāo)簽中的內(nèi)容是循環(huán)生成的，如上面的例子將會(huì)生成語句：(username=xxx1 or username=xxx2 or username=xxx 3)。該元素也經(jīng)常用來動(dòng)態(tài)生成In查詢條件，如id in (xx1,xx2,xx3,.....)，括號(hào)中的(包括括號(hào))都由該元素標(biāo)簽生成。

比如下面的配置例子：

<selectid="SelectSysuserDynamic4"resultMap="SysuserResult"parameterClass="System.Collections.IDictionary">

<![CDATA[ SELECT * FROM DEAN.SYSUSER ]]>

<dynamicprepend=" WHERE">

<isPropertyAvailableproperty="SEX">

<isNotNullproperty="SEX"prepend="AND">

SEX=#SEX#

</isNotNull>

</isPropertyAvailable>

<isNotNullprepend="And"property="USERIDLIST">

USERID in

<iterateproperty="USERIDLIST"open="("close=")"conjunction=",">

#USERIDLIST[]#

</iterate>

</isNotNull>

</dynamic>

</select>

調(diào)用代碼為：

protectedvoid Button6_Click(object sender, EventArgs e)

{

//動(dòng)態(tài)查詢2：其它元素條件，Iterate

try

{

Hashtable hash = newHashtable();//聲明哈希表

string sex = "男";

hash.Add("SEX", sex);

List<int> IDS = newList<int>();//聲明List對(duì)象

IDS.Add(1);

IDS.Add(2);

IDS.Add(3);

hash.Add("USERIDLIST", IDS);

ISqlMapper mapper = Mapper.Instance(); //得到ISqlMapper實(shí)例

IList<iBatisTest.Domain.Sysuser> plist = mapper.QueryForList<iBatisTest.Domain.Sysuser>("Test3Map.SelectSysuserDynamic4", hash);//調(diào)用QueryForList方法

if (plist != null && plist.Count > 0)

{

GridView1.DataSource = plist;

GridView1.DataBind();

}

else

{

GridView1.DataSource = null;

GridView1.DataBind();

}

Label1.Text = "動(dòng)態(tài)查詢4成功";

}

catch (Exception ex)

{

Label1.Text = ex.Message;

}

}

系統(tǒng)會(huì)根據(jù)配置信息動(dòng)態(tài)的生成In查詢條件，最終動(dòng)態(tài)生成的SQL語句為：SELECT * FROM DEAN.SYSUSER AND SEX='男' And USERID in (1,2,3)。

4、多表查詢

前面講到的示例都是從一個(gè)表中查詢記錄，獲取的結(jié)果也是單個(gè)對(duì)象。事實(shí)上在程序開發(fā)中，經(jīng)常需要對(duì)多個(gè)表進(jìn)行組合查詢，返回的結(jié)果也是復(fù)雜對(duì)象。如查詢用戶權(quán)限信息，就需要關(guān)聯(lián)用戶表和權(quán)限表。

向數(shù)據(jù)庫添加系統(tǒng)權(quán)限表SysUserRight，腳本如下：

CREATE TABLE DEAN.SYSUSERRIGHT

(

ID NUMBER(10,0) NOT NULL ENABLE,

USERID NUMBER(10,0) NOT NULL ENABLE,

RIGHTID NUMBER(10,0) NOT NULL ENABLE,

constraint PK_SYSUSERRIGHT primary key (ID)

);

comment on column DEAN.SYSUSERRIGHT.ID is 'ID';

comment on column DEAN.SYSUSERRIGHT.USERID is '用戶ID';

comment on column DEAN.SYSUSERRIGHT.RIGHTID is '權(quán)限ID';

有兩種方式來處理這種多表查詢，一種是參照單表查詢，根據(jù)返回結(jié)果定制一個(gè)新類，或者直接設(shè)置返回參數(shù)為Hashtable表。如：

<selectid="MultiTable1"resultClass="Hashtable" >

SELECT A.*,B.RIGHTID FROM DEAN.SYSUSER A,DEAN.SYSUSERRIGHT B WHERE A.USERID=B.USERID

</select>

通過用戶ID（USERID）關(guān)聯(lián)用戶表(SYSUSER)和系統(tǒng)權(quán)限表（SYSUSERRIGHT）查詢出用戶信息及對(duì)應(yīng)的權(quán)限信息，直接返回一個(gè)Hashtable表，記錄了相應(yīng)的信息。

第二種方式是利用iBatis的復(fù)雜屬性來實(shí)現(xiàn)，在Sysuser類新增一個(gè)屬性：

///<summary>

///多表查詢新增權(quán)限屬性

///</summary>

privateint _rightid;

publicint Rightid

{

get { return _rightid; }

set {_rightid = value;}

}

修改配置文件信息，在resultMaps部分增加一個(gè)結(jié)果映射信息，唯一號(hào)為UserRightResult，它繼承于Test3Map.SysuserResult結(jié)果映射，增加的配置信息如下：

<resultMapid="UserRightResult"class="Sysuser"extends="Test3Map.SysuserResult">

<resultproperty="<spansstyle="color:blue; background-color:white">Rightid"column="USERID=USERID"select="Test3Map.SelectSysuserRight" />

</resultMap>

在statements節(jié)加入如下信息：

<statementid="SelectSysuserRight"parameterClass="int"resultClass="int">

SELECT RIGHTID FROM DEAN.SYSUSERRIGHT WHERE USERID= #USERID#

</statement>

<selectid="MultiTable2"parameterClass="int"resultMap="UserRightResult">

SELECT * FROM DEAN.SYSUSER ORDER BY USERID

</select>

通過XML配置文件中resultMap的result使用"select"進(jìn)行一種迭代查詢，也就是將<result property="Rightid" column="USERID=USERID" select="Test3Map.SelectSysuserRight" />中column指定的一項(xiàng)或多項(xiàng)作為參數(shù)（USERID=USERID），傳入并執(zhí)行指定的select語句SelectSysuserRight，并將查詢結(jié)果賦給property=" Rightid "，從而實(shí)現(xiàn)多表查詢。

該例子中實(shí)現(xiàn)的是1:1的關(guān)系查詢，如果是1:n的關(guān)系查詢，只需要Sysuser類增加的屬性修改為IList類型。Statements節(jié)點(diǎn)"SelectSysuserRight"的返回類修改為resultClass=" SysuserRightResult"。

通過iBatis復(fù)雜屬性，可以非常方便的實(shí)現(xiàn)多表查詢，但這個(gè)方法給我們帶來便利的同時(shí)，也帶來了兩個(gè)問題。首先，創(chuàng)建包含大量對(duì)象的列表可能會(huì)消耗大量的內(nèi)存。其次，這種方法會(huì)導(dǎo)致數(shù)據(jù)庫的I/O問題，其原因就是所謂的"N+1"查詢現(xiàn)象。對(duì)于主從表（也稱為父子表）的查詢，特別容易產(chǎn)生N+1查詢問題，N+1查詢問題是由于試圖加載多個(gè)父記錄（比如User）的子記錄（Right）而引起的。在查詢父記錄時(shí)，只需要1條語句，假設(shè)返回N條記錄，那么就需要再執(zhí)行N條語句來查詢子記錄，引發(fā)所謂的"N+1查詢"。

解決N+1查詢問題可以通過延遲加載（lazy loading）來實(shí)現(xiàn)，它將加載過程打散為一些更小的過程。在父子表查詢過程中，對(duì)子表的查詢往往不需要和父表一起加載，例如，系統(tǒng)的用戶管理，打開一個(gè)用戶信息頁面時(shí)顯示的是用戶信息列表，當(dāng)點(diǎn)擊一個(gè)用戶時(shí)，才需要加載該用戶的權(quán)限信息。這種情況就特別適合使用延遲加載，每次都僅查詢一個(gè)列表。使用延遲加載的時(shí)候還需要特別注意，使用的動(dòng)態(tài)代理的對(duì)象的所有方法和屬性都必須是virtual類型。

要實(shí)現(xiàn)延遲加載，只需要在配置文件里面加入lazyLoad="true"屬性就可以了。通過延遲加載，它能提高查詢的效率，但并沒有真正解決數(shù)據(jù)庫I/O問題，在最壞的情況下，它對(duì)數(shù)據(jù)庫的訪問次數(shù)與非延遲加載的時(shí)候是一樣的。如何真正解決N+1查詢問題呢？iBatis提供了連接語句（join）方式來完全避免N+1查詢的出現(xiàn)。

修改配置文件信息，在resultMaps部分增加如下配置：

<resultMapid="SysuserRightResult"class="SysuserRight">

<resultproperty="ID"column="ID"/>

<resultproperty="Userid"column="USERID" />

<resultproperty="Rightid"column="RIGHTID"/>

</resultMap>

<resultMapid="SysuserJoinResult"class="SysuserJoin"extends="Test3Map.SysuserResult"groupBy="Userid">

<resultproperty="RightList"resultMapping="Test3Map.SysuserRightResult" />

</resultMap>

這個(gè)配置使用了relultMap的resultMapping屬性，該屬性用在如果一個(gè)數(shù)據(jù)類的屬性本身不是基元數(shù)據(jù)類型，而是一個(gè)復(fù)雜數(shù)據(jù)類型的場景。這個(gè)時(shí)候就不能用一個(gè)簡單的result元素來表示，必須給他一個(gè)完整的resultMap。resultMapping的值指明RightList屬性由結(jié)果映射集SysuserRightResult所表示的復(fù)雜數(shù)據(jù)類型表示。因?yàn)橛脩艉蜋?quán)限信息是一對(duì)多的關(guān)系，在主表的結(jié)果映射上加入groupBy="Userid"屬性。

注意用戶類SysuserJoin的權(quán)限屬性RightList的定義，它是由權(quán)限類SysuserRight組成的一個(gè)列表。RightList定義如下：

///多表查詢新增權(quán)限列表屬性

privateIList<SysuserRight> _rightlist;

publicIList <SysuserRight> RightList

{

get { return _rightlist; }

set { _rightlist = value; }

}

在statements節(jié)加入如下信息

<selectid="MultiTable3"resultMap="SysuserJoinResult" >

select A.*,B.* FROM DEAN.SYSUSER A LEFT JOIN DEAN.SYSUSERRIGHT B ON A.USERID=B.USERID </select>

再通過程序的調(diào)用，就不會(huì)出現(xiàn)N+1查詢問題。

5、結(jié)束語

以上程序在VS2012(C#)+Oracle11gR2+Win7(64位)調(diào)試通過，附件的例子程序中有詳細(xì)的配置信息和程序調(diào)用代碼。