Mybatis——Mybatis动态SQL的简介以及使用动态SQL对用户的优化

1. 动态SQL简介

1.1 动态SQL描述

    动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句的繁琐，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。
    利用动态 SQL，可以彻底摆脱这种痛苦。使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

1.2 标签的简介以及项目优化

1.2.1 < sql >标签以及< include >标签

• < sql >当多种类型的查询语句的查询字段或者查询条件相同时，可以将其定义为常量，方便调用。
• < include > 用于引用定义的常量。

两个结合使用优化了在查询时的语句:

    <sql id="allColumns">
        id,username,birthday,sex,address
    sql>
    
	 <select id="getAll" resultType="users">
        select  <include refid="allColumns">include>
        from users
   	 select>
1
2
3
4
5
6
7
8
9

1.2.2 < where >标签以及< if >标签

• < if >标签主要是用于条件的判断
• < where >标签：一般开发复杂业务的查询条件时，如果有多个查询条件，通常会使用标签来进行控制。 标签可以自动的将第一个条件前面的逻辑运算符 (or ,and) 去掉，正如代码中写的，id 查询条件前面是有“and”关键字的，但是在打印出来的 SQL 中却没有，这就是< where > 的作用。

 <select id="getByCondition" parameterType="users" resultType="users">
        select <include refid="allColumns">include>
        from users
        <where>
            <if test="username != null and username !=''">
                and username like cancat('%',#{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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

1.2.3 < set >标签

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

    <update id="updateBySet" 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>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

1.2.4 < foreach >标签

标签简介：
    < foreach >主要用来进行集合或数组的遍历，主要有以下参数：
collection：collection 属性的值有三个分别是 list、array、map 三种，分别对应的参数类型为：List、数组、map 集合。
    item ：循环体中的具体对象。支持属性的点路径访问，如item.age,item.info.details，在list和数组中是其中的对象，在map中是value。
    index ：在list和数组中,index是元素的序号，在map中，index是元素的key，该参数可选。
    open ：表示该语句以什么开始
    close ：表示该语句以什么结束
    separator ：表示元素之间的分隔符，例如在in()的时候，separator=","会自动在元素中间用“,“隔开，避免手动输入逗号导致sql错误，如in(1,2,)这样。该参数可选。

    <select id="getByIds" resultType="users">
        select <include refid="allColumns">include>
        from users
        where id in
            <foreach collection="array" item="id" separator="," open="(" close=")">
                #{id}
            foreach>
    select>
1
2
3
4
5
6
7
8
9
10
11
12

使用foreach标签实现批量增加和删除操作

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

    
    <insert id="insertBatch">
        insert into users (username,birthday,sex,address) values
        <foreach collection="list" item="user" separator=",">
            (#{user.username},#{user.birthday},#{user.sex},#{user.address})
        foreach>
    insert>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

2. 接口向xml文件中传入参数的优化

2.1 指定参数位置

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

使用方式：
接口中的代码

//查询指定日期范围内的用户
    List<Users> getByBirthday(Date date1,Date date2);
1
2

xml文件中的实现

    <select id="getByBirthday" resultType="users">
        select <include refid="allColumns">include>
        from users
        where birthday between #{arg0} and #{arg1}
    select>
1
2
3
4
5
6
7
8
9

2.2 注解@Param指定参数名称

接口中的实现：

//使用用户名和地址进行模糊查询
    /*使用注解，方便在xml文件中可以使用${}取到数据*/
    List<Users> getUsersByNameOrAddress(
            @Param("columnName") String columnName,
            @Param("columnValue") String columnValue);
1
2
3
4
5

xml文件中的实现

    <select id="getUsersByNameOrAddress"   resultType="users">
        select <include refid="allColumns">include>
        from users where ${columnName} like concat('%',#{columnValue},'%')
    select>
1
2
3
4
5
6
7
8

2.3 入参使用Map集合传入

接口中代码实现

 //入参数Map【将日期放入Map集合】，查询指定日期内的用户
    List<Users> getByMap(Map map);
1
2

xml文件中使用

 
    <select id="getByMap" resultType="users">
        select <include refid="allColumns">include>
        from users
        where birthday between #{birthdayBegin} and #{birthdayEnd}
select>
1
2
3
4
5
6
7
8
9

此时对测试时创建Map集合中的key就有要求，需要和#{}占位符中的数据名称一样
//入参数Map【将日期放入Map集合】，查询指定日期内的用户
   //List getByMap(Map map);
    @Test
    public void testGetByMap() throws ParseException {
        Date begin = sdf.parse("2000-01-01");
        Date end = sdf.parse("2000-04-04");
        Map map = new HashMap<>();
        map.put("birthdayBegin",begin);
        map.put("birthdayEnd",end);
        UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
        List<Users> usersList = usersMapper.getByMap(map);
        usersList.forEach(users -> System.out.println(users));
    }
1
2
3
4
5
6
7
8
9
10
11
12
13
14

2.4 返回结果时Map（额外内容）

实现返回一行Map
接口中的实现：

//返回值是Map,返回一行数据
    Map returnMapOne(Integer id);
1
2

xml文件中的实现：

 
    <select id="getByMap" resultType="users">
        select <include refid="allColumns">include>
        from users
        where birthday between #{birthdayBegin} and #{birthdayEnd}
select>
1
2
3
4
5
6
7
8
9

实现返回多行Map

接口中的实现：

//返回多行Map的实现，将Map放入到集合中去
    List<Map> returnMapMul();
1
2

xml文件中的实现：

   <!--
    //返回多行Map的实现，将Map放入到集合中去
    List<Map> returnMapMul();
    -->
    <select id="returnMapMul" resultType="map">
        select username ,address
        from users
    </select>
1
2
3
4
5
6
7
8

测试返回一行和多行map

 @Test
    public void testReturnMapOne(){
        UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
        Map map = usersMapper.returnMapOne(7);
        System.out.println(map);
    }

    @Test
    public void testReturnMapMul(){
        UsersMapper usersMapper = sqlSession.getMapper(UsersMapper.class);
        List<Map> mapList = usersMapper.returnMapMul();
        mapList.forEach(map -> System.out.println(map));
    }
1
2
3
4
5
6
7
8
9
10
11
12
13