动态SQL

如果使用JDBC或者其他框架，很多时候你得根据需要去拼装SQL，这是一个麻烦的事情。而Mybatis提供对SQL语句动态的组装能力，大量的判断可以在Mybatis的映射XML文件里面配置，已达到许多我们需要大量代码才能实现的功能。

Mybatis的动态SQL包括以下几种元素：

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

if, where, trim元素

if元素是我们最常用的判断语句，相当于Java中的if语句。它常常与test属性联合使用。

  <!--使用if-->
 <select id="findUserById2" resultType="user">
     select * from user where 1=1
     <if test="userId!=null">
        and user_id=#{userId}
     </if>
     <if test="userName!=null">
       and user_name=#{userName}
     </if>
     <if test="userPwd!=null">
       and user_pwd=#{userPwd}
     </if>
   </select>

接下来我们来测试这个Mapper

    /**
     * 测试动态Sql  if
     */
    @Test
    public void testIf() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        User user = userMapper.findUserById2(new User(3, null, null));
        System.out.println(user);
    }

这里有几点问题需要注意：

1.在XML文件中使用“and”/“or” 而不是“&&”/"||"

<if test="userId!=null and userId!='' ">
   and user_id=#{userId}
</if>

2.我们使用了where 1=1 这样的方式去拼SQL，可能看起来很怪异，如果不想使用这种方式就用where元素吧

 <!-- 使用where -->
  <select id="findUserById2" resultType="user">
         select * from user
         <where>
             <if test="userId!=null">
                and user_id=#{userId}
             </if>
             <if test="userName!=null">
               and user_name=#{userName}
             </if>
             <if test="userPwd!=null">
               and user_pwd=#{userPwd}
             </if>
         </where>
   </select>

where会将遇到的第一个and/or 去掉，以保证SQL语句的正常。如果where元素中条件都不成立，where这个SQL关键字不会加入

3.where元素很方便的处理了拼SQL的条件问题，但是，如果我们将and/or 放到后面，where元素就不能解决问题了

 <!-- 使用where -->
  <select id="findUserById2" resultType="user">
         select * from user
         <where>
             <if test="userId!=null">
                user_id=#{userId} and
             </if>
             <if test="userName!=null">
                user_name=#{userName} and
             </if>
             <if test="userPwd!=null">
                user_pwd=#{userPwd} and
             </if>
         </where>
   </select>

这个时候可以使用trim元素

   <!--使用trim  -->
    <select id="findUserById2" resultType="user">
         select * from user
         <!-- 自定义截取规则  -->
         <trim prefix="where" prefixOverrides="and">
             <if test="userId!=null">
                and user_id=#{userId}
             </if>
             <if test="userName!=null">
               and user_name=#{userName}
             </if>
             <if test="userPwd!=null">
               and user_pwd=#{userPwd}
             </if>
         </trim>
   </select>

trim元素的属性

• prefix="":前缀，trim标签体中是整个字符串拼串后的结果。 prefix给拼串后的整个字符串加一个前缀
• prefixOverrides="":前缀覆盖： 去掉整个字符串前面多余的字符
• suffix="":后缀， suffix给拼串后的整个字符串加一个后缀
• suffixOverrides="" 后缀覆盖：去掉整个字符串后面多余的字符

trim元素很强大，我们可以使用这些属性完成很多操作

set元素

在Hibernate中我们更新某一个对象，需要发送所有的字段给持久化对象。现实中场景往往是，我只想更新某几个字段，如果发送所有的属性去更新一遍，对网络带宽消耗较大，性能较佳的方式是把主键和更新字段的值传递给SQL更新即可。在Mybatis中，我们可以使用set元素完成这个功能（set元素只能用在update语句中，因为只有update语句才有set关键字）。

   <!--set和if实现动态更新  -->
   <update id="updateUser2">
     update user 
     <set>
       <if test="userName!=null">
         user_name=#{userName},
       </if>
       <if test="userPwd!=null">
         user_pwd=#{userPwd},
       </if>
     </set>
     <where>
        user_id=#{userId}
     </where>
   </update>

set元素会见更新条件的最后一个逗号去掉，以保证SQL条件的正确。

同样的我们也可以使用trim元素完成set元素的功能：

<trim prefix="SET" suffixOverrides=",">
...
</trim>

测试代码

    /**
     * 测试动态更新
     */
    @Test
    public void testUpdate2() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        System.out.println(userMapper.updateUser2(new User(2,null,"Lee123")));
    }

choose，when，otherwise元素

在有的时候我们需要的是第三种选择甚至更多的选择，也就说需要类似于Java中的switch...case...default语句，而映射器中的choose，when，otherwise

   <!--  有什么条件就查询什么  switch case-->
   <select id="findList" resultType="user">
     select * from user
     <where>
       <choose>
         <when test="userId!=null">
            user_id=#{userId}
         </when>
         <when test="userName!=null">
            user_name=#{userName}
         </when>
         <when test="userPwd!=null">
            user_pwd=#{userPwd}
         </when>
         <otherwise>
            1=1
         </otherwise>
       </choose>
     </where>
   </select>

这样Mybatis就可以根据参数的设置进行判断来动态组装SQL。

测试代码

    /**
     * 测试动态Sql  choose
     */
    @Test
    public void testChoose() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> list= userMapper.findList(new User(null, "tom3", "tom3"));
        for (User user : list) {
            System.out.println(user);
        }
    }

foreach元素

显然foreach元素是一个循环语句，它的作用是遍历集合。它能够很好的支持数组和List，Set接口的集合，对此提供便利的功能。

   <select id="findList3" resultType="cn.lynu.model.User">
      select * from user 
      <foreach collection="ids" item="id" separator="," open="where user_id in(" close=")">
         #{id}
      </foreach>
   </select>

foreach元素的属性：

• collection：指定要遍历的集合：
• item：将当前遍历出的元素赋值给指定的变量
• separator:每个元素之间的分隔符
• open：遍历出所有结果拼接一个开始的字符
• close:遍历出所有结果拼接一个结束的字符
• index:索引。遍历list的时候是index就是索引，item就是当前值  ，遍历map的时候index表示的就是map的key，item就是map的值

我们使用 #{变量名} 就能取出变量的值也就是当前遍历出的元素

测试代码

    /**
     * foreach in的查询
     */
    @Test
    public void testIn() {
        UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
        List<User> list = userMapper.findList3(Arrays.asList(2,3,6));
        for (User user : list) {
            System.out.println(user);
        }
        
    }

foreach元素常常被使用在 in语句中，对于大量数据的in语句需要我们特别注意，因为它会消耗大量的性能，还有一些数据库对于SQL的长度也有限制。所以我们在使用它的时候需要预估一下这个Collection的长度。

bind元素

bind元素的作用是通过OGNL表达式去自定义一个上下文变量，这样更方便我们使用。例如，在我们进行模糊查询的时候，如果是MySQL，我们可以使用concat关键字将“%”和参数相连接，然而在Oracle中使用的是符号“||”。使用bind元素之后，我们就不必使用数据库的语言，只要使用Mybatis的语言即可与所需参数相连。

<select id="findRole" resultType="cn.lynu.model.Role">
   <bind name="pattern" value="'%' +_parameter+'%'"/>
   SELECT id,role_name roleName create_date createDate,end_date endDate FROM
t_role where role_name like #{pattern}
</select>

这里的_parameter代表的就是传进来的参数(这里使用的是内置参数，关于内置参数我们最后再说)，它和通配符连接之后，赋给了pattern，我们就可以在select语句中使用这个变量进行模糊查询，不论是MySQL还是Oracle都可以使用这样的语句，提高了其可移植性。

在MySQL和Oracle中插入多条记录

因为mysql支持values(),(),()语法，所以我们可以使用foreach元素配合这种语法完成插入多条记录的功能：

    <insert id="addEmps" databaseId="mysql">
         insert into tbl_employee(last_name,email,gender,d_id) 
        values
        <foreach collection="emps" item="emp" separator=",">
            (#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
        </foreach>
     </insert>

多个()之间使用逗号分隔

我们也可以通过使用多条insert语句的方式插入多条记录，但是因为涉及到多条SQL语句的执行，所以需要开启 allowMultiQueries 连接参数为true

<!-- 这种方式需要数据库连接属性allowMultiQueries=true；
         这种分号分隔多个sql可以用于其他的批量操作（删除，修改） -->
    <insert id="addEmps">
         <foreach collection="emps" item="emp" separator=";">
             insert into tbl_employee(last_name,email,gender,d_id)
             values(#{emp.lastName},#{emp.email},#{emp.gender},#{emp.dept.id})
         </foreach>
     </insert> 

在Oracle中则不能使用使用这种方式，同时Oracle也不支持values(),(),()这种方式。我们可以使用foreach元素并将多个insert语句放在begin...end;之间

<insert id="addEmps" databaseId="oracle">
    <foreach collection="emps" item="emp" open="begin" close="end;">
        insert into employees(employee_id,last_name,email) values(employees_seq.nextval,#{emp.lastName},#{emp.email});
     </foreach>
</insert>

SQL片段

我们有时候经常需要将多个字段查询出来，而且这多个字段还经常被使用，所以我们希望可以将这些经常使用的东西提出来，以便我们需要使用的时候直接简单的导入即可。SQL元素这是Mybatis为我们提供解决这个问题的方法。

<sql id="role_columns">
  id,role_name,note
<sql>

<select parameterType="long" id="getRole" resultMap="roleMap">
   select <include refid="role_columns"/> from t_role where id=#{id}
</select>

我们可以很方便的使用include元素的refid属性进行引用，从而达到重用的功能。

我们还可以传递参数来使用它们

<sql id="role_columns">
  ${prefix}.role_no,${prefix}.role_name,${prefix}.note
<sql>

<select parameterType="long" id="getRole" resultMap="roleMap">
   select 
      <include refid="role_columns">
          <property name="prefix" value="r"/> 
      </include> 
   from t_role r where id=#{id}
</select>

include-property：取值的正确方式${prop},  #{不能使用这种方式}

内置参数

在上面我们使用过_parameter这个内置参数，其实Mybatis还有另一个_databaseId内置参数

我们使用”_parameter“代表传入的整个参数，单个参数的情况下，_parameter就是表示这个参数；多个参数情况下，参数会被封装为一个map，_parameter就是代表这个map。

_databaseId:如果配置了databaseIdProvider标签，_databaseId就是代表当前数据库的别名。_databaseId使用如下：

<sql id="insertColumn">
    <if test="_databaseId=='oracle'">
      employee_id,last_name,email
   </if>
   <if test="_databaseId=='mysql'">
      last_name,email,gender,d_id
   </if>
</sql>