首页 > 代码库 > MyBatis动态Sql语句

MyBatis动态Sql语句

林炳文Evankaka原创作品。

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

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

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

  • if
  • 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的标签,他能够实践whereset的效果。

	<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>



MyBatis动态Sql语句