mybatis(四) 动态sql

4、动态sql

动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

4.1、if和where

• 当需要对传入参数进行判断从而进行可选搜索，可以使用if搭配where进行使用

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.szm.mapper.UserMapper">

    <resultMap id="userMap" type="user">
        <id property="userId"  column="id" />
        <result property="userName" column="name"/>
        <result property="userPwd" column="pwd" />
    </resultMap>

    <select id="selectUser" resultMap="userMap">
        select * from user
        <where>
            <if test="name != null">
                name like "%"#{name}"%"
            </if>
            <if test = "pwd != null">
              and   pwd like "%"#{pwd}"%"
            </if>
        </where>
    </select>  
</mapper>

• 其中如果使用了where标签，mybatis在运行期间会进行动态判读，去除其中多余的and。

4.2、choose、when、otherwise

• choose、when、otherwise类似于Java中的switch，when类似于case，otherwise类似于defalut ，例如：

<select id="selectUserChoose" resultMap="userMap">
        select * from user
        <where>
            <choose>
                <when test='flag =="all"'>
                    1=1
                </when>
                <when test='flag !=null'>
                    and name like "%"#{flag}"%"
                </when>
                <otherwise>
                    and  1=1
                </otherwise>
            </choose>
        </where>
</select>

4.3、trim、set

4.3.1、trim 标签

• trim属性列表：

属性	描述
prefix	在当前标签包裹的sql的最前面拼接的此属性配置的属性值
suffix	在当前标签包裹的sql的最后面拼接的此属性配置的属性值
prefixOverrides	根据配置的属性值，在前标签包裹的sql的最前面的若匹配到此属性值字符串，会将此字符串删除
suffixOverrides	根据配置的属性值，在前标签包裹的sql的最后面的若匹配到此属性值字符串，会将此字符串删除

使用示例：

<select id="selectUserTrim" resultMap="userMap">
        select * from user
        <trim prefix="where" prefixOverrides="and">
            <if test='name != null'>
               and name like "%"#{name}"%"
            </if>
            <if test='pwd != null'>
               and pwd like "%"#{pwd}"%"
            </if>
        </trim>
    </select>

• 需要注意的是，trim标签是要在当前包裹的sql段不为空的情况下才会生效

4.3.2、set标签

• 用于动态更新语句的类似解决方案叫做 set ，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号

使用示例：

<update id="updateUserSet">
        <if test="id!=null">
            update user
            <set>
                <if test='userName!=null'>name = #{userName},</if>
                <if test='userPwd!=null'>pwd = #{userPwd}</if>
            </set>
            <where>
                id = #{id}
            </where>
        </if>
    </update>

4.4、foreach

• 动态 SQL 的另一个常见使用场景是对集合进行遍历。它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符。这个元素也不会错误地添加多余的分隔符，看它多智能！

你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象作为集合参数传递给 foreach。当使用可迭代对象或者数组时，index 是当前迭代的序号，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

• forEach标签属性表

属性	作用
collection	可迭代对象或者数组的对象名称
item	当使用可迭代对象或者数组时，item 的值是本次迭代获取到的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，item 是值（value）。
separator	循环时，每个循环中间插入的字符
open	循环开始时插入的内容
close	整个循环全部结束时插入的内容
index	当使用可迭代对象或者数组时，index 是当前迭代的序号。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键（key）。

• 传入一个数组，例如：

 List<User> selectUserForEach(@Param("ids")int[] ids);

<select id="selectUserForEach" resultMap="userMap">
        select * from user where
        id in
        <foreach collection="ids" item="id" separator="," open="(" close=")" index="index">
           #{id}
        </foreach>
</select>

• 传入一个map对象时：

 List<User> selectUserMap(@Param("userMap") Map<String, String> userMap);

<select id="selectUserMap" resultMap="userMap">
       select * from user
       <where>
           <foreach collection="userMap" item="value" index="key" separator="and">
               ${key} =  #{value}
           </foreach>
       </where>
</select>

		@Test
    public void test7() throws IOException {
        String resource = "mybatis.xml";
        InputStream inputStream = Resources.getResourceAsStream(resource);
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        SqlSession sqlSession = sqlSessionFactory.openSession(true);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);
        Map<String,String> queryMap =new HashMap<String,String>();
        queryMap.put("name","user1");
        queryMap.put("pwd","pwd1");
        List<User> users = mapper.selectUserMap(queryMap);
        users.stream().forEach((e)->{
            System.out.println(e);
        });
        sqlSession.close();
    }

4.5、bind

bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文。比如：

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

4.6 、script

• 要在带注解的映射器接口类中使用动态 SQL，可以使用 script 元素

@Update({"<script>",
      "update user",
      "  <set>",
      "    <if test='userName != null'>name=#{userName},</if>",
      "    <if test='userPwd != null'>pwd=#{userPwd}</if>",
      "  </set>",
      "where id=#{id}",
      "</script>"})
   void updateUser(User user);