MyBatis之【动态sql】

一、什么是动态sql

可以定义代码片段，可以进行逻辑判断，可以进行循环处理（批量处理），使条件判断更为简单。

二、如何实现动态sql

1、<sql>

（1）当多种类型的查询语句的查询字段或者查询条件相同时，可以将其定义为常量，方便调用。

（2）示例如下

    <sql id="allColumns">
        id,username,birthday,sex,address
    </sql>

2、<include>

（1）用于引用定义的变量

（2）示例如下

<select id="getByCondition" parameterType="users" resultType="users">
        select <include refid="allColumns"/>
        from users
        <where>
            <if test="userName != null and userName != ''">
                and username like concat('%', #{userName}, '%')
            </if>
            <if test="birthday != null">
                and birthday = #{birthday}
            </if>
            <if test="sex != null and sex != ''">
                and sex = #{sex}
            </if>
            <if test="address != null and address != ''">
                and address like concat('%', #{address}, '%')
            </if>
        </where>
    </select>

3、<if>

（1）进行条件判断

（2）示例见上方代码

4、<when>

（1）如果有多个查询条件，可以使用<when>标签进行控制，标签可以自动的将第一个条件面前的逻辑运算符（or，and）去掉，

（2）示例见上方代码

5、<set>

（1）使用set标签可以将动态的配置 SET 关键字，并剔除追加到条件末尾的任何不相关的逗号。使用 if+set 标签修改后，在进行表单更新的操作中，哪个字段中有值才去更新，如果某项为 null 则不进行更新，而是保持数据库原值。切记：至少更新一列。

（2）示例如下

    <update id="getBySet" parameterType="users">
        update users
        <set>
            <if test="userName != null and userName != ''">
                username = #{userName},
            </if>
            <if test="birthday != null">
                birthday = #{birthday},
            </if>
            <if test="sex != null and sex != ''">
                sex = #{sex},
            </if>
            <if test="address != null and address != ''">
                address = #{address},
            </if>
        </set>
        where id = #{id}
    </update>

6、<foreach>

（1）参数

collection：指定入参的类型，常见的三个取值list，map，array

item：每次循环遍历出来的值或对象

separator：多个值或对象或语句之间的分隔符

open：整个循环外面的前字符

close：整个循环外面的后字符

index ：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选。

（2）批量删除

    <delete id="deleteMore">
        delete from users
        where id in
        <foreach collection="array" item="a" separator="," close=")" open="(">
            #{a}
        </foreach>
    </delete>

（3）批量更新

<!--    有选择的批量更新,至少更新一列-->
    <update id="updateSet"  >
       <foreach collection="list" item="u" separator=";">
        update users
        <set>
            <if test="u.userName != null  and u.userName != ''">
                username=#{u.userName},
            </if>
            <if test="u.birthday != null">
                birthday = #{u.birthday},
            </if>
            <if test="u.sex != null  and u.sex != ''">
                sex = #{u.sex},
            </if>
            <if test="u.address != null  and u.address != ''">
                address = #{u.address}
            </if>
        </set>
        where id = #{u.id}
       </foreach>
    </update>

注意：要使用批量更新，必须在jdbc.properties属性文件中的url中添加&allowMultiQueries=true，允许多行操作。 

（4）批量插入

    <insert id="insertMore">
        insert into users(username,birthday,sex,address) values
        <foreach collection="list" separator="," item="u">
            (#{u.userName},#{u.birthday},#{u.sex},#{u.address})
        </foreach>
    </insert>

7、使用指定参数位置

（1）为什么使用

如果入参是多个，可以通过指定参数位置进行传参，是实体包含不住的条件，实体类只能封装住成员变量的条件。如果某个成员变量要有区间范围的判定，或者有两个值进行处理，则实体类不够用。

（2）实现

可以不使用对象的属性名进行参数值绑定，使用下标值。 mybatis-3.3 版本和之前的版本使用#{0},#{1}方式， 从 mybatis3.4 开始使用#{arg0}方式。

①入参是普通类

    // 根据日期查询记录
    List<Users> getByDate(Date begin, Date end);

    <select id="getByDate" resultType="users">
        select <include refid="allColumns"/>
        from users
        where birthday between #{arg0} and #{arg1}
    </select>

②入参是map

如果入参超过一个以上，使用map封装查询条件，更有语义，查询条件更明确

    // 入参是map
    List<Users> getByMap(Map map);

    <select id="getByMap" resultType="users">
        select <include refid="allColumns"/>
        from users
        where birthday between #{birthdayBegin} and #{birthdayEnd}
    </select>

    @Test
    public void testGetByMap() throws ParseException {
        Map map = new HashMap();
        map.put("birthdayBegin", sf.parse("1999-1-1"));
        map.put("birthdayEnd", sf.parse("1999-12-31"));
        List<Users> list = usersMapper.getByMap(map);
        list.forEach(i -> System.out.println(i));
    }

#{birthdayBegin}就是map中的key

8、返回值是map

（1）概述

（1）如果返回的数据实体类无法包含，可以使用map返回多张表中的若干数据，返回后这些数据之间没有任何关系，就是Object类型，返回的map的key就是列名或别名

（2）返回值是map的适用场景,如果的数据不能使用对象来进行封装,可能查询的数据来自多张表中的某些列,这种情况下,使用map,但是map的返回方式破坏了对象的封装,返回来的数据是一个一个单独的数据, 之间不相关.map使用表中的列名或别名做为键名进行返回数据.

（2）返回值是一行的map

    // 返回值是map
    Map getReturnMap(Integer id);

    <select id="getReturnMap" parameterType="int" resultType="map">
        select username,birthday
        from users
        where id = #{id}
    </select>

    @Test
    public void testGetReturnMap(){
        Map map = usersMapper.getReturnMap(36);
        System.out.println(map);
        System.out.println(map.get("username"));
    }

（3）返回值是多行的map

    // 返回值是多行map
    List<Map> getReturnMaps(Integer[] arr);

    <select id="getReturnMaps" resultType="map">
        select username,birthday
        from users
        where id in
        <foreach collection="array" separator="," open="(" close=")" item="a">
            #{a}
        </foreach>
    </select>

    @Test
    public void testGetReturnMaps(){
        Integer[] arr = {36, 37, 38};
        List<Map> list = usersMapper.getReturnMaps(arr);
        list.forEach(i -> System.out.println(i));
    }

9、@Param指定参数名称

//切换列名进行模糊查询
//@Param("columnName"):这里定义的columnName的名称是要在xml文件中的${引用定义的名称}
List<Users> getByColunm(
                    @Param("columnName")
                    String columnName,
                    @Param("columnValue") 
                    String columnValue);

<select id="getByColunm" resultType="users">
    select <include refid="columns"></include>
    from users
    where ${columnName} =#{columnValue}
</select>

@Test
public void testGetByColumn(){
    List<Users> list = mapper.getByColunm("username","王五四");
    list.forEach(u-> System.out.println(u));
}

10、列名和类中成员变量名称不一致

（1）使用列的别名，别名与类中的成员变量名一样，即可完成注入。

<select id="getAll" resultType="book">
    select bookid id,bookname name
    from book
</select>

（2）使用<resultMap>标签进行映射。

<resultMap>：子标签<id>完成主键绑定，子标签<result>完成非主键绑定