MyBatis动态Sql语句

林炳文Evankaka原创作品。

转载请注明出处http://blog.csdn.net/evankaka

MyBatis中对数据库的操作。有时要带一些条件。因此动态SQL语句很有必要。以下就主要来讲讲几个经常使用的动态SQL语句的语法

MyBatis中用于实现动态SQL的元素主要有：

choose（when。otherwise）

trim

where

set

foreach

1、if

对属性进行推断。假设不为空则运行推断条件

	<select id="selectByCriteria" parameterType="com.mucfc.dto.Student" resultMap="BaseResultMap">
		select * from t_student 
		where
		<if test="stuId != null and stuId !=''">
		STU_ID = #{stuId}
		</if>
		<if test="stuName != null and stuName !=''" >
		and STU_NAME = #{stuName}
		</if>
		<if test="stuClass != null and stuClass !=''">
		and STU_CLASS = #{stuClass}
		</if>
		<if test="stuSex != null and stuSex !=''">
		and STU_SEX=#{stuSex}
		</if>
		<if test="stuAge != null and stuAge !=''">
		and STU_AGE=#{stuAge}
		</if>		
	</select>

来看看结果：

这是从web页面输入的參数

这是输出的结果

这是打印出来的Sql语句

从结果能够看出。仅仅有在条件不为空的时候，属性才会赋值。

2、where

当where中的条件使用的if标签较多时。这种组合可能会导致错误。

我们以在1中的查询语句为样例，当输入參数stuId为空时。就会报错

然后是输出的结果：

此时SQL语句变成了select * from t_student where and STU_SEX=?

这样会报错

假设上面样例，參数stuId为null，将不会进行STUDENT_NAME列的推断，则会直接导“WHERE AND”keyword多余的错误SQL。这时我们能够使用where动态语句来解决。

这个“where”标签会知道假设它包括的标签中有返回值的话，它就插入一个‘where’。

此外，假设标签返回的内容是以AND 或OR 开头的，则它会剔除掉。

能够改成例如以下：

	<select id="selectByCriteria" parameterType="com.mucfc.dto.Student" resultMap="BaseResultMap">
		select * from t_student 
		<where>
		<if test="stuId != null and stuId !=''">
		STU_ID = #{stuId}
		</if>
		<if test="stuName != null and stuName !=''" >
		and STU_NAME = #{stuName}
		</if>
		<if test="stuClass != null and stuClass !=''">
		and STU_CLASS = #{stuClass}
		</if>
		<if test="stuSex != null and stuSex !=''">
		and STU_SEX=#{stuSex}
		</if>
		<if test="stuAge != null and stuAge !=''">
		and STU_AGE=#{stuAge}
		</if>
		</where>		
	</select>

再来看看,输入查询条件

然后输出结果

打印出来的SQL语句

- ==> Preparing: select * from t_student WHERE STU_SEX=?
==> Parameters: 男(String)
<== Total: 14

说明结果是正确的。假设它包括的标签中有返回值的话就插入一个where。此外假设标签返回的内容是以AND或OR开头的，则它会剔除掉。

3、set

当update语句中没有使用if标签时。假设有一个參数为null，都会导致错误。
当在update语句中使用if标签时，假设前面的if没有运行。则或导致逗号多余错误。使用set标签能够将动态的配置SET keyword，和剔除追加到条件末尾的不论什么不相关的逗号。

使用if+set标签改动后，假设某项为null则不进行更新。而是保持数据库原值。例如以下演示样例：

4、trim

trim是更灵活的去处多余keyword的标签，他能够实践where和set的效果。

	<select id="selectByCriteria" parameterType="com.mucfc.dto.Student" resultMap="BaseResultMap">
		select * from t_student 
		<trim prefix="where" prefixOverrides="AND|OR">  
		<if test="stuId != null and stuId !=''">
		STU_ID = #{stuId}
		</if>
		<if test="stuName != null and stuName !=''" >
		and STU_NAME = #{stuName}
		</if>
		<if test="stuClass != null and stuClass !=''">
		and STU_CLASS = #{stuClass}
		</if>
		<if test="stuSex != null and stuSex !=''">
		and STU_SEX=#{stuSex}
		</if>
		<if test="stuAge != null and stuAge !=''">
		and STU_AGE=#{stuAge}
		</if>
		</trim>	
	</select>

首先推断是否须要where,假设须要，它会自己主动推断假设where后面有and或者or,就自己主动把它们都去掉。

prefix是前置的，还有suffix是后置的。

例如以下输入查找參数stuclass=2。

看看打印出来的语句

suffix是后置的样例

  <insert id="insert" parameterType="com.mucfc.dto.Student" >
    insert into t_student 
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="stuId != null" >
        STU_ID,
      </if>
      <if test="stu_name!= null" >
        STU_NAME,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="stuId  != null" >
        #{stuId},
      </if>
      <if test="stu_name != null" >
        #{stu_name},
      </if>
 </insert

这里是自己主动推断是否为空。然后去掉逗号

prefix="(" suffix=")"

会自己主动推断是否须要加上（）这个符号

5、choose

有时候我们并不想应用全部的条件，而仅仅是想从多个选项中选择一个。而使用if标签时。仅仅要test中的表达式为true，就会运行if标签中的条件。

MyBatis提供了choose 元素。if标签是与(and)的关系，而choose比傲天是或（or）的关系。
choose标签是按顺序推断其内部when标签中的test条件出否成立。假设有一个成立。则choose结束。

当choose中全部when的条件都不满则时。则运行otherwise中的sql。

相似于Java 的switch 语句，choose为switch，when为case。otherwise则为default。

	<!-- 满足当中一个条件时候用choose when操作 -->
	<select id="selectByCriteria" parameterType="com.mucfc.dto.Student" resultMap="BaseResultMap">
		select * from t_student
        <where>
            <choose>
                <when test="stuId != null and stuId !=''">
                   STU_ID = #{stuId}
                </when>                               
                <when test="stuClass != null and stuClass !=''">
                 	and STU_CLASS = #{stuClass}
                </when>
                <otherwise>               
                </otherwise>
            </choose>
        </where>
    </select>

输入两个參数

这里会跳过王五这个參数，由于stuId不为空

然后看打印了来的语句。果然仅仅有一个条件，所以说明是对的

foreach

对于动态SQL 很必须的。主是要迭代一个集合。一般是用于IN 条件。List 实例将使用“list”做为键，数组实例以“array” 做为键。

foreach元素是很强大的，它同意你指定一个集合，声明集合项和索引变量，它们能够用在元素体内。

它也同意你指定开放和关闭的字符串。在迭代之间放置分隔符。这个元素是很智能的，它不会偶然地附加多余的分隔符。
注意：你能够传递一个List实例或者数组作为參数对象传给MyBatis。当你这么做的时候，MyBatis会自己主动将它包装在一个Map中。用名称在作为键。

List实例将会以“list”作为键。而数组实例将会以“array”作为键。

foreach的主要用在构建in条件中，它能够在SQL语句中进行迭代一个集合。

foreach元素的属性主要有item，index。collection，open，separator。close。item表示集合中每个元素进行迭代时的别名，index指定一个名字，用于表示在迭代过程中。每次迭代到的位置，open表示该语句以什么開始。separator表示在每次进行迭代之间以什么符号作为分隔符，close表示以什么结束。在使用foreach的时候最关键的也是最easy出错的就是collection属性。该属性是必须指定的，可是在不同情况下。该属性的值是不一样的。主要有一下3种情况：

假设传入的是单參数且參数类型是一个List的时候。collection属性值为list
假设传入的是单參数且參数类型是一个array数组的时候，collection的属性值为array
假设传入的參数是多个的时候，我们就须要把它们封装成一个Map了，当然单參数也能够封装成map。实际上假设你在传入參数的时候，在MyBatis里面也是会把它封装成一个Map的，map的key就是參数名，所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key

以下分别来看看上述三种情况的演示样例代码：
1.单參数List的类型：

<select id="dynamicForeachTest" resultType="Blog">
select * from t_blog where id in
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>

上述collection的值为list，相应的Mapper是这种

public List<Blog> dynamicForeachTest(List<Integer> ids);

測试代码：

@Test
public void dynamicForeachTest() {
SqlSession session = Util.getSqlSessionFactory().openSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(3);
ids.add(6);
List<Blog> blogs = blogMapper.dynamicForeachTest(ids);
for (Blog blog : blogs)
System.out.println(blog);
session.close();
}

2.单參数array数组的类型：

<select id="dynamicForeach2Test" resultType="Blog">
select * from t_blog where id in
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>

上述collection为array，相应的Mapper代码：

public List<Blog> dynamicForeach2Test(int[] ids);

相应的測试代码：

@Test
public void dynamicForeach2Test() {
SqlSession session = Util.getSqlSessionFactory().openSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
int[] ids = new int[] {1,3,6,9};
List<Blog> blogs = blogMapper.dynamicForeach2Test(ids);
for (Blog blog : blogs)
System.out.println(blog);
session.close();
}

3.自己把參数封装成Map的类型

<select id="dynamicForeach3Test" resultType="Blog">
select * from t_blog where title like "%"#{title}"%" and id in
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
#{item}
</foreach>
</select>

上述collection的值为ids，是传入的參数Map的key。相应的Mapper代码：

public List<Blog> dynamicForeach3Test(Map<String, Object> params);

相应測试代码：

@Test
public void dynamicForeach3Test() {
SqlSession session = Util.getSqlSessionFactory().openSession();
BlogMapper blogMapper = session.getMapper(BlogMapper.class);
final List<Integer> ids = new ArrayList<Integer>();
ids.add(1);
ids.add(2);
ids.add(3);
ids.add(6);
ids.add(7);
ids.add(9);
Map<String, Object> params = new HashMap<String, Object>();
params.put("ids", ids);
params.put("title", "中国");
List<Blog> blogs = blogMapper.dynamicForeach3Test(params);
for (Blog blog : blogs)
System.out.println(blog);
session.close();
}

6、联合实例：

  <insert id="batchInsertTrxBillDetailInfo" parameterType="java.util.Map" >
	INSERT ALL
	<foreach collection="list" item="item">
		into TRX_BILL_DETAIL_INFO
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="item.id != null" >
        ID,
      </if>
      <if test="item.custNo != null" >
        CUST_NO,
      </if>
      <if test="item.transCode != null" >
        TRANS_CODE,
      </if>
      <if test="item.transRefno != null" >
        TRANS_REFNO,
      </if>
      <if test="item.transSeqno != null" >
        TRANS_SEQNO,
      </if>
      <if test="item.orderNo != null" >
        ORDER_NO,
      </if>
      <if test="item.transAmt != null" >
        TRANS_AMT,
      </if>
      <if test="item.billDate != null" >
        BILL_DATE,
      </if>
      <if test="item.billFlag != null" >
        BILL_FLAG,
      </if>
      <if test="item.transDesc != null" >
        TRANS_DESC,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="item.id != null" >
        #{item.id,jdbcType=DECIMAL},
      </if>
      <if test="item.custNo != null" >
        #{item.custNo,jdbcType=CHAR},
      </if>
      <if test="item.transCode != null" >
        #{item.transCode,jdbcType=CHAR},
      </if>
      <if test="item.transRefno != null" >
        #{item.transRefno,jdbcType=CHAR},
      </if>
      <if test="item.transSeqno != null" >
        #{item.transSeqno,jdbcType=VARCHAR},
      </if>
      <if test="orderNo != null" >
        #{item.orderNo,jdbcType=CHAR},
      </if>
      <if test="item.transAmt != null" >
        #{item.transAmt,jdbcType=DECIMAL},
      </if>
      <if test="item.billDate != null" >
        #{item.billDate,jdbcType=DECIMAL},
      </if>
      <if test="item.billFlag != null" >
        #{item.billFlag,jdbcType=CHAR},
      </if>
      <if test="item.transDesc != null" >
        #{item.transDesc,jdbcType=VARCHAR},
      </if>
    </trim>
     </foreach>
   select 1 from dual 
  </insert>