011，Mybatis动态Sql

Mybatis动态Sql

---

V哥官网：http://www.vgxit.com

本博客对应视频教程：http://www.vgxit.com/course/22

---

1，概述：

我们之前已经学习了如何编写Sql来查询对应的数据。但是有的时候真的开发的时候我们的Sql不会是完全固定的。

比如我们有三个查询需求：

• 通过年龄查询用户
• 通过性别查询用户
• 通过年龄和性别查询用户

这个时候可能我们会学三个sql来解决上面问题：

• select * from user where age=#{age}
• select * from user where gender=#{gender}
• select * from user where age=#{age} and gender=#{gender}

但是同学们有没有觉得上面的这种方式真的是太痛苦了，其实上面的sql仅仅只是查询条件不同，而其他的地方sql的结构都是相同的。我们分成三个sql来写真的是很麻烦，我们能够能有一个思路传入参数中不为空我们就加上对应的查询条件。我们的动态Sql就是来解决这个问题的。

---

2，if元素：

if元素是我们再编写Mysql的sql的时候最常用的元素。和java里面一样，if元素是用来在生成动态Sql的时候做条件判断的。

我们就用上面的概述来举例：

1，我们创建对应的通过年龄和性别查询的方法

List<User> findByGenderAndAge(@Param("gender") Short gender, @Param("age") Integer age);

2，xml中定义对应的sql

<select id="findByGenderAndAge" resultType="com.vgxit.learn.vgmybatis.ktdm.po.User">
        select * from user where 1=1
        <if test="gender != null">
            and gender=#{gender}
        </if>
        <if test="age != null">
            and age=#{age}
        </if>
    </select>

3，运行调试

private static void testIf() throws IOException {
        try (SqlSession sqlSession = MybatisTool.getSqlSession()) {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> users = userMapper.findByGenderAndAge(null, null);
            users.forEach(System.out::println);
        }
    }

我们可以看到只打印出来了满足条件的查询

为什么我们在讲课的时候，我们要加上where 1=1?如果不加，那么可能就出现sql语法错误，比如where and age=?扩折直接是select * from user where。

---

3，choose,when,otherwise元素：

有的同学看到三个元素一起讲，直接就吓到了，对吧，其实大家不用怕，这三个元素可以帮我们实现类似于Java中的if,else if, else的功能。假如我们有这样的功能，我们传入年龄的类型，如果是"agetype1"那么查询20到25岁的用户。如果是"agetype2"那么查询25到40岁的用户，否则查询40岁以上的用户。

1，我们创建对应的通过年龄类型查询的方法

List<User> findByAgeType(@Param("ageType") String ageType);

2，编写sql

<select id="findByAgeType" resultType="com.vgxit.learn.vgmybatis.ktdm.po.User" parameterType="string">
        select * from user where 1=1
        <choose>
            <when test="ageType != null and ageType == 'ageType1'">
                and age>=20 and age<25
            </when>
            <when test="ageType != null and ageType == 'ageType2'">
                and age>=25 and age<40
            </when>
            <otherwise>
                and age>=40
            </otherwise>
        </choose>
    </select>

3，测试

private static void testChoose() throws IOException {
        try (SqlSession sqlSession = MybatisTool.getSqlSession()) {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            List<User> users = userMapper.findByAgeType("ageType1");
            users.forEach(System.out::println);
        }
    }

我们直接运行，发现报错了，这个是什么原因？老师说一下，这个地方是属于我们sql里面使用了>,<这种和xml冲突的符号。要解决上面问题，我们可以有两种办法。

1，用&gt;表示大于，用&lt;表示小于:

<select id="findByAgeType" resultType="com.vgxit.learn.vgmybatis.ktdm.po.User" parameterType="string">
        select * from user where 1=1
        <choose>
            <when test="ageType != null and ageType == 'ageType1'">
                and age&gt;=20 and age&lt;25
            </when>
            <when test="ageType != null and ageType == 'ageType2'">
                and age&gt;=25 and age&lt;40
            </when>
            <otherwise>
                and age&gt;=40
            </otherwise>
        </choose>
    </select>

但是老师觉得，上面的代码看上去很丑，老师是不太喜欢，这个时候，我们可以使用CDATA的方式：

<select id="findByAgeType" resultType="com.vgxit.learn.vgmybatis.ktdm.po.User" parameterType="string">
        select * from user where 1=1
        <choose>
            <when test="ageType != null and ageType == 'ageType1'">
                and age <![CDATA[>=]]> 20 and age <![CDATA[<]]> 25
            </when>
            <when test="ageType != null and ageType == 'ageType2'">
                and age <![CDATA[>=]]> 25 and age <![CDATA[<]]> 40
            </when>
            <otherwise>
                and age <![CDATA[>=]]> 40
            </otherwise>
        </choose>
    </select>

---

4，where元素：

我们上面使用的所有的sql，都加了一个条件where 1=1。如果没有这个条件的时候Sql会报错。但是我们加上了1=1zhihou ,有非常的奇怪。这个时候，我们用where标签可以完全解决：

<select id="findByAgeType" resultType="com.vgxit.learn.vgmybatis.ktdm.po.User" parameterType="string">
        select * from user
        <where>
            <choose>
                <when test="ageType != null and ageType == 'ageType1'">
                    age <![CDATA[>=]]> 20 and age <![CDATA[<]]> 25
                </when>
                <when test="ageType != null and ageType == 'ageType2'">
                    age <![CDATA[>=]]> 25 and age <![CDATA[<]]> 40
                </when>
                <otherwise>
                    age <![CDATA[>=]]> 40
                </otherwise>
            </choose>
        </where>
    </select>

---

5，set元素：

这个元素在做修改操作的时候会经常使用。比如我们这里要修改用户信息，在Jpa里面我们修改用户信息的方式是吧所有的字段都查询出来全部更新一次。这样不需要更新的字段还是被自我覆盖了一次，虽然对Mysql的性能带来的压力也不大，但是总有一些性能开销。那么现在怎么办呢？

有没有一个办法，就是我传入对应的PO，PO对应的属性有设置值，我更新，没有设置值的，我不管。

<update id="updateUser" parameterType="user">
    update user set
    <if test="user.name != null">
        name = #{user.name},
    </if>
    <if test="user.gender != null">
        gender = #{user.gender},
    </if>
    <if test="user.age != null">
        age = #{user.age},
    </if>
    <if test="user.nickName != null">
        nick_name = #{user.nickName}
    </if>
    <where>
        id=#{user.id}
    </where>
</update>

 

然后，我们直接所有的字段都复制，完全没有问题：

private static void testSet() throws IOException {
        try (SqlSession sqlSession = MybatisTool.getSqlSession()) {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            User user = User.builder().id(4).name("郑爽").gender((short) 1).age(26).nickName("爽妹儿111").build();
            userMapper.updateUser(user);
            sqlSession.commit();
        }
    }

但是，如果我们对NickName不赋值，会怎么样呢？

直接报错了，因为sql在where的前面多了一个无用的逗号：

update user set name=?, gender=?, age=?, WHERE id=?

那应该怎么办呢，我们可以用set标签来解决

<update id="updateUser" parameterType="user">
        update user
        <set>
            <if test="name != null">
                name=#{name},
            </if>
            <if test="gender != null">
                gender=#{gender},
            </if>
            <if test="age != null">
                age=#{age},
            </if>
            <if test="nickName != null">
                nick_name=#{nickName}
            </if>
        </set>
        <where>
            id=#{id}
        </where>
    </update>

现在运行就没有问题了，set标签会自动检查对应的sql，把不要的逗号都给干掉。

---

6，foreach元素：

foreach元素就是一个循环语句，它尝尝在in语句中使用。

比如，我们要通过多个id来查询到对应的用户。怎么办？

List<User> findByIds(@Param("ids") List<Integer> ids);

对应的xml中的sql:

<select id="findByIds" resultType="com.vgxit.learn.vgmybatis.ktdm.po.User" parameterType="list">
        select * from user
        <where>
            id in
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </where>
    </select>

foreach的各个属性介绍如下：

• collection:这是的ids就是我们传进来的参数的名称，它可以是一个数组，List，Set等集合。
• item:被循环的当前的元素
• open：以什么符号开始
• close: 以什么符号结束
• separator: 各个元素之间的分隔符

---

7，基于Annotation的动态sql：

1，通过<script>标签来处理动态sql:

@Select("<script>" +
            "select * from user\n" +
            "        <where>\n" +
            "            id in\n" +
            "            <foreach collection=\"ids\" item=\"id\" open=\"(\" separator=\",\" close=\")\">\n" +
            "                #{id}\n" +
            "            </foreach>\n" +
            "        </where>" +
            "</script>")
    List<User> findByIds(@Param("ids") List<Integer> ids);

2，基于@XXXProvider来动态实现sql:

@SelectProvider(type = UserSqlProvider.class, method = "findByIds")
    List<User> findByIds(@Param("ids") List<Integer> ids);
@InsertProvider(type = UserSqlProvider.class, method = "addUser")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int addUser(User user);

    class UserSqlProvider {
        public String findByIds(@Param("ids") List<Integer> ids) {
            List<String> strIds = ids.stream().map(id -> id + "").collect(Collectors.toList());
            String idstr = String.join(",", strIds);
            return new SQL()
                    .SELECT("*")
                    .FROM("user")
                    .WHERE("id in (" + idstr + ")")
                    .toString();
        }

        public String addUser(User user) {
            return new SQL()
                    .INSERT_INTO("user")
                    .VALUES(
                            "name, gender, age, nick_name",
                            "'" + user.getName() + "', " + user.getGender() + "," + user.getAge() + ",'" + user.getNickName() + "'"
                    ).toString();
        }
    }