在学习动态SQL语句之前，首先必须对条件查询有一定了解，先来学习如何向IBatis.Net的映射文件里传入参数吧。
一、条件查询
1、传递单个参数
如根据Id查询：
<select id="SelectOnePerson" resultMap="PersonModel"> <!--其中#Id就是传入的参数--> select * from person where Id = #Id# </select>
调用的时候，只需要传入对应Id就可以了。
监控数据库，生成的SQL语句为：
exec sp_executesql N'select * from person where Id = @param0',N'@param0 int',@param0=1
2、传递多个参数
传递多个参数通常使用键值对，如HashTable或实体类。
- 键值对的方式
键值对的方式可以使用HashTable或Dictionary<TKey,TValue>，当为后者时映射文件中的parameterClass的值要为：System.Collections.IDictionary。
<select id="SelectPersonById" resultMap="PersonModel" parameterClass="HashTable"> SELECT * FROM Person WHERE Id = #Id# AND Name = #Name# </select>
DAO层方法：
public PersonModel SelectPersonById(Hashtable ht) { PersonModel p = mapper.QueryForObject<PersonModel>("SelectPersonById", ht); return p; }
调用：
static void Main(string[] args) { PersonDAO dao = new PersonDAO(); Hashtable ht = new Hashtable(); ht.Add("Id", 1); ht.Add("Name", "刘备"); PersonModel p = dao.SelectPersonById(ht); if (p != null) Console.WriteLine(p.PersonId + p.PersonName); Console.ReadKey(); }
生成的SQL语句为：
exec sp_executesql N'SELECT * FROM Person WHERE Id = @param0 AND Name = @param1',N'@param0 int,@param1 nvarchar(2)',@param0=1,@param1=N'刘备'
-
自定义参数类
自定义参数类通常是使用本实体类，当然你也可以自定义一个参数类(与SQL语句中的参数同名就可以)。
PersonModel类如下：
public class PersonModel { public int PersonId { get; set; } public string PersonName { get; set; } //在Person之中加入一个CountryModel public CountryModel Country { get; set; } }
因为PersonModel类定义的属性和表列名不一致(一般情况下最好和表列同名)，所以我们这重新定义一个PersonViewModel类：
public class PersonViewModel { public int Id { get; set; } public string Name { get; set; } }
然后在SqlMap.config中指定别名
<typeAlias alias="PersonViewModel" type="IbatisNetModel.PersonViewModel,IbatisNetModel"/>
XML映射文件：
<select id="SelectPersonById" resultClass="PersonViewModel" parameterClass="PersonViewModel"> SELECT * FROM Person WHERE Id = #Id# AND Name = #Name# </select>
这里没有指定resultMap，而是指定了resultClass，当property与column完全相同时可以这样做，这个在第二篇中介绍过。
DAO层：
public PersonViewModel SelectPersonById(PersonViewModel p) { PersonViewModel per = mapper.QueryForObject<PersonViewModel>("SelectPersonById", p); return per; }
调用：
static void Main(string[] args) { PersonDAO dao = new PersonDAO(); PersonViewModel p = new PersonViewModel(); p.Id = 1; p.Name = "刘备"; PersonViewModel model = dao.SelectPersonById(p); if (model != null) Console.WriteLine(p.Id + p.Name); Console.ReadKey(); }
二、动态查询
动态查询的意思就是说，当满足一定的条件，才拼接某一段SQL代码。
我们在之前的基础上改动下：
<select id="SelectPersonById" resultMap="PersonModel" parameterClass="HashTable"> SELECT * FROM Person <dynamic prepend="where"> <isLessEqual prepend="AND" property="Id" compareValue="3"> <!--当传入的参数Id小于3时，才拼接该 子句--> Id = #Id# </isLessEqual> <isNotEmpty prepend="AND" property="Name"> <!--当传入的参数Name不为""或Null时，才拼接该 子句--> Name = #Name# </isNotEmpty> </dynamic> </select>
DAO层方法：
public IList<PersonModel> SelectPersonById(Hashtable ht) { IList<PersonModel> pList = mapper.QueryForList<PersonModel>("SelectPersonById", ht); return pList; }
假如传入的参数为：ht.Add("Id", 4);
则生成的SQL语句为：
SELECT TOP 1 * FROM Person WHERE 1=1
假如传入的参数为：ht.Add("Id",2);
则生成的SQL语句为：
exec sp_executesql N'SELECT TOP 1 * FROM Person WHERE 1=1 AND Id = @param0',N'@param0 int',@param0=2
假如传入的参数为：ht.Add("Id",2); ht.Add("Name","关羽");
则生成的SQL语句为：
exec sp_executesql N'SELECT TOP 1 * FROM Person WHERE 1=1 AND Id = @param0 AND Name = @param1',N'@param0 int,@param1 nvarchar(2)',@param0=2,@param1=N'关羽'
动态查询就是这样，根据一定的判断条件，去判断是否应该拼接某段SQL代码。
dynamic可以去除第一个prepend="and"中的字符（这里为and），当两个条件都不满足时，where会自动省略，当满足其中某个条件时，则会加上where，自动去除第一个prepend="and"中的字符。我觉得还可以换一种写法，不使用dynamic，加上 where 1=1，如下：
<select id="SelectPersonById" resultMap="PersonModel" parameterClass="HashTable"> SELECT * FROM Person where 1=1 <isLessEqual prepend="AND" property="Id" compareValue="3"> <!--当传入的参数Id小于3时，才拼接该 子句--> Id = #Id# </isLessEqual> <isNotEmpty prepend="AND" property="Name"> <!--当传入的参数Name不为""或Null时，才拼接该 子句--> Name = #Name# </isNotEmpty> </select>
更多的判断条件如下：
|
属性关键字 |
含义 |
|
<isEqual> |
如果参数相等于值则查询条件有效。 |
|
<isNotEqual> |
如果参数不等于值则查询条件有效。 |
|
<isGreaterThan> |
如果参数大于值则查询条件有效。 |
|
<isGreaterEqual> |
如果参数大于等于值则查询条件有效。 |
|
<isLessEqual> |
如果参数小于值则查询条件有效。 |
|
<isPropertyAvailable> |
如果参数中有此属性则查询条件有效。 |
|
<isNotPropertyAvailable> |
如果参数中没有此属性则查询条件有效。 |
|
<isNull> |
如果参数为NULL则查询条件有效。 |
|
<isNotNull> |
如果参数不为NULL则查询条件有效。 |
|
<isEmpty> |
如果参数为空则查询条件有效。 |
|
<isNotEmpty> |
如果参数不为空则查询条件有效。 |
|
<isParameterPresent> |
如果存在参数对象则查询条件有效。 |
|
<isNotParameterPresent> |
如果不存在参数对象则查询条件有效。 |
属性说明：
- perpend——可被覆盖的SQL语句组成部分，添加在语句的前面，该属性为可选。
- property——是比较的属性，该属性为必选。
- compareProperty——另一个用于和前者比较的属性(必选或选择compareValue属性)
- compareValue——用于比较的值(必选或选择compareProperty属性)
还有一个比较特别的判断条件:iterate。它用于循环生成多个SQL片段。
<select id="SelectPersonById" resultMap="PersonModel" parameterClass="Hashtable" > SELECT * FROM Person <dynamic prepend="WHERE"> <isNotNull prepend="And" property="NameList"> <!--遍历NameList参数，对于每一个 生成一个Name in NameList[i]--> Name in <iterate property="NameList" open="(" close=")" conjunction=","> #NameList[]# </iterate> </isNotNull> </dynamic> </select>
调用方法为：
static void Main(string[] args) { PersonDAO dao = new PersonDAO();
List<string> ListStr = new List<string>(); ListStr.Add("刘备"); ListStr.Add("曹操"); ListStr.Add("孙权"); Hashtable ht = new Hashtable(); ht.Add("NameList", ListStr); IList<PersonModel> pList = dao.SelectPersonById(ht); foreach (var item in pList) { Console.WriteLine(item.PersonId + item.PersonName); } Console.ReadKey(); }
生成的SQL语句为：
exec sp_executesql N'SELECT * FROM Person WHERE Name in (@param0 ,@param1 ,@param2)',N'@param0 nvarchar(2),@param1 nvarchar(2),@param2 nvarchar(2)',@param0=N'刘备',@param1=N'曹操',@param2=N'孙权'
Iterate的属性：
- prepend——可被覆盖的SQL语句组成部分，添加在语句的前面，该属性为可选。
- property——类型为List的用于遍历的元素属性，该属性为必选。
- open——整个遍历内容体开始的字符串，用于定义括号，该属性为可选。
- close ——整个遍历内容体结束的字符串，用于定义括号，该属性为可选。
- conjunction——每次遍历内容之间的字符串，用于定义AND或OR，该属性为可选。
参考：http://www.cnblogs.com/caoyc/category/873268.html
posted on
