Mybatis--動態SQL

韓家小志發表於2020-12-14
MyBatisSQL

動態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]
​

相關文章