使用ibatis完成复合条件SQL语句的查询
[ 本文原创发表于cnblogs : 布蓝灯 ]
近来工作中用到了 ibatis 技术，主要用来完成动态条件的查询，深感这种一劳永逸的书写方式确实很genius。不过因为是在使用的过程中照猫画虎，没有深入系统地去研究，所以这几天遇到了一个很棘手的问题。在网上查了很久也没有太清楚解决办法，后来终于找到了一篇和我的问题很类似的介绍，在此要多谢原作者的 ibatis传入数组或List类型参数小结。
之前使用 ibatis 来写SQL查询语句，无非就是 WHERE 后的条件是并联的。也就是说，一些动态的条件用 AND 相连。下面举一个例子
1 <select id="SelectEnergyCost" parameterClass="Hashtable" resultClass="EnergyCostData">
2 <![CDATA[
3 SELECT * FROM UNITCOMP
4 ]]>
5 <dynamic prepend="WHERE">
6 <isParameterPresent>
7 <isNotEmpty prepend="AND" property="DATEFROM" >
8 <![CDATA[
9 DATETIME >= TO_DATE( #DATEFROM#,'YYYY-MM-DD HH24:MI:SS')
10 ]]>
11 </isNotEmpty>
12 <isNotEmpty prepend="AND" property="DATETO" >
13 <![CDATA[
14 DATETIME <= TO_DATE( #DATETO#,'YYYY-MM-DD HH24:MI:SS')
15 ]]>
16 </isNotEmpty>
17 <isNotEmpty prepend="AND" property="IGROUPID" >
18 IGROUPID = #IGROUPID#
19 </isNotEmpty >
20 <isNotEmpty prepend="AND" property="CONDITION1" >
21 CONDITION1 LIKE '%' || #CONDITION1# || '%'
22 </isNotEmpty >
23 <isNotEmpty prepend="AND" property="CONDITION2" >
24 CONDITION2 LIKE '%' || #CONDITION2# || '%'
25 </isNotEmpty >
26 <isNotEmpty prepend="AND" property="CONDITION3" >
27 CONDITION3 LIKE '%' || #CONDITION3# || '%'
28 </isNotEmpty >
29 </isParameterPresent>
30 </dynamic>
31 ORDER BY DATETIME DESC
32 </select>
（涉及工作内容，部分地方命名做了一些通用化处理）
简单说一下上面这段，这样即使对 ibatis 不熟悉的人也能更清楚看懂。
首先是<select>标签，parameterClass是入参，也就是接下来的查询语句中将要用到的各个字段的值，resultClass是输出，也就是输出的查询结果。输入输出都可以是很灵活的格式，可以只是一个整型变量，也可以是一个数据集。在我的例子中，输出是字段与值相对应的Hashtable，输出则是一个数据集，字段与所查询的表一致。
之后就可以直接写SQL语句了。假如不涉及到动态条件，那么就可以直接写完整的SQL语句。因为程序本身是xml格式，为了避免SQL语句和xml的字符冲突，可以用“<![CDATA[”和“]]>”把SQL括起来。
如果涉及到了动态条件，就要用到<dynamic>标签。ibatis 中可选的标签有很多，我这里用的是<isNotEmpty>，主要也是因为平台原有的一些语句也都是用的这个，我就照着搬下来了。具体其他标签有什么区别，大家如果有需要，可以自行研究。<isNotEmpty>标签的属性看一眼也很清晰，prepend是指在接下来的语句之前要加这个词，property是入参。因为是动态的，所以当入参为空的时候，prepend的内容会自动省略，所以入参为空时，在这个<isNotEmpty>中的所有内容都不会添加到语句中，包括prepend。
有多个条件就添加多个<isNotEmpty>就可以了。接下来的SQL语句就正常写，需要赋值为入参的地方用“#入参名#”。这里除了#还可以用$，我没有具体研究过，区别似乎是二者对于入参的数据类型要求不一样，并且$的安全性较低。感兴趣可以深入研究一下。
所以上面这段程序拼接之后就是：
SELECT * FROM UNITCOMP WHERE DATETIME >= TO_DATE( #DATEFROM#,'YYYY-MM-DD HH24:MI:SS' ) AND DATETIME <= TO_DATE( #DATETO#,'YYYY-MM-DD HH24:MI:SS' )
AND IGROUPID = #IGROUPID# AND CONDITION1 LIKE '%#CONDITION1#%' AND CONDITION2 LIKE '%#CONDITION2#%' AND CONDITION3 LIKE '%#CONDITION3#%'
那么接下来就是我遇到的问题了。在上面的查询语句中，我们需要IGROUPID传进来一个固定的值。但是如果遇到IGROUPID可以取多个值的话，该怎么办呢？也就是下面这段SQL：
SELECT * FROM UNITCOMP WHERE DATETIME >= TO_DATE( #DATEFROM#,'YYYY-MM-DD HH24:MI:SS' ) AND DATETIME <= TO_DATE( #DATETO#,'YYYY-MM-DD HH24:MI:SS' ) AND IGROUPID IN ( #IGROUPID1#, #IGROUPID2# ) AND CONDITION1 LIKE '%#CONDITION1#%' AND CONDITION2 LIKE '%#CONDITION2#%' AND CONDITION3 LIKE '%#CONDITION3#%'
重点看IGROUPID这一段，我们能够想到的解决办法可能有这么几种：
1、IGROUPID = IGROUPID1 OR IGROUPID = IGROUPID3 OR IGROUPID = IGROUPID3 ...
2、IGROUPID IN ( IGROUPID1, IGROUPID2, ... )
3、IGROUPID IN ( IGROUPIDS )。其中 IGROUPIDS为IGROUPID1, IGROUPID2, ... 的集合
通过比较，当然是第三种方式最优。既然是动态语句，我们就希望它的扩展性能达到最佳，在前台只需要定义了IGROUPS集合，不管集合中有几个元素，我们都可以一次性传进来。
但在解决问题的过程中，以上几种方式我都尝试过，都没有很好地成功。实际操作的时候会出现一些问题，不如想象中那么清晰明了，比如第一种，虽然传参数简单了，但是这些OR语句拼接好以后外面还需要加一对括号，因为这个条件是在复合条件中，它还需要考虑到与语句其他部分的衔接。而动态添加括号又会很麻烦。
在查找资料的过程中，我发现了<iterate>这个标签。通过不断迭代，就可以实现我们所需要的功能。问题在于，这种方法的格式我不太清楚是怎样的，包括入参应该以什么样的形式传入。过程就不废话了，通过看开头提到的文章，我最终还是解决了这个问题，下面先直接给出结果：
1 <select id="SelectEnergyCost2" parameterClass="Hashtable" resultClass="EnergyCostData"> 2 <![CDATA[ 3 SELECT * FROM UNITCOMP 4 ]]> 5 <dynamic prepend="WHERE"> 6 <isParameterPresent> 7 <isNotEmpty prepend="AND" property="DATEFROM" > 8 <![CDATA[ 9 DATETIME >= TO_DATE( #DATEFROM#,'YYYY-MM-DD HH24:MI:SS') 10 ]]> 11 </isNotEmpty> 12 <isNotEmpty prepend="AND" property="DATETO" > 13 <![CDATA[ 14 DATETIME <= TO_DATE( #DATETO#,'YYYY-MM-DD HH24:MI:SS') 15 ]]> 16 </isNotEmpty> 17 <isNotEmpty prepend="AND" property="GROUPIDS"> 18 IGROUPID IN 19 <iterate open="(" close=")" conjunction="," property="GROUPIDS" > 20 $GROUPIDS[]$ 21 </iterate > 22 </isNotEmpty> 23 <isNotEmpty prepend="AND" property="CONDITION1" > 24 CONDITION1 LIKE '%' || #CONDITION1# || '%' 25 </isNotEmpty > 26 <isNotEmpty prepend="AND" property="CONDITION2" > 27 CONDITION2 LIKE '%' || #CONDITION2# || '%' 28 </isNotEmpty > 29 <isNotEmpty prepend="AND" property="CONDITION3" > 30 CONDITION3 LIKE '%' || #CONDITION3# || '%' 31 </isNotEmpty > 32 </isParameterPresent> 33 </dynamic> 34 ORDER BY DATETIME DESC 35 </select>
重点看IGROUPID这一部分。<iterate>就相当于一个循环语句，首先要定义循环部分开头和结尾，用open和close属性分别定义为“(”和“)”，连接符用conjunction属性定义为“,”，最后入参是GROUPIDS。语句中同样还是用#GROUPIDS#来接收入参。
那么这里的入参究竟是什么呢？我们自然能想到它要么是一个数组，要么是一个集合。在 ibatis 的规定里，入参必须是array或List<>，也就是数组。所以在语句中，接收入参的部分要是一个能够接收数组的形式。在这里我尝试了很久，最后发现，不能直接用GROUPIDS，而是要写GROUPIDS[]。这样就会在每一个迭代循环中，分别只用数组的一个元素来赋值。至于为什么用$而非#，我没有尝试，大家可以自己试一下#有没有问题。
xml这部分这样就解决了。前台的代码相对也很简单，只要定义一个数组放进Hashtable就可以了。我采取的是以下的形式（C#），大家可以当做参考。
1 Hashtable ht = new Hashtable(); 2 int iGroupID = int.Parse(this.ddlcGroup.SelectedValue); 3 List<int> ilGroupIDs = new List<int>(); 4 switch (iGroupID) 5 { 6 case 1 : 7 { 8 ilGroupIDs.Add(10001); 9 ilGroupIDs.Add(10002); 10 break; 11 } 12 case 2 : 13 { 14 ilGroupIDs.Add(10003); 15 ilGroupIDs.Add(10004); 16 break; 17 } 18 case 3 : 19 { 20 ilGroupIDs.Add(10005); 21 ilGroupIDs.Add(10006); 22 ilGroupIDs.Add(10007); 23 break; 24 } 25 default : 26 break; 27 } 28 ht.Add("GROUPIDS", ilGroupIDs.ToArray()); 29 ht.Add("DATEFROM", this.txtDateFrom.Text); 30 ht.Add("DATETO", this.txtDateTo.Text);
其中第28行，把数组放进Hashtable的时候，因为ilGroupIDs本身就是一个数组了，所以可能不需要.ToArray()方法，但是我没有尝试。感兴趣的朋友可以自己深入探讨这其中的一些细节。
通过上面的示例，就可以在 ibatis 中完成复核条件的查询。
