mybatis 动态sql

很多时候需要根据传入的条件去进行sql的拼接,mybatis提供了几个基本的元素,通过这几个标签在xml文件中就可以实现SQL的动态拼接。
最常使用的几个元素定义如下:

语句	作用	备注
if	判断语句	单条件分支判断
choose(when otherwise)	相当于Java中的case switch	多条件分支判断
trim(where set)	辅助元素	用来处理一些sql拼接问题
foreach	循环语句	在in语句等列举条件常用

1.if标签
if标签是我们常用的判断语句,相当于Java的if语句,常和test属性联合使用。常见的一个应用场景是多个可选条件的查询,比如说branch表中有name和address两个属性,现在用户可以通过输入name或address部分关键字进行查询,但是可能都输入或只输入一个或都不输入,这种时候就可以通过if语句来根据输入的字符串是否为空来拼接处合适的sql。示例如下：

<select id="getBranchByNameAndCity"  resultType="branch">
        select * from branch where 1=1
        <if test="city!=null and city!=''">
            and city like concat('%',#{city},'%')
        </if>
        <if test="name!=null and name!=''">
            and name like concat('%',#{city},'%')
        </if>
 </select>

对应的mapper接口:

 public List<Branch> getBranchByNameAndCity(@Param("name") String name, @Param("city") String city);

这里需要注意的是test中的字符串判空语法是类似于java的,并且其中引用传入的参数不需要加#。最前面的where 1=1为了兼容后面的and避免出现
语法错误。

2.choose when otherwise标签
上面的if语句针对条件成立或不成立进行处理,但有很多时候是需要根据条件的不同而进行不同的处理,类似于java中的switch case default的语法,mybatis的choose when otherwise就是用来实现这样多条件选择的。基本语法如下:

<choose>
 <when test="">
 </when>
 <when test="">
 </when>
 <otherwise>
 </otherwise>
</choose>

如果其中一个when语句条件满足,则执行其中的逻辑并退出<choose>语句,如果所有<when>中的条件都不满足,则执行<otherwise>中的逻辑继续上面的例子,现在假定如果传入name就按name进行匹配,否则就按city就行匹配,如果两者都为空,则将address不为null的记录查询出来。针对这个场景,如果还是使用if那么实现起来就很困难,但是如果使用choose when otherwise实现起来就比较简单:

<select id="getBranchByNameAndCity"  resultType="branch">
        select * from branch where
        <choose>
            <when test="name!=null and name!=''">
                name like concat('%',#{name},'%')
            </when>
            <when test="city!=null and city!=''">
                city like concat('%',#{city},'%')
            </when>
            <otherwise>
                address is not null
            </otherwise>
        </choose>
 </select>

3.trim where set标签
在上面为了避免语法错误加入了1=1这样的条件,在一条sql里加入这样的判断条件未免让人觉得奇怪,我们可以用where标签对sql进行处理达到不需要这种额外判断条件的目的。
where标签的基本语法:

<where>
 <if test>
 </if>
</where>

如果test条件为真,则在sql中加入where关键字,否则就不添加。比如

<select id="getBranchByNameAndCity"  resultType="branch">
       select * from branch
       <where>
           <if test="name!=null and name!=''">
               name like concat('%',#{name},'%')
           </if>
       </where>
 </select>

如果传入的name参数不为空,则sql是select * from branch WHERE name like concat('%',name,'%')。
如果传入的name为空,则sql是select * from branch。
trim标签也可以实现上面where标签所做的事情,并且功能更为强大。trim元素有四个属性:prefix，prefixOverrides，suffix，suffixOverrides。
prefix表示在trim包裹的语句前面增加一个前缀
suffix表示在trim包裹的预计后面增加一个后缀
prefixOverrides表示覆盖掉(去掉)trim语句中第一个满足条件的字符串
suffixOverrides表示覆盖掉(去掉)trim语句最后满足条件的字符串
注意如果prefixOverrides和suffixOverrides指定字符串出现在trim包裹语句的中间位置,而不是最前面/最后面则不会被去掉。
如果trim标签中的条件不成立,导致trim包裹的语句为空,则上述属性都不会生效。下面是两个具体例子:
首先还是前面的根据用户输入的name或city进行模糊查询。

<select id="getBranchByNameAndCity" resultType="branch">
        select * from branch
        <trim prefix="where" prefixOverrides="and">
            <if test="name!=null and name!=''">
                and name like concat('%',#{name},'%')
            </if>
            <if test="city!=null and city!= ''">
                and city like concat('%',#{name},'%')
            </if>
        </trim>
</select>

这里用prefix添加where前缀 prefixOverrides用来去掉多余的and防止出现语法错误。下面的例子是根据用户的输入来更新name或city,用户可能会同时输入这两个属性,或只会输入其中一个值,需要根据输入动态的构建对应sql。

<select id="updateBranchInfo" resultType="int">
        update branch SET
        <trim suffixOverrides=",">
            <if test="name!=null and name!=''">
                name =#{name},
            </if>
            <if test="city!=null and city!=city">
                city=#{city}
            </if>
        </trim>
        where branch_id=#{id}
</select>

这里处理的主要是输入的name不为空而输入的city为空的情况下去掉最后的逗号。除了trim标签mybatis还有一个set标签,专门用来处理更新的时候多余逗号的情况,set默认会去掉最后一个逗号,并且还会在sql语句中自动添加set关键字.所以上面的sql也可以写成下面这种形式:

<update id="updateBranchInfo" >
        update branch
        <set>
            <if test="name!=null and name!=''">
                name=#{name},
            </if>
            <if test="city!=null and city!=''">
                city=#{city}
            </if>
        </set>
        where branch_id=#{id}
</update>

4.foreach元素
foreach标签是一个循环语句,它的作用是遍历集合,它能很好的支持数组 list set接口的集合,对其提供遍历功能。
下面通过批量插入Branch对象来展示基本的用法：
mapper接口定义如下:

public void batchSave(@Param("branchList") List<Branch> branchList);

对应的映射文件配置如下:

<insert id="batchSave">
        insert into branch
        <include refid="insertKey"/>
        values
        <foreach  item="bc" index="index" collection="branchList" separator=",">
            ( #{bc.name},#{bc.city},#{bc.state},#{bc.zip})
        </foreach>
    </insert>


    <sql id="insertKey">
      (name,city,state,zip)
    </sql>

foreach元素的属性主要有 item，index，collection，open，separator，close。
item 表示集合中每一个元素进行迭代时的别名；
index指 定一个名字，用于表示在迭代过程中，每次迭代到的位置；
open 表示整个语句以什么开始;
separator表示在每次进行迭代之间以什么符号作为分隔 符；
close 表示整个语句以什么结束。
这里需要注意的是collection属性,它是必填属性,并且随着mapper接口的传参形式的不同而填充的参数也不同
1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
3.如果传入的参数是多个或单个参数使用@Param进行注释的时候就需要通过@Param中的名字进行引用。
foreach多用于根据传入的list参数拼接in参数或批量参数拼接values参数的情况。

5.test属性
test属性常用于条件判断语句中,用于进行真假的判断.上面已经在if语句中用来判断过字符串是否为空。其实还可以用来判断字符串是否相等,数字大小关系等。字符串相等用”=”,数字的相等用”==”。

6.bind参数
bind参数用来定义一个变量,可以在上下文中使用。比如前面的模糊查询,就可以不使用concat进行字符串的拼接,而是直接给传入的参数加上”%”号,从而直接用来进行查询。示例如下:

 <select id="getBranchByNameAndCity" resultType="branch">
        <bind name="name_pattern" value="'%'+name+'%'"/>
        <bind name="city_pattern" value="'%'+city+'%'"/>
        select* from branch
        where name like #{name_pattern} and city like #{city_pattern}
    </select>