Mybatis--动态SQL

动态SQL

• 我们接下来使用动态SQL实现以下功能：
  • 需求1：查询所有男性用户，如果输入了姓名，则按照姓名模糊查找；如果没有输入则不管姓名
    ​ - 需求2：查询所有用户，传递参数orderType，如果值为0，按照年龄升序排序，如果为1则按照年龄降序排序，否则按照ID排序
    ​ - 需求3：查询所有用户，如果有姓名不为空，则按照姓名模糊查找；如果年龄也不为空，则还要满足年龄条件。
    ​ - 需求4：修改用户信息，如果某字段为null，则不修改这个字段
    ​ - 需求5：根据多个ID查询用户

1 if 判断

需求1：查询所有男性用户，如果输入了姓名，则按照姓名模糊查找；如果没有输入则不管姓名

1.1 接口:

// 需求1：查询所有男性用户，如果输入了姓名，则按照姓名模糊查找；如果没有输入则不管姓名
public List<User> queryManUsersByName(@Param("name") String name);

1.2 mapper文件:

<!--需求1：查询所有男性用户，如果输入了姓名，则按照姓名模糊查找；如果没有输入则不管姓名-->
<select id="queryManUsersByName" resultMap="userResultMap">
    select <include refid="userColumns"/>
    from tb_user
    where sex=1
    <if test="name!=null and name.trim()!=''">
        and name like '%${name}%'
    </if>
</select>

1.3 测试1: 如果姓名不为null

 @Test
    public void queryManUsersByName() {
        List<User> userList = userMapper.queryManUsersByName("张");
        for (User user : userList) {
            System.out.println(user);
        }
    }

• 日志信息:

2019-10-09 14:55:46,860 [main] [org.apache.ibatis.datasource.pooled.PooledDataSource]-[DEBUG] Created connection 624271064.
2019-10-09 14:55:46,860 [main] [org.apache.ibatis.transaction.jdbc.JdbcTransaction]-[DEBUG] Setting autocommit to false on JDBC Connection [com.mysql.jdbc.JDBC4Connection@25359ed8]
2019-10-09 14:55:46,862 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryManUsersByName]-[DEBUG] ==>  Preparing: select id, user_name, password, name, age, sex, birthday, created, updated from tb_user where sex=1 and name like '%张%' 
2019-10-09 14:55:46,883 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryManUsersByName]-[DEBUG] ==> Parameters: 
2019-10-09 14:55:46,904 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryManUsersByName]-[DEBUG] <==      Total: 2
User [id=1, userName=zhangsan, password=123456, name=张三, age=30, sex=1, birthday=Wed Aug 08 00:00:00 CST 1984, created=Fri Sep 19 16:56:04 CST 2014, updated=Sun Sep 21 11:24:59 CST 2014]
User [id=4, userName=zhangwei, password=123456, name=张伟, age=20, sex=1, birthday=Thu Sep 01 00:00:00 CDT 1988, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]

1.4 测试2: 如果姓名为null

2019-10-09 14:58:49,745 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryManUsersByName]-[DEBUG] ==>  Preparing: select id, user_name, password, name, age, sex, birthday, created, updated from tb_user where sex=1 
2019-10-09 14:58:49,764 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryManUsersByName]-[DEBUG] ==> Parameters: 
2019-10-09 14:58:49,783 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryManUsersByName]-[DEBUG] <==      Total: 6
User [id=1, userName=zhangsan, password=123456, name=张三, age=30, sex=1, birthday=Wed Aug 08 00:00:00 CST 1984, created=Fri Sep 19 16:56:04 CST 2014, updated=Sun Sep 21 11:24:59 CST 2014]
User [id=4, userName=zhangwei, password=123456, name=张伟, age=20, sex=1, birthday=Thu Sep 01 00:00:00 CDT 1988, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
User [id=5, userName=lina, password=123456, name=李娜, age=28, sex=1, birthday=Tue Jan 01 00:00:00 CST 1985, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
User [id=6, userName=lilei, password=123456, name=李磊, age=23, sex=1, birthday=Mon Aug 08 00:00:00 CDT 1988, created=Sat Sep 20 11:41:15 CST 2014, updated=Sat Sep 20 11:41:15 CST 2014]
User [id=14, userName=guanyuchang, password=abc, name=关云长, age=28, sex=1, birthday=Fri Oct 04 00:00:00 CST 2019, created=Fri Oct 04 18:15:34 CST 2019, updated=Fri Oct 04 18:16:26 CST 2019]
User [id=16, userName=zhuobotong2, password=123456, name=周伯通2, age=38, sex=1, birthday=Sat Sep 09 00:00:00 CST 2000, created=Wed Oct 09 13:08:41 CST 2019, updated=Wed Oct 09 13:08:41 CST 2019]

• 注意：
  • 在写SQL时，我们使用了”%${name}%”来拼接SQL，这样在传参数时，可以只写姓名
  • 这里也可以写#{name}来进行预编译，那么传参数时，就必须在参数中写上”%李%”了

2 choose,when,otherwise

• 动态标签中有if，但是没有else，如果我们有多条件，就需要用choose标签 choose中可以定义多个when和1个otherwise，所有状态中只能有一个成立： 多个when类似与if 和 else if otherwise类似于最后的else
• 需求2：查询所有用户，传递参数orderType，如果值为0，按照年龄升序排序，如果为1则按照年龄降序排序，否则按照ID排序

2.1 接口

// 需求2：查询所有用户，传递参数orderType，如果值为0，按照年龄升序排序，如果为1则按照年龄降序排序，否则按照ID排序
public List<User> queryUsersAndSort(@Param("orderType") int orderType);

2.2 mapper文件

  <!--
        需求2：查询所有用户，传递参数orderType，
        如果值为0，按照年龄升序排序，
        如果为1则按照年龄降序排序，
        否则按照ID排序
    -->
    <select id="queryUsersAndSort" resultMap="userResultMap">
        select <include refid="userColumns"/>
        from tb_user
        <choose>
            <when test="orderType==0">order by age asc</when>
            <when test="orderType==1">order by age desc</when>
            <otherwise>order by id asc</otherwise>
        </choose>
    </select>

2.3 测试

 @Test
    public void queryUsersAndSort() {
        List<User> userList = userMapper.queryUsersAndSort(10);
        for (User user : userList) {
            System.out.println(user);
        }
    }

• 测试日志:

2019-10-09 15:35:32,282 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersAndSort]-[DEBUG] ==>  Preparing: select id, user_name, password, name, age, sex, birthday, created, updated from tb_user order by id asc 
2019-10-09 15:35:32,302 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersAndSort]-[DEBUG] ==> Parameters: 
2019-10-09 15:35:32,324 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersAndSort]-[DEBUG] <==      Total: 9
1====张三====30
2====李四====21
3====王五====22
4====张伟====20
5====李娜====28
6====李磊====23
12====戚继光====18
14====关云长====28
16====周伯通2====38

3 where

• 需求3：查询所有用户，如果有姓名不为空，则按照姓名模糊查找；如果年龄也不为空，则还要满足年龄小于指定年龄。

3.1 接口

// 需求3：查询所有用户，如果有姓名不为空，则按照姓名模糊查找；如果年龄也不为空，则还要满足年龄条件。
public List<User> queryUsersByNameAndAge(@Param("name") String name,
                                         @Param("age") int age);

3.2 mapper文件

 <!--需求3：查询所有用户，如果有姓名不为空，则按照姓名模糊查找；如果年龄也不为空，则还要满足年龄条件。-->
    <select id="queryUsersByNameAndAge" resultMap="userResultMap">
        select <include refid="userColumns"/>
        from tb_user
        <where>
            <if test="name!=null and name.trim()!=''">
                and name like '%${name}%'
            </if>
            <if test="age!=null">
                and age &lt; #{age}
            </if>
        </where>
    </select>

3.3 测试

@Test
public void queryUsersByNameAndAge() {
// 需求3：查询所有用户，如果有姓名不为空，则按照姓名模糊查找；如果年龄也不为空，则还要满足年龄条件。
List userList = userMapper.queryUsersByNameAndAge(“李”, 25);
for (User user : userList) {
System.out.println(user);
}
}

• 测试日志

2019-10-09 15:39:02,109 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersByNameAndAge]-[DEBUG] ==>  Preparing: select id, user_name, password, name, age, sex, birthday, created, updated from tb_user WHERE name like '%李%' and age < ? 
2019-10-09 15:39:02,129 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersByNameAndAge]-[DEBUG] ==> Parameters: 25(Integer)
2019-10-09 15:39:02,149 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersByNameAndAge]-[DEBUG] <==      Total: 2
User [id=2, userName=lisi, password=123456, name=李四, age=21, sex=2, birthday=Tue Jan 01 00:00:00 CST 1991, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
User [id=6, userName=lilei, password=123456, name=李磊, age=23, sex=1, birthday=Mon Aug 08 00:00:00 CDT 1988, created=Sat Sep 20 11:41:15 CST 2014, updated=Sat Sep 20 11:41:15 CST 2014]

4 set

• 需求4：修改用户信息，如果某字段为null，则不修改这个字段
• 在修改用户时，有一些不需要修改的字段我们可能不愿意填写，这时，修改时的非空判断就非常有必要 而且在多个不确定有哪些字段或者有没有字段需要修改时，我们就需要set标签

4.1 接口

 // 需求4：修改用户信息，如果某字段为null，则不修改这个字段
    public void updateUser2(User user);

4.2 mapper文件

  <!--需求4：修改用户信息，如果某字段为null，则不修改这个字段-->
    <update id="updateUser2">
        update tb_user
        <set>
            <if test="userName!=null and userName.trim()!=''">
                user_name = #{userName},
            </if>
            <if test="password!=null and password.trim()!=''">
                password = #{password},
            </if>
            <if test="name!=null and name.trim()!=''">
                name = #{name},
            </if>
            <if test="age!=null">
                age = #{age},
            </if>
            <if test="sex!=null">
                sex = #{sex},
            </if>
            <if test="birthday!=null and birthday.trim()!=''">
                birthday = #{birthday},
            </if>
            updated = now()
        </set>
        where id = #{id}
    </update>

4.3 测试

 @Test
    public void updateUser2() {
        User user = new User();
        user.setId(16L);
        user.setName("王重阳");
        user.setuserName("wangchongyang");
        userMapper.updateUser2(user);
    }

• 日志:

2019-10-09 15:43:05,298 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.updateUser2]-[DEBUG] ==>  Preparing: update tb_user SET user_name = ?, name = ?, updated = now() where id = ? 
2019-10-09 15:43:05,316 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.updateUser2]-[DEBUG] ==> Parameters: wangchongyang(String), 王重阳(String), 16(Long)
2019-10-09 15:43:05,319 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.updateUser2]-[DEBUG] <==    Updates: 1

5 foreach

• 需求5：根据多个ID查询用户

5.1 接口

 // 需求5：根据多个ID查询用户
    public List<User> queryUsersByIds(@Param("ids") List<Long> ids);

5.2 mapper文件

  <!--需求5：根据多个ID查询用户-->
    <select id="queryUsersByIds" resultMap="userResultMap">
        select <include refid="userColumns"/>
        from tb_user
        <where>
            <if test="ids!=null and ids.size()>0">
                and id in
                <foreach collection="ids" item="id" open="(" close=")" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

5.3 测试

    @Test
    public void queryUsersByIds() {
        // 需求5：根据多个ID查询用户
        List<Long> idList = new ArrayList<Long>();
        idList.add(1L);
        idList.add(2L);
        idList.add(3L);
​
        List<User> userList = userMapper.queryUsersByIds(null);
        for (User user : userList) {
            System.out.println(user);
        }
    }

• 测试日志:

2019-10-09 15:45:56,095 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersByIds]-[DEBUG] ==>  Preparing: select id, user_name, password, name, age, sex, birthday, created, updated from tb_user 
2019-10-09 15:45:56,113 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersByIds]-[DEBUG] ==> Parameters: 
2019-10-09 15:45:56,131 [main] [cn.hanjiaxiaozhi.mapper.UserMapper.queryUsersByIds]-[DEBUG] <==      Total: 9
User [id=1, userName=zhangsan, password=123456, name=张三, age=30, sex=1, birthday=Wed Aug 08 00:00:00 CST 1984, created=Fri Sep 19 16:56:04 CST 2014, updated=Sun Sep 21 11:24:59 CST 2014]
User [id=2, userName=lisi, password=123456, name=李四, age=21, sex=2, birthday=Tue Jan 01 00:00:00 CST 1991, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
User [id=3, userName=wangwu, password=123456, name=王五, age=22, sex=2, birthday=Sun Jan 01 00:00:00 CST 1989, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
User [id=4, userName=zhangwei, password=123456, name=张伟, age=20, sex=1, birthday=Thu Sep 01 00:00:00 CDT 1988, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
User [id=5, userName=lina, password=123456, name=李娜, age=28, sex=1, birthday=Tue Jan 01 00:00:00 CST 1985, created=Fri Sep 19 16:56:04 CST 2014, updated=Fri Sep 19 16:56:04 CST 2014]
User [id=6, userName=lilei, password=123456, name=李磊, age=23, sex=1, birthday=Mon Aug 08 00:00:00 CDT 1988, created=Sat Sep 20 11:41:15 CST 2014, updated=Sat Sep 20 11:41:15 CST 2014]
User [id=12, userName=qijiguang, password=abc, name=戚继光, age=18, sex=2, birthday=Fri Oct 04 00:00:00 CST 2019, created=Fri Oct 04 16:43:34 CST 2019, updated=Fri Oct 04 17:26:51 CST 2019]
User [id=14, userName=guanyuchang, password=abc, name=关云长, age=28, sex=1, birthday=Fri Oct 04 00:00:00 CST 2019, created=Fri Oct 04 18:15:34 CST 2019, updated=Fri Oct 04 18:16:26 CST 2019]
User [id=16, userName=zhuobotong2, password=123456, name=周伯通2, age=38, sex=1, birthday=Sat Sep 09 00:00:00 CST 2000, created=Wed Oct 09 13:08:41 CST 2019, updated=Wed Oct 09 13:08:41 CST 2019]
​