ibatis 中动态SQL查询和动态标签嵌套的使用

本文链接：https://blog.csdn.net/u014475796/article/details/51354608

ibatis 动态查询

对于从事 Java EE 的开发人员来说，iBatis 是一个再熟悉不过的持久层框架了，在 Hibernate、JPA 这样的一站式对象 / 关系映射（O/R Mapping）解决方案盛行之前，iBaits 基本是持久层框架的不二选择。即使在持久层框架层出不穷的今天，iBatis 凭借着易学易用、轻巧灵活等特点，也仍然拥有一席之地。尤其对于擅长 SQL 的开发人员来说，iBatis 对 SQL 和存储过程的直接支持能够让他们在获得 iBatis 封装优势的同时而不丧失 SQL 调优的手段，这是 Hibernate/JPA 所无法比拟的。若要了解、学习ibatis，请看 iBatis开发环境搭建和示例。

在项目开发的过程中，肯定会遇到需要根据需求动态组装sql 语句的时候，这时，ibatis的动态查询功能应运而生。

使用动态查询是iBatis一个非常强大的功能。有时你已经改变WHERE子句条件的基础上你的参数对象的状态。在这种情况下的iBATIS提供了一组可以映射语句中使用，以提高SQL语句的重用性和灵活性的动态SQL标签。动态标签的作用是动态构建SQL语句，根据不同的一元或二元运算条件构建复杂的SQL语句，这功能非常好，这样就可以把写的BO层的SQL语句构造移值到SQL MAP 文件里。例如：

select id="findUser" resultClass="User">
   SELECT * User
   <dynamic prepend="WHERE ">
      <isNull property="id">
         id IS NULL
      </isNull>
      <isNotNull property="id">
         id = #id#
      </isNotNull>
   </dynamic>
</select>

ibatis的动态标签分为一元条件元素标签和二元条件元素标签：

一元条件元素

<isPropertyAvailable>	检查是否存在该属性（存在parameter bean的属性）。
<isNotPropertyAvailable>	检查是否不存在该属性（不存在parameter bean的属性）。
<isNull>	检查属性是否为null。
<isNotNull>	检查属性是否不为null。
<isEmpty>	检查Collection.size()的值，属性的String或String.valueOf()值,是否为null或空（“”或size() < 1）。
<isNotEmpty>	检查Collection.size()的值，属性的String或String.valueOf()值,是否不为null或不为空（“”或size() > 0）。例子： <isNotEmpty prepend=”AND” property=”firstName” > FIRST_NAME=#firstName# </isNotEmpty>

二元条件元素

<isEqual>	比较属性值和静态值或另一个属性值是否相等。
<isNotEqual>	比较属性值和静态值或另一个属性值是否不相等。
<isGreaterThan>	比较属性值是否大于静态值或另一个属性值。
<isGreaterEqual>	比较属性值是否大于等于静态值或另一个属性值。
<isLessThan>	比较属性值是否小于静态值或另一个属性值。
<isLessEqual>	比较属性值是否小于等于静态值或另一个属性值。例子： <isLessEqual prepend=”AND” property=”age” compareValue=”18”> ADOLESCENT = ‘TRUE’ </isLessEqual>

prepend －可被覆盖的SQL语句组成部分，添加在语句的前面（可选）

property －被比较的属性（必选）
compareProperty －另一个用于和前者比较的属性（必选或选择compareValue）
compareValue －用于比较的值（必选或选择compareProperty）

还有两个其它的条件元素，<isParameterPresent>，<iterate>就不说了，用到的时候可以再查。

ibatis 动态标签的嵌套

说到动态标签，那就不得不说一下动态标签的嵌套查询了。在我进行项目开发的时候，由于存在一个查询报表的功能，该功能涉及到的数据库表特别多，所以查询条件非常复杂，这时候不得不要到动态标签的嵌套查询了。例如：

<dynamic>
		<isEqual property="reimburseType" compareValue="1">
		left join
		(
			select ReimbursementRevisionId
			from f_reimbursement_type_travel 
			where 1=1			
			<isNotNull prepend="and" property="BusinessPersonName">
					BusinessPersonName = #applicationType#
			</isNotNull>
			<isNotNull prepend="and" property="Position">
					Position = #Position#
			</isNotNull>
		)as rtt
		on rr.ReimbursementRevisionId = rtt.ReimbursementRevisionId 
		</isEqual>
	</dynamic>

理论上来说，动态标签是可以任意嵌套的，比如<isEqual>里面可以嵌套<isEqual>;但是嵌套也有一些要求，比如，<dynamic>标签里面就不能嵌套<dynamic>标签,另外我还发现<isGreaterEqual>里嵌套<isLessEqual>不能实现预期的功能，比如我想在detailType属性在3-6,8-10时，执行查询语句，代码如下：

               <isGreaterEqual property="detailType" compareValue="3"> 
			<isLessEqual property="detailType" compareValue="10">
				<isNotEqual property="detailType" compareValue="7">
       			join 
       			(
       				select ProjectRevisionId,sum(DetailAmount) as DetailTotalAmount      						
					from f_reimbursement_detail
					group by ProjectRevisionId
					where DetailTypeId = #detailType#
					having 1=1
					<isNotEmpty prepend="and" property="detailAmountMin">
						DetailTotalAmount <![CDATA[>=]]> #detailAmountMin#
					</isNotEmpty>
					<isNotEmpty prepend="and" property="detailAmountMax">
						DetailTotalAmount <![CDATA[<=]]> #detailAmountMax#
					</isNotEmpty>
				) as rf on rf.ProjectRevisionId = rr.ProjectRevisionId
				</isNotEqual>
			</isLessEqual>
		</isGreaterEqual>

如果传参detailType=5,运行过后，发现程序在执行上面代码时sql语句中并没有以上的语句，但是如果只是下面这样写，则一点问题也没有，这让我很郁闷。

                <isGreaterEqual property="detailType" compareValue="3"> 
       			join 
       			(
       				select ProjectRevisionId,sum(DetailAmount) as DetailTotalAmount      						
					from f_reimbursement_detail
					group by ProjectRevisionId
					where DetailTypeId = #detailType#
					having 1=1
					<isNotEmpty prepend="and" property="detailAmountMin">
						DetailTotalAmount <![CDATA[>=]]> #detailAmountMin#
					</isNotEmpty>
					<isNotEmpty prepend="and" property="detailAmountMax">
						DetailTotalAmount <![CDATA[<=]]> #detailAmountMax#
					</isNotEmpty>
				) as rf on rf.ProjectRevisionId = rr.ProjectRevisionId
		</isGreaterEqual-->

ibatis使用过程中应该知道的知识：

1.占位符

#是占位符，$是字符串拼接。id = #id# 生成的sql语句是 id = ？使用的是PreparedStatement，执行时，通过setXXX方法，将值加入到sql语句中。

若字段id为String类型，传递的参数id为1，
则 id = #id# 实际SQL为：select * from user where a = ‘1’;
而 id = $id$ 实际SQL为：select * from user where a = 1;
注：尽量用#id# 而不要用$id$ ，因为后面这种只是拼接，很容易被sql注入攻击。

2.动态生成查询的结果列：

有时会出现不能动态生成查询结果列的情况，那是因为ibaits 会缓存查询的meta信息，所以在生成动态列时一定要加上 remapResults="true"。

<select id="getxx" resultClass="java.util.HashMap" parameterClass="map" remapResults="true">

3.动态指定表名

有时会碰到2张表结构相同，但是表名不一样的情况，这时候查询语句中的表名就需要动态指定了。这时只能用$tableName$,而不能用#tableName#。

select  * from $tableName$ where id = #id#

4.动态标签里面的属性

1）dynamic的prepend只要检测到第一个条件为“真”的比较元素，则覆盖其prepend属性并组装where关键字为动态SQL的一部分。若果dynamic下面第一条动态标签不含prepend属性，则会覆盖第二个条件为“真”的动态标签的prepend属性值，所有最好每个动态标签都加上prepend属性。
2）isNotNull的prepend='and' 只要检测到参数值满足比较条件，则前置组装and关键字为动态SQL的一部分。

5.动态sql片段

//动态sql片段
	<sql id="sql_count">
                select count(*)
        </sql>
        <sql id="sql_select">
                select *
        </sql>
        <sql id="sql_where">
                from student
                <dynamic prepend="where">
                        <isNotEmpty prepend="and" property="name">
                                name like '%$name$%'
                        </isNotEmpty>
                        <isNotEmpty prepend="and" property="no">
                                no like '%no$%'
                        </isNotEmpty> 
                </dynamic>               
        </sql>
        <select id="findStudentCount" parameterClass="map" resultClass="int">
                <include refid="sql_count"/>
                <include refid="sql_where"/>
        </select>
        <select id="findStudent" parameterClass="map" resultMap="student.result_base">
                <include refid="sql_select"/>
                <include refid="sql_where"/>
        </select>