ibatis笔记（一）：组合条件查询SQL语句

一时失言乱红尘 2022-03-21 14:28 488阅读 0赞

原文链接：[https://www.cnblogs.com/brandon988/p/6038383.html][https_www.cnblogs.com_brandon988_p_6038383.html]

近来工作中用到了 ibatis 技术，主要用来完成动态条件的查询，深感这种一劳永逸的书写方式确实很genius。不过因为是在使用的过程中照猫画虎，没有深入系统地去研究，所以这几天遇到了一个很棘手的问题。在网上查了很久也没有太清楚解决办法，后来终于找到了一篇和我的问题很类似的介绍，在此要多谢原作者的 [ibatis传入数组或List类型参数小结][ibatis_List]。

之前使用 ibatis 来写SQL查询语句，无非就是 WHERE 后的条件是并联的。也就是说，一些动态的条件用 AND 相连。下面举一个例子：

<select id="SelectEnergyCost" parameterClass="Hashtable" resultClass="EnergyCostData">
        <![CDATA[
        SELECT * FROM UNITCOMP
        ]]>
        <dynamic prepend="WHERE">
            <isParameterPresent>
                <isNotEmpty prepend="AND" property="DATEFROM" >
                    <![CDATA[ 
                    DATETIME >= TO_DATE( #DATEFROM#,'YYYY-MM-DD HH24:MI:SS')
                    ]]>
                </isNotEmpty>
                <isNotEmpty prepend="AND" property="DATETO" >
                    <![CDATA[ 
                    DATETIME <= TO_DATE( #DATETO#,'YYYY-MM-DD HH24:MI:SS')
                    ]]>
                </isNotEmpty>
                <isNotEmpty prepend="AND" property="IGROUPID" >
                    IGROUPID = #IGROUPID#
                </isNotEmpty >
                <isNotEmpty prepend="AND" property="CONDITION1" >
                    CONDITION1 LIKE '%' || #CONDITION1# || '%'
                </isNotEmpty >
                <isNotEmpty prepend="AND" property="CONDITION2" >
                    CONDITION2 LIKE '%' || #CONDITION2# || '%'
                </isNotEmpty >
                <isNotEmpty prepend="AND" property="CONDITION3" >
                    CONDITION3 LIKE '%' || #CONDITION3# || '%'
                </isNotEmpty >
            </isParameterPresent>
        </dynamic>
        ORDER BY DATETIME DESC
    </select>

上面是网上找来的例子，下面是我经过实际调试的例子：

<select id="queryInfoByCompoundConditions" parameterClass="java.util.Map" resultMap="ConfigResult">
    	    SELECT
    	<include refid="Config.AllFields"/>
    	FROM member_config
    	<dynamic prepend="WHERE" >
    		<isParameterPresent>
    			<isNotEmpty prepend="AND" property="startDt" >
    				<![CDATA[
    				start_dt <= #startDt#
                    ]]>
    			</isNotEmpty>
    			<isNotEmpty prepend="AND" property="endDt" >
    				<![CDATA[
    				end_dt >= #endDt#
    				]]>
    			</isNotEmpty>
    			<isNotEmpty prepend="AND" property="benefitId">
    				benefit_id = #benefitId#
    			</isNotEmpty>
    			<isNotEmpty prepend="AND" property="itemId">
    				item_id = #itemId#
    			</isNotEmpty>
    			<isNotEmpty prepend="AND" property="benefitStatus">
    				status = #benefitStatus#
    			</isNotEmpty>
    			<isNotEmpty prepend="AND" property="creator">
    				creator = #creator#
    			</isNotEmpty>
    			<isNotEmpty prepend="AND" property="benefitName">
    				name LIKE concat('%', #benefitName#, '%')
    			</isNotEmpty>
    		</isParameterPresent>
    	</dynamic>
    	ORDER BY start_dt DESC
    </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>这个标签。通过不断迭代，就可以实现我们所需要的功能。问题在于，这种方法的格式我不太清楚是怎样的，包括入参应该以什么样的形式传入。过程就不废话了，通过看开头提到的文章，我最终还是解决了这个问题，下面先直接给出结果：

<select id="SelectEnergyCost2" parameterClass="Hashtable" resultClass="EnergyCostData">
        <![CDATA[
        SELECT * FROM UNITCOMP
        ]]>
        <dynamic prepend="WHERE">
            <isParameterPresent>
                <isNotEmpty prepend="AND" property="DATEFROM" >
                    <![CDATA[ 
                    DATETIME >= TO_DATE( #DATEFROM#,'YYYY-MM-DD HH24:MI:SS')
                    ]]>
                </isNotEmpty>
                <isNotEmpty prepend="AND" property="DATETO" >
                    <![CDATA[ 
                    DATETIME <= TO_DATE( #DATETO#,'YYYY-MM-DD HH24:MI:SS')
                    ]]>
                </isNotEmpty>
                <isNotEmpty prepend="AND" property="GROUPIDS">
                    IGROUPID IN
                    <iterate open="(" close=")" conjunction="," property="GROUPIDS" >
                        $GROUPIDS[]$
                    </iterate >
                </isNotEmpty>
                <isNotEmpty prepend="AND" property="CONDITION1" >
                    CONDITION1 LIKE '%' || #CONDITION1# || '%'
                </isNotEmpty >
                <isNotEmpty prepend="AND" property="CONDITION2" >
                    CONDITION2 LIKE '%' || #CONDITION2# || '%'
                </isNotEmpty >
                <isNotEmpty prepend="AND" property="CONDITION3" >
                    CONDITION3 LIKE '%' || #CONDITION3# || '%'
                </isNotEmpty >
            </isParameterPresent>
        </dynamic>
        ORDER BY DATETIME DESC
    </select>

重点看IGROUPID这一部分。<iterate>就相当于一个循环语句，首先要定义循环部分开头和结尾，用open和close属性分别定义为“(”和“)”，连接符用conjunction属性定义为“,”，最后入参是GROUPIDS。语句中同样还是用\#GROUPIDS\#来接收入参。

那么这里的入参究竟是什么呢？我们自然能想到它要么是一个数组，要么是一个集合。在 ibatis 的规定里，入参必须是array或List<>，也就是数组。所以在语句中，接收入参的部分要是一个能够接收数组的形式。在这里我尝试了很久，最后发现，不能直接用GROUPIDS，而是要写GROUPIDS\[\]。这样就会在每一个迭代循环中，分别只用数组的一个元素来赋值。至于为什么用$而非\#，我没有尝试，大家可以自己试一下\#有没有问题。

xml这部分这样就解决了。前台的代码相对也很简单，只要定义一个数组放进Hashtable就可以了。我采取的是以下的形式（C\#），大家可以当做参考。

Hashtable ht = new Hashtable();
    int iGroupID = int.Parse(this.ddlcGroup.SelectedValue);
    List<int> ilGroupIDs = new List<int>();
    switch (iGroupID)
    {
        case 1 :
            {
                ilGroupIDs.Add(10001);
                ilGroupIDs.Add(10002);
                break;
            }
        case 2 :
            {
                ilGroupIDs.Add(10003);
                ilGroupIDs.Add(10004);
                break;
            }
        case 3 :
            {
                ilGroupIDs.Add(10005);
                ilGroupIDs.Add(10006);
                ilGroupIDs.Add(10007);
                break;
            }
        default :
            break;
        }
        ht.Add("GROUPIDS", ilGroupIDs.ToArray());
        ht.Add("DATEFROM", this.txtDateFrom.Text);
        ht.Add("DATETO", this.txtDateTo.Text);

其中第28行，把数组放进Hashtable的时候，因为ilGroupIDs本身就是一个数组了，所以可能不需要.ToArray()方法，但是我没有尝试。感兴趣的朋友可以自己深入探讨这其中的一些细节。

通过上面的示例，就可以在 ibatis 中完成复核条件的查询。

[https_www.cnblogs.com_brandon988_p_6038383.html]: https://www.cnblogs.com/brandon988/p/6038383.html
[ibatis_List]: http://blog.csdn.net/bruce128/article/details/22098795