MyBatis系列四: 动态SQL

动态SQL语句-更复杂的查询业务需求

官方文档
基本介绍
案例演示
- if标签应用实例
- where标签应用实例
- choose/when/otherwise应用实例
- foreach标签应用实例
- trim标签应用实例[使用较少]
- set标签应用实例[重点]
- 课后练习

上一讲, 我们学习的是 MyBatis系列三: 原生的API与配置文件详解

现在打开mybatis项目

在这里插入图片描述

官方文档

文档地址:
https://mybatis.org/mybatis-3/zh_CN/dynamic-sql.html

为什么需要动态SQL
1.动态 SQL 是 MyBatis 的强大特性之一
2.使用 JDBC 或其它类似的框架, 根据不同条件拼接 SQL 语句非常麻烦, 例如拼接时要确保不能忘记添加必要的空格, 还要注意去掉列表最后一个列名的逗号等.
3.SQL 映射语句中的强大的动态 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的, 保持原来的值]

案例演示

1.在原来的mybatis项目中, 新建dynamic-sql子项目 [参考], 演示动态SQL的使用

2.新建Module后, 先创建需要的包, 再将需要的文件 / 资源拷贝过来(这里我们拷贝Monster.java, resources/jdbc.properties 和 mybatis-config.xml)

3.拷贝MonsterMapper.java, MonsterMapper.xml 和 MonsterMapperTest.java, 做一个比较干净的讲解环境

if标签应用实例

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

1.修改MonsterMapper.java, 增加方法接口

public interface MonsterMapper {
    //根据age查询结果
    public List<Monster> findMonsterByAge(@Param(value = "age") Integer age);
}

2.修改MonsterMapper.xml

<mapper namespace="com.zzw.mapper.MonsterMapper">
    <!--   
    1.配置/实现public List<Monster> findMonsterByAge(@Param(value = "age")Integer age);



    2.请查询`age` 大于 `10` 的所有妖怪, 如果程序员输入的`age` 不大于 `0`, 则输出所有的妖怪!



    3.如果我们按照以前方式来配置->问题? 如果使用原来的#{age} 在test表达式是取不出入参值


    4.解决方案是使用@Param 
    -->
    <select id="findMonsterByAge" parameterType="Integer" resultType="Monster"><!--这里能用简写是因为mybatis-config.xml文件里配置了别名-typeAliases-->
        SELECT * FROM `monster` where 1 = 1
        <if test="age > 0">
            AND `age` > #{age}
        </if>
    </select>
</mapper>

3.测试MonsterAnnotationTest

@Test
public void findMonsterByAge() {
    List<Monster> monsters
            = monsterMapper.findMonsterByAge(-1);
    for (Monster monster : monsters) {
        System.out.println("monster--" + monster);
    }

    if (sqlSession != null) {
        sqlSession.close();
    }

    System.out.println("操作成功");
}

where标签应用实例

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

1.修改MonsterMapper.java, 增加方法接口

public interface MonsterMapper {
    //根据id和名字来查询结果
	public List<Monster> findMonsterByIdAndName(Monster monster);
}

2.修改MonsterMapper.xml

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

3.测试MonsterAnnotationTest

@Test
public void findMonsterByIdAndName() {
    Monster monster = new Monster();
    monster.setId(20);
    monster.setName("牛魔王");

    List<Monster> monsters = monsterMapper.findMonsterByIdAndName(monster);
    for (Monster m : monsters) {
        System.out.println("m=" + m);
    }

    if (sqlSession != null) {
        sqlSession.close();
    }

    System.out.println("操作成功");
}

choose/when/otherwise应用实例

●需求:
1) 如果给的name不为空, 就按名字查询妖怪.
2) 如果指定的id>0, 就按id来查询妖怪.
3) 如果前面两个条件都不满足, 就默认查询 salary > 100的
4) 要求使用 choose/when/otherwise 标签实现, 传入参数要求使用Map

1.修改MonsterMapper.java, 增加方法接口

public interface MonsterMapper {
	//测试choose标签的使用
    public List<Monster> findMonsterByIdOrName_choose(Map<String, Object> map);
}

2.修改MonsterMapper.xml

<!--
1.配置/使用public List<Monster> findMonsterByIdOrName_choose(Map<String, Object> map);
2.  1) 如果给的`name`不为空, 就按名字查询妖怪.
    2) 如果指定的`id>0`, 就按`id`来查询妖怪.
    3) 如果前面两个条件都不满足, 就默认查询 `salary > 100`的
    4) 使用mybatis 提供choose-when-otherwise
-->
<select id="findMonsterByIdOrName_choose" parameterType="map" resultType="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 `salary` > 100
        </otherwise>
    </choose>
</select>

3.测试MonsterAnnotationTest

@Test
public void findMonsterByIdOrName_choose() {
    Map<String, Object> map = new HashMap<>();
    map.put("id", -1);
    //map.put("name", "牛魔王");
    List<Monster> monsters = monsterMapper.findMonsterByIdOrName_choose(map);
    for (Monster monster : monsters) {
        System.out.println("monster--" + monster);
    }

    if (sqlSession != null) {
        sqlSession.close();
    }

    System.out.println("查询成功");
}

foreach标签应用实例

●需求: 查询id 为 20, 22, 34的妖怪

1.修改MonsterMapper.java, 增加方法接口

public interface MonsterMapper {
	//测试foreach标签的使用
	public List<Monster> findMonsterById_forEach(Map<String, Object> map);
}

2.修改MonsterMapper.xml

<!--
1.配置/实现public List<Monster> findMonsterById_forEach(Map<String, Object> map);
2.查询`id` 为 `20, 22, 34`的妖怪
3.使用foreach标签
4.入参map 中 会如何传入id值 k-v, ids - [集合, 比如List 10,12,14]
  , 即map 入参中应当有 ids-[10,12,14]
-->
<select id="findMonsterById_forEach" resultType="Monster" parameterType="map">
    SELECT * FROM `monster`

    <!--解读
    1. where标签
    2. 再写入相应的处理代码, 比如判断ids 是否为空.. if
    3. 如果ids不为空, 则使用foreach标签进行遍历
    4. collection="ids" 对应你的入参map的 key - ids
    5. item="id" 在遍历ids集合时, 每次取出的值, 对应的变量id
    6. open="(" 对应的就是sql (10,12,14) 的第一 (
    7. separator="," 遍历出来的多个值的 分隔符号
    8. close=")" 对应的就是sql (10,12,14)  的最后 )
    9. #{id} 对应的就是 item="id"
    -->
    <if test="ids != null and ids != ''">
        <where>
            `id` IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </if>
</select>

3.测试MonsterAnnotationTest

@Test
public void findMonsterById_forEach() {
    Map<String, Object> map = new HashMap<>();
    //map.put("ids", Arrays.asList(10, 12));
    List<Monster> monsters = monsterMapper.findMonsterById_forEach(map);

    for (Monster monster : monsters) {
        System.out.println("monster--" + monster);
    }

    if (sqlSession != null) {
        sqlSession.close();
    }

    System.out.println("查询成功");
}

trim标签应用实例[使用较少]

●trim可以替换一些关键字. 要求: 按名字和年龄查询妖怪, 如果sql语句开头有 and | or 就替换成 where

1.修改MonsterMapper.java, 增加方法接口

public interface MonsterMapper {
    //trim标签的使用
    public List<Monster> findMonsterByName_Trim(Map<String, Object> map);
}

2.修改MonsterMapper.xml

<!--
1.配置/实现public List<Monster> findMonsterByName_Trim(Map<String, Object> map);
2.按名字和年龄查询妖怪, 如果sql语句开头有 and | or 就替换成 where
3.分析: 如果要实现这个功能, 其实使用where标签 [加入where 同时会去掉多余的and]
4.trim prefix="WHERE" prefixOverrides="and|or|zzw" 如果子句的开头为 "AND" 或 "OR" 或 "zzw"
, 就去除
-->
<select id="findMonsterByName_Trim" parameterType="map" resultType="Monster">
    SELECT * FROM `monster`
    <trim prefix="WHERE" prefixOverrides="and|or|zzw">
        <if test="name != null and name != ''">
            and `name` = #{name}
        </if>
        <if test="age != null and age != ''">
            AND `age` > #{age}
        </if>
    </trim>
</select>

3.测试MonsterAnnotationTest

@Test
public void findMonsterByName_Trim() {
    Map<String, Object> map = new HashMap<>();
    map.put("age", 30);
    map.put("name", "牛魔王");
    List<Monster> monsters = monsterMapper.findMonsterByName_Trim(map);
    for (Monster monster : monsters) {
        System.out.println("monster--" + monster);
    }

    if (sqlSession != null) {
        sqlSession.close();
    }

    System.out.println("操作成功");
}

set标签应用实例[重点]

●需求: 请对指定id的妖怪进行修改, 如果没有设置新的属性, 则保存原来的值
- 先回顾传统的方式是如何处理的? -> 麻烦
- 使用set标签搞定

1.修改MonsterMapper.java, 增加方法接口

public interface MonsterMapper {
    //测试set标签
    public void updateMonster_set(Map<String, Object> map);
}

2.修改MonsterMapper.xml

<!--
1.配置/实现public void updateMonster_set(Map<String, Object> map);
2.请对指定id的妖怪进行 修改, 如果没有设置新的属性, 则保存原来的值
3.入参要根据sql语句来配合 map [age-10, email-'9789@qq.com'...]
4.set标签会处理多余的 ,
-->
<update id="updateMonster_set" parameterType="map">
    UPDATE `monster`
    <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="name != null and name != ''">
            `name` = #{name},
        </if>
        <if test="gender != null and gender != ''">
            `gender` = #{gender},
        </if>
        <if test="salary != null and salary != ''">
            `salary` = #{salary},
        </if>
    </set>
    WHERE `id` = #{id}
</update>

3.测试MonsterAnnotationTest

@Test
public void updateMonster_set() {
    Map<String, Object> map = new HashMap<>();
    map.put("id", 5);
    //map.put("name", "牛魔王6");
    //map.put("age", 18);
    //map.put("birthday", "2000-10-15 12:12:12");
    monsterMapper.updateMonster_set(map);

    //增删改需要提交事务
    if (sqlSession != null) {
        sqlSession.commit();
        sqlSession.close();
    }

    System.out.println("修改成功");
}

课后练习

自己创建个新的项目完成.
● 要求属性
1. 编号- id
2. 外号 -nickname
3. 本领 - skill
4. 排行 - rank
5. 薪水 - salary
6. 入伙日期 - days (要求可以保存年-月-日时:分:秒)

● 完成功能
1. 创建表hero
2. 编写方法, 添加hero记录 [方法就在HeroMapper.xml配置] ➡️
3. 编写方法: 查询rank大于 10 的所有hero, 如果输入的rank 不大于 0, 则输出所有hero ➡️
4. 编写方法: 查询rank 为 3, 6, 8[rank可变] 的hero ➡️
5. 编写方法: 修改hero信息, 如果没有设置新的属性值, 则保持原来的值 ➡️
6. 编写方法: 可以根据id查询hero, 如果没有传入id, 就返回所有hero ➡️
7. 自己完成, 会有新的体会.

● 环境准备
1.新建hero表

USE mybatis;
CREATE TABLE `hero` (
`id` INT NOT NULL AUTO_INCREMENT,
`nickname` VARCHAR(255) NOT NULL,
`skill` VARCHAR(255) NOT NULL,
`rank` TINYINT NOT NULL,
`salary` DOUBLE NOT NULL,
`days` TIMESTAMP NOT NULL,
PRIMARY KEY(id)
)CHARSET=utf8

2.在原来的mybatis项目中, 新建homework-hero子项目 [参考]

3.新建Module后, 先创建需要的包, 再将需要的文件 / 资源拷贝过来(这里我们拷贝com.zzw.entity.Hero.java, resources/jdbc.properties 和 mybatis-config.xml)

@Getter
@Setter
@ToString
public class Hero {
    private Integer id;
    private String nickname;
    private String skill;
    private Integer rank;
    private Double salary;
    private Date days;
}

4.拷贝com.zzw.mapper.HeroMapper.java接口, com.zzw.mapper.HeroMapper.xml 和 com.zzw.mapper.HeroMapperTest.java, 做一个比较干净的讲解环境

编写方法: 添加hero记录 ⬆️
1.HeroMapper

public interface HeroMapper {
    //添加hero记录
    public void addHero(Hero hero);
}

2.HeroMapper.xml

<mapper namespace="com.zzw.mapper.HeroMapper">
    <!--
    1.配置/实现public void addHero(Hero hero)
    2.添加hero记录
    -->
    <insert id="addHero" parameterType="Hero">
        INSERT INTO `hero` (`nickname`, `skill`, `rank`, `salary`, `days`)
        VALUES (#{nickname}, #{skill}, #{rank}, #{salary}, #{days});
    </insert>
</mapper>

3.测试HeroMapperTest

public class HeroMapperTest {
    //属性
    private SqlSession sqlSession;
    private HeroMapper heroMapper;

    //编写方法完成初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        heroMapper = sqlSession.getMapper(HeroMapper.class);
        System.out.println("heroMapper=" + heroMapper.getClass());
    }

    @Test
    public void addHero() {
        Hero hero = new Hero();
        hero.setNickname("花和尚");
        hero.setSkill("倒拔垂杨柳");
        hero.setRank(13);
        hero.setSalary(7500.0);
        hero.setDays(new Date());

        heroMapper.addHero(hero);

        //增删改需要提交事务
        if (sqlSession != null) {
            sqlSession.commit();
            sqlSession.close();
        }

        System.out.println("操作成功");
    }

编写方法: 查询rank大于 10 的所有hero, 如果输入的rank 不大于 0, 则输出所有hero ⬆️
1.HeroMapper

public interface HeroMapper {
    //查询rank大于 10 的所有hero, 如果输入的rank 不大于 0, 则输出所有hero
    public List<Hero> findHeroByRank(Map<String, Object> map);
}

2.HeroMapper.xml

<mapper namespace="com.zzw.mapper.HeroMapper">
    <!--
    1.配置/实现public void findHeroByRank(HashMap<String, Object> map);
    2.查询rank大于 10 的所有hero, 如果输入的rank 不大于 0, 则输出所有hero
    -->
    <select id="findHeroByRank" parameterType="map" resultType="Hero">
        SELECT * FROM `hero` WHERE 1 = 1
        <if test="rank > 0">
            AND `rank` > #{rank}
        </if>
    </select>
</mapper>

3.测试HeroMapperTest

public class HeroMapperTest {
    //属性
    private SqlSession sqlSession;
    private HeroMapper heroMapper;

    //编写方法完成初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        heroMapper = sqlSession.getMapper(HeroMapper.class);
        System.out.println("heroMapper=" + heroMapper.getClass());
    }

    @Test
    public void findHeroByRank() {
        Map<String, Object> map = new HashMap<>();
        map.put("rank", 10);
        List<Hero> heroes = heroMapper.findHeroByRank(map);
        for (Hero hero : heroes) {
            System.out.println("hero--" + hero);
        }

        //关闭连接
        if (sqlSession != null) {
            sqlSession.close();
        }

        System.out.println("查询成功");
    }

编写方法: 查询rank 为 3, 6, 8[rank可变] 的hero ⬆️
1.HeroMapper

public interface HeroMapper {
    //查询rank 为 3, 6, 8[rank可变] 的hero
    public List<Hero> findHeroByRank_choose(Map<String, Object> map);
}

2.HeroMapper.xml

<mapper namespace="com.zzw.mapper.HeroMapper">
	<!--
    1.配置/实现public List<Hero> findHeroByRank_choose(Map<String, Object> map);
    2.查询rank 为 3, 6, 8[rank可变] 的hero
    3.入参map k-v key-ranks 值 List - [4, 12, 13]
    -->
    <select id="findHeroByRank_choose" parameterType="map" resultType="Hero">
        SELECT * FROM `hero`
        <!--
        解读
        1.where标签
        2.对ranks是否为空做判断
        3.如果ranks不为空, 则使用foreach做判断
        4.collection="ranks" 对应入参map 的key
        5.item="rank" 在遍历ranks集合时, 每次取出的值, 叫做变量rank
        6.open="(" 对应的就是sql (4, 12, 13) 第一(
        7.separator="," 遍历出来的多个值的 分隔符号
        8.close=")" 对应的就是sql (4, 12, 13) 最后)
        9.#{rank} 对应的就是 item="rank"
        -->
        <where>
            <if test="ranks != null and ranks != ''">
                `rank` IN
                <foreach collection="ranks" item="rank" open="(" separator="," close=")">
                    #{rank}
                </foreach>
            </if>
        </where>
    </select>
</mapper>

3.测试HeroMapperTest

public class HeroMapperTest {
    //属性
    private SqlSession sqlSession;
    private HeroMapper heroMapper;

    //编写方法完成初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        heroMapper = sqlSession.getMapper(HeroMapper.class);
        System.out.println("heroMapper=" + heroMapper.getClass());
    }

    @Test
    public void findHeroByRank_choose() {
        Map<String, Object> map = new HashMap<>();
        map.put("ranks", Arrays.asList(4, 12, 13));
        List<Hero> heroes = heroMapper.findHeroByRank_choose(map);
        for (Hero hero : heroes) {
            System.out.println("hero--" + hero);
        }

        if (sqlSession != null) {
            sqlSession.close();
        }

        System.out.println("查询成功");
    }

编写方法: 修改hero信息, 如果没有设置新的属性值, 则保持原来的值 ⬆️
1.HeroMapper

public interface HeroMapper {
    //修改hero信息, 如果没有设置新的属性值, 则保持原来的值
    public void updateHeroById_set(Hero hero);
}

2.HeroMapper.xml

<mapper namespace="com.zzw.mapper.HeroMapper">
    <!--
    1.配置/实现public void updateHeroById_set(Hero hero);
    2.修改hero信息, 如果没有设置新的属性值, 则保持原来的值
    -->
    <update id="updateHeroById_set" parameterType="hero">
        UPDATE `hero`
        <set>
            <if test="nickname != null and nickname != ''">
                `nickname` = #{nickname},
            </if>
            <if test="rank != null and rank != ''">
                `rank` = #{rank},
            </if>
            <if test="skill != null and skill != ''">
                `skill` = #{skill},
            </if>
            <if test="salary != null and salary != ''">
                `salary` = #{salary},
            </if>
            <if test="days != null and days != ''">
                `days` = #{days},
            </if>
        </set>
        WHERE id = #{id}
    </update>
</mapper>

3.测试HeroMapperTest

public class HeroMapperTest {
    //属性
    private SqlSession sqlSession;
    private HeroMapper heroMapper;

    //编写方法完成初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        heroMapper = sqlSession.getMapper(HeroMapper.class);
        System.out.println("heroMapper=" + heroMapper.getClass());
    }
    @Test
    public void updateHeroById_set() {
        Hero hero = new Hero();
        hero.setNickname("豹子头");
        hero.setRank(5);
        hero.setSalary(120000.0);
        hero.setId(4);
        heroMapper.updateHeroById_set(hero);

        //增删改需要提交事务
        if (sqlSession != null) {
            sqlSession.commit();
            sqlSession.close();
        }

        System.out.println("操作成功");
    }

编写方法: 可以根据id查询hero, 如果没有传入id, 就返回所有hero ⬆️
1.HeroMapper

public interface HeroMapper {
    //可以根据id查询hero, 如果没有传入id, 就返回所有hero
    public List<Hero> findHeroById(@Param(value = "id") Integer id);
}

2.HeroMapper.xml

<mapper namespace="com.zzw.mapper.HeroMapper">
    <!--
    1.配置/实现public List<Hero> findHeroById(@Param(value = "id") Integer id);
    2.可以根据id查询hero, 如果没有传入id, 就返回所有hero
    -->
    <select id="findHeroById" parameterType="Integer" resultType="Hero">
        SELECT * FROM `hero`
        <choose>
            <when test="id != null and id != ''">
                WHERE `id` = #{id}
            </when>
        </choose>
    </select>
</mapper>

3.测试HeroMapperTest

public class HeroMapperTest {
    //属性
    private SqlSession sqlSession;
    private HeroMapper heroMapper;

    //编写方法完成初始化
    @Before
    public void init() {
        sqlSession = MyBatisUtils.getSqlSession();
        heroMapper = sqlSession.getMapper(HeroMapper.class);
        System.out.println("heroMapper=" + heroMapper.getClass());
    }

    @Test
    public void findHeroById() {
        List<Hero> heroes = heroMapper.findHeroById(1);
        for (Hero hero : heroes) {
            System.out.println("hero--" + hero);
        }

        if (sqlSession != null) {
            sqlSession.close();
        }

        System.out.println("查询成功");
    }