MyBatis-动态 SQL 语句

1.动态 SQL-官方文档

1.文档地址

https://mybatis.org/mybatis-3/zh/dynamic-sql.html

2.为什么需要动态 SQL

1、动态 SQL 是 MyBatis 的强大特性之一

2、使用 JDBC 或其它类似的框架，根据不同条件拼接 SQL 语句非常麻烦，例如拼接时要 确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号等

3、 SQL 映射语句中的强大的动态 SQL 语言, 可以很好的解决这个问题.

2.动态 SQL-基本介绍

● 基本介绍

1. 在一个实际的项目中，sql 语句往往是比较复杂的
2. 为了满足更加复杂的业务需求，MyBatis 的设计者，提供了动态生成 SQL 的功能。

● 动态 SQL 必要性

1. 比如我们查询Monster 时，如果程序员输入的age 不大于0, 我们的sql语句就不带age 。
2. 更新 Monster 对象时，没有设置的新的属性值，就保持原来的值，设置了新的值，才更 新

● 解决方案分析

1. 从上面的需求我们可以看出，有时我们在生成 sql 语句时，在同一个方法中，还要根据 不同的情况生成不同的 sql 语句.
2. 解决方案: MyBatis 提供的动态 SQL 机制

● 动态 SQL 常用标签

动态 SQL 提供了如下几种常用的标签，类似我们 Java 的控制语句：

1. if [判断]
2. where [拼接 where 子句]
3. choose/when/otherwise [类似 java 的 switch 语句, 注意是单分支]
4. foreach [类似 in ]
5. trim [替换关键字/定制元素的功能]
6. set [在 update 的 set 中，可以保证进入set 标签的属性被修改，而没有进入 set 的，保持原来的值]

3.动态 SQL-案例演示

1.新建 Module dynamic-sql

2.if 标签应用实例

● 需求：请查询 age 大于 10 的所有妖怪，如果程序员输入的 age 不大于 0, 则输出所有 的妖怪!

1.修改MonsterMapper.java

//● 需求：请查询 age 大于 10 的所有妖怪，如果程序员输入的 age 不大于 0, 则输出所有 的妖怪!
//@Param注解是Mybatis用于指定参数名称的注解，建议都写上
List<Monster> findMonsterByAge(@Param("age") Integer age);
1
2
3

2.修改MonsterMapper.xml

<!--需求：请查询 age 大于 10 的所有妖怪，如果程序员输入的 age 不大于 0, 则输出所有 的妖怪!-->
<select id="findMonsterByAge" resultType="com.llp.entity.Monster">
    select * from monster where 1=1
    <if test="age>0">
        AND age > #{age}
    </if>
</select>
1
2
3
4
5
6
7

3.修改MonsterMapperTest.java

    @Test
    public void findMonsterByAge(){
        //select * from monster where 1=1 AND age > ? 查询年龄大于10的妖怪
        List<Monster> monsterList = monsterMapper.findMonsterByAge(10);
        System.out.println(monsterList);
        //select * from monster where 1=1 查询所有
        List<Monster> monsters = monsterMapper.findMonsterByAge(-1);
        System.out.println(monsters);
    }
1
2
3
4
5
6
7
8
9

3.where 标签应用实例

应用场景：where标签和if标签一起使用，当if标签满足条件的时候会去掉第一个多余AND，对sql进行拼接；当if标签没有一个满足的时候则去掉where及后面的条件，查询所有

● where 标签应用实例

需求：查询 id 大于 20 的，并且名字是 “牛魔王” 的所有妖怪, 注意，如果名字为空， 或者输入的 id 小于 0, 则不拼接 sql 语句(梳理：如果名字为空 , 就不带名字条件, 如果输入的 id 小于 0, 就不带 id 的条件)

1.修改MonsterMapper.java

//需求：查询 id 大于 20 的，并且名字是 "牛魔王" 的所有妖怪, 注意，如果名字为空，
// 或者输入的 id 小于 0, 则不拼接 sql 语句(梳理：如果名字为空 , 就不带名字条件, 如果输入的 id 小于 0, 就不带 id 的条件)
//根据 id 和名字来查询结果
 List<Monster> findMonsterByIdAndName(Monster monster);
1
2
3
4

2.修改MonsterMapper.xml

<!--如果名字为空 , 就不带名字条件, 如果输入的 id 小于 0, 就不带 id 的条件-->
<!--
1、配置 List<Monster> findMonsterByIdAndName(Monster monster);
2、查询id 大于 0的，并且名字是 "牛魔王" 的所有妖怪, 注意，如果名字为空，
    或者输入的id 小于 0, 则不拼接 sql语句(梳理：如果名字为空 , 就不带名字条件,
    如果输入的id 小于 0, 就不带id的条件)
3、where + if : WHERE `id` > #{id} AND `name` = #{name}
4. 如果我们入参是对象，test表达式中, 直接使用对象的属性名即可
5. where标签，会在组织动态sql时，加上where
6. mybatis底层自动的去掉多余的AND
7.如果查询条件中没有一个满足则去掉where及后面的条件查询所有
-->
<select id="findMonsterByIdAndName" resultType="com.llp.entity.Monster">
    select * from monster
    <where>
        <if test="id>=0">
            AND id=#{id}
        </if>
        <if test="name != null and name != ''">
            AND name = #{name}
        </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

3.修改MonsterMapperTest.java

    //如果名字为空 , 就不带名字条件, 如果输入的 id 小于 0, 就不带 id 的条件
    @Test
    public void findMonsterByIdAndName() {
        Monster monster = new Monster();
        monster.setId(1);
        monster.setName("牛魔王");
        //两个条件都满足的情况
        //select * from monster WHERE id=? AND name = ?
        List<Monster> monsterList = monsterMapper.findMonsterByIdAndName(monster);

        monster.setId(-11);
        monster.setName("牛魔王");
        //只有name满足条件，去掉了id作为查询条件
        //select * from monster WHERE name = ?
        monsterList = monsterMapper.findMonsterByIdAndName(monster);

        monster.setId(-11);
        monster.setName("");
        //都没有满足条件，则去掉where及查询条件，查询所有
        //select * from monster
        monsterList = monsterMapper.findMonsterByIdAndName(monster);
        for (Monster m : monsterList) {
            System.out.println(m);
        }
    }
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

4.choose/when/otherwise 应用实例

应用场景：多个条件走一个条件的时候

● 需求：如果给的 name 不为空，就按名字查询妖怪，如果指定的 id>0，就按 id 来查询妖 怪， 要求使用 choose/when/otherwise 标签实现, 传入参数要求使用 Map

1.修改MonsterMapper.java

//测试 choose 标签的使用
List<Monster> findMonsterByIdAndName_choose(Map<String, Object> map);
1
2

2.修改MonsterMapper.xml

<!--
● 需求：如果给的 name 不为空，就按名字查询妖怪，如果指定的 id>0，就按 id 来查询妖怪，都不满足则查询所有 要求使用 choose/when/otherwise 标签实现, 传入参数要求使用 Map
-->
<select id="findMonsterByIdAndName_choose" resultType="com.llp.entity.Monster">
    select * from monster
    <choose>
        <when test="name!=null and name!=''">
            where name = #{name}
        </when>
        <when test="id>0">
            where id = #{id}
        </when>
        <otherwise>
            where 1=1
        </otherwise>
    </choose>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3.修改MonsterMapperTest.java

@Test
public void findMonsterByIdAndName_choose() {
    Map<String, Object> map = new HashMap<>();
    map.put("id", 1);
    map.put("name", "牛魔王");
    //select * from monster where name = ?
    List<Monster> monsterList = monsterMapper.findMonsterByIdAndName_choose(map);
    map.put("id", 11);
    map.put("name",null);
    //如果id和name都满足，则根据xml中的先后顺序来
    //select * from monster where id = ?
    monsterList = monsterMapper.findMonsterByIdAndName_choose(map);
    System.out.println(monsterList);
    map.put("id", -1);
    map.put("name",null);
    //select * from monster where 1=1
    monsterList = monsterMapper.findMonsterByIdAndName_choose(map);
    System.out.println(monsterList);
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

5.forEach 标签应用实例

● 需求：查询 monster_id 为 20, 22, 34 的妖怪

1.修改MonsterMapper.java

//测试 foreach 的标签使用
 List<Monster> findMonsterById_forEach(Map<String, Object> map);
1
2

2.修改MonsterMapper.xml

<select id="findMonsterById_forEach" resultType="com.llp.entity.Monster">
select * from monster 
    <if test="ids != null and ids != ''">
        <where>
            id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </if>
</select>
1
2
3
4
5
6
7
8
9
10
11

3.修改MonsterMapperTest.java

@Test
public void findMonsterById_forEach() {
    Map<String, Object> map = new HashMap<>();
    map.put("ids", Arrays.asList(20, 22, 34));
    List<Monster> monsterList = monsterMapper.findMonsterById_forEach(map);
    System.out.println(monsterList);
}
1
2
3
4
5
6
7

6.trim 标签应用实例【使用较少】

**应用场景：prefix prefixOverrides前缀 suffix suffixOverrides后缀； 将前缀prefixOverrides替换为prefix 将后缀suffixsuffixOverrides替换为suffix **

● trim 可以替换一些关键字.要求：按名字查询妖怪，如果 sql 语句有 and | or 就替换成 where

1. 修改 MonsterMapper.java

//trim 标签的使用
List<Monster> findMonsterByName_Trim(Map<String, Object> map);
1
2

2. 修改 MonsterMapper.xml

<!--按名字查询妖怪，如果 sql 语句有 and | or | llp 就替换成 where-->
<select id="findMonsterByName_Trim" resultType="com.llp.entity.Monster">
   select * from monster
      <!--prefixOverrides前缀  suffixOverrides后缀-->
      <trim prefix="where" prefixOverrides="and|or|llp" >
         <if test="name != null and name != ''">
            llp name=#{name}
         </if>
         <if test="id>10">
            and id=#{id}
         </if>
      </trim>
</select>
1
2
3
4
5
6
7
8
9
10
11
12
13

3. 修改 MonsterMapperTest.java 并完成测试

@Test
public void findMonsterByName_Trim(){
    Map<String, Object> map = new HashMap<>();
    map.put("id",12);
    map.put("name","牛魔王");
    // select * from monster where name=? and id=?
    List<Monster> monsterList = monsterMapper.findMonsterByName_Trim(map);
    System.out.println(monsterList);
}
1
2
3
4
5
6
7
8
9

7.set 标签应用实例

应用场景：set 元素可以用于动态包含需要更新的列，忽略其它不更新的列，set 元素会动态地在行首插入 SET 关键字，并会删掉额外的逗号（这些逗号是在使用条件语句给列赋值时引入的）。或者，你可以通过使用trim元素来达到同样的效果：

<trim prefix="SET" suffixOverrides=",">
  ...
</trim>
1
2
3

● 需求: 请对指定 id 的妖怪进行 修改，如果没有设置新的属性，则保持原来的值

1. 修改 MonsterMapper.java

//测试 Set 标签
 void updateMonster_set(Map<String, Object> map);
1
2

2. 修改 MonsterMapper.xml

<update id="updateMonster_set">
   UPDATE monster
   <!-- 根据传入的值是否为空，来决定是否进行修改 map.put("name",...) map.put("age",...) map.put("monster_id",...) -->
   <set>
      <if test="age != null and age != ''">
         age = #{age},
      </if>
      <if test="birthday != null and birthday != ''">
          birthday = #{birthday},
      </if>
      <if test="email != null and email != ''">
          email = #{email},
      </if>
      <if test="gender != null and gender != ''">
          gender = #{gender},
      </if>
      <if test="name != null and name != ''">
          name = #{name},
      </if>
      <if test="salary != null and salary != ''">
          salary = #{salary},
      </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
23
24
25

3. 修改 MonsterMapperTest.java 并完成测试

@Test
public void updateMonster_set() {
    Map<String, Object> map = new HashMap<String, Object>();
    map.put("id", 1);
    map.put("name", "牛魔王");
    map.put("age", 10);
    // UPDATE monster SET age = ?, name = ? WHERE id = ?
    monsterMapper.updateMonster_set(map);
    if (sqlSession != null) {
        sqlSession.commit();
        sqlSession.close();
    }
    System.out.println("操作成功~");
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14