MyBatis从入门到精通(第4章)：MyBatis动态SQL【if、choose 和 where、set、trim】

(第4章)：MyBatis动态SQL【if、choose 和 where、set、trim】

MyBatis 的强大特性之一便是它的动态 SQL。MyBatis 3.4.6版本采用了功能强大的OGNL（Object-Graph Navigation Language）表达式语言，以下是MyBatis的动态SQL在XML中支持的几种标签。

if
choose(when、otherwise)
trim(where、set)
foreach
bind

本章除了讲解这几种标签的用法外，还会介绍如何在一个 XML 中针对不同的数据库编写不同的 SQL 语句，另外会对这 5 种标签中必须要用到的 OGNL 表达式进行一个简单的介绍。

4.1 <if> 用法

if 标签通常用于 WHERE 语句中，通过判断参数值来决定是否使用某个查询条件，它也经常用于UPDATE 语句中判断是否更新某一个字段，还可以在 INSERT 语句中用来判断是否插入某个字段的值。

4.1.1 在where条件中使用 if

假设有一个新的需求：实现一个用户管理高级查询功能，根据输入的条件去检索用户信息。这个功能还需要支持以下三种情况：

当只输入用户名时，需要根据用户名进行模糊查询；
当只输入邮箱时，根据邮箱进行完全匹配；
当同时输入用户名和邮箱时，用这两个条件去查询匹配的用户。

如果仍然按照前面章节中介绍的方法去编写代码，可能会是下面的样子：

    <select id="selectByUser" resultType="cn.bjut.example.model.SysUser">

        select id,
        user_name userName,
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
        from sys_user
        where
             user_name like concat('%', #{userName}, '%')
             and user_email = #{userEmail}
    </select>

当同时输入userName和userEmail这两个条件时，能查出正确的结果，但是当只提供userName的参数时，userEmail默认是null，这就会导致user_email=null也成为了查询条件，因而查不出正确的结果。

这时可以使用if标签来解决这个问题了，代码如下。

    <select id="selectByUser" resultType="cn.bjut.simple.model.SysUser">
        select id,
        user_name userName,
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
        from sys_user
        where 1 = 1
            <if test="userName != null and userName != ''">
                and user_name like concat('%', #{userName}, '%')
            </if>
            <if test="userEmail != '' and userEmail != null">
                and user_email = #{userEmail}
            </if>
    </select>

if标签有个必填的test属性的值，是一个符合OGNL要求的判断表达式。表达式的结果只有0为false，所有的非0值都为true。

为了方便理解，在表达式中，建议只用true或false作为结果。

判断条件 property !=null 或 property==null 适用于任何类型的字段，用于判断属性值是否为空。
判断条件 property !=‘’ 或 property==‘’ 仅适用于String类型的字段，用于判断是否为空字符串。
and 和 or :当有多个判断条件时，使用and或or进行连接，嵌套的判断可以使用小括号分组，and相当于（&&），or相当于（||）

在本章所有例子中，字符串的判断几乎都包含是否为null和空‘’的判断，这两个条件不是必须写在一起。

有了 XML 中的方法后，还需要在 UserMapper 接口中增加对应的接口方法，代码如下。

    /**
     * 第4章
     * 根据动态条件查询用户信息
     *
     * @param sysUser
     * @return
     */
    List<SysUser> selectByUser(SysUser sysUser);

View Code

测试方法如下。

    @Test
    public void testSelectByUser(){
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            //只查询用户名时
            SysUser query = new SysUser();
            query.setUserName("ad");
            List<SysUser> userList = userMapper.selectByUser(query);
            Assert.assertTrue(userList.size() > 0);
            //只查询用户邮箱时
            query = new SysUser();
            query.setUserEmail("test@mybatis.tk");
            userList = userMapper.selectByUser(query);
            Assert.assertTrue(userList.size() > 0);
            //当同时查询用户名和邮箱时
            query = new SysUser();
            query.setUserName("ad");
            query.setUserEmail("test@mybatis.tk");
            userList = userMapper.selectByUser(query);
            //由于没有同时符合这两个条件的用户，查询结果数为 0
            Assert.assertTrue(userList.size() == 0);
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

View Code

从日志中可以看到，查询条件的不同组合最终执行的SQL和预期一样，这样就实现了动态条件查询。

DEBUG [main] - ==>  Preparing: select id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime from sys_user where 1 = 1 and user_name like concat('%', ?, '%') 
DEBUG [main] - ==> Parameters: ad(String)
TRACE [main] - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <==        Row: 1, admin, 123456, admin@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-07-12 17:00:58.0
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime from sys_user where 1 = 1 and user_email = ? 
DEBUG [main] - ==> Parameters: test@mybatis.tk(String)
TRACE [main] - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
TRACE [main] - <==        Row: 1001, test, 123456, test@mybatis.tk, <<BLOB>>, <<BLOB>>, 2019-07-12 17:01:52.0
DEBUG [main] - <==      Total: 1
DEBUG [main] - ==>  Preparing: select id, user_name userName, user_password userPassword, user_email userEmail, user_info userInfo, head_img headImg, create_time createTime from sys_user where 1 = 1 and user_name like concat('%', ?, '%') and user_email = ? 
DEBUG [main] - ==> Parameters: ad(String), test@mybatis.tk(String)
DEBUG [main] - <==      Total: 0

Console 打印日志

注意SQL中 where 关键字后面的条件: where 1 = 1

由于两个条件都是动态的，所以如果没有1=1这个默认条件，当两个if判断都不满足时，最后生成的SQL就会以where结束，这样会报错不符合SQL规范。

加上1=1这个条件就可以避免SQL语法错误导致的异常。在4.3节中会介绍where标签的用法，可以替代这种不美观的写法。

注意条件中的and(或or)

and user_name like concat('%', #{userName}, '%')

这里的and(或or)需要手动添加，当这部分条件拼接到where 1 =1 后面时仍然是合法的SQL。因为有默认的 1=1 这个条件，我们才不需要判断第一个动态条件是否需要加上 and （或 or），因为这种情况下 and（或 or）是必须有的。

4.1.2 在UPDATE更新列中使用if

现在要实现这样一个需求：只更新有变化的字段。需要注意，更新的时候不能将原来有值但没有发生变化的字段更新为空或 null。通过 if 标签可以实现这种动态列更新。

在MyBatis中一般把选择性更新的方法名会以 Selective作为后缀。

    /**
     * 根据主键更新
     *
     * @param sysUser
     * @return
     */
    int updateByIdSelective(SysUser sysUser);

接下来在UserMapper.xml映射文件中增加对应的SQL语句，代码如下。

    <update id="updateByIdSelective">
        update sys_user
        set
            <if test="userName != null and userName != ''">
                user_name = #{userName},
            </if>
            <if test="userPassword != null and userPassword != ''">
                user_password = #{userPassword},
            </if>
            <if test="userEmail != null and userEmail != ''">
                user_email = #{userEmail},
            </if>
            <if test="userInfo != null and userInfo != ''">
                user_info = #{userInfo},
            </if>
            <if test="headImg != null">
                head_img = #{headImg, jdbcType=BLOB},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            id = #{id}
        where id = #{id}

    </update>

update id="updateByIdSelective"

需要注意的有两点：第一点是每个if元素里面SQL语句后面的逗号；第二点就是where关键字前面的 id = #{id}这个条件。考虑以下两种情况：

全部的查询条件都是null或者空。

如果有id=#{id}这个条件，最终的SQL如下。

update sys_user set id=#{id} where id=#{id}

如果没有这个条件，最终的SQL如下。

update sys_user set    where id=#{id}

这个SQL很明显是错误的，set关键字后面没有内容。

查询条件只有一个不是null也不是空（假设是userName）

如果有id=#{id}这个条件，最终的SQL如下。

update sys_user set user_name=#{userName} , id=#{id} where id=#{id}

如果没有id=#{id}这个条件，最终的SQL如下。

update sys_user set user_name=#{userName} ,   where id=#{id}

where关键字前面直接是一个逗号，这个SQL语句也是错的。

从上面两种情况来看，id=#{id}这个条件可以最大限度的保障方法不出错。除此之外，也可以通过where和set标签来解决这些问题。

下面是以上方法的测试代码。

@Test
    public void testUpdateByIdSelective(){
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            //从数据库查询 1 个 user 对象
            SysUser user = new SysUser();
            //更新 id = 1 的用户
            user.setId(1L);
            //修改邮箱
            user.setUserEmail("test@mybatis.tk");
            //将新建的对象插入数据库中，特别注意，这里的返回值 result 是执行的 SQL 影响的行数
            int result = userMapper.updateByIdSelective(user);
            //只更新 1 条数据
            Assert.assertEquals(1, result);
            //根据当前 id 查询修改后的数据
            user = userMapper.selectById(1L);
            //修改后的名字保持不变，但是邮箱变成了新的
            Assert.assertEquals("admin", user.getUserName());
            Assert.assertEquals("test@mybatis.tk", user.getUserEmail());
        } finally {
            //为了不影响数据库中的数据导致其他测试失败，这里选择回滚
            sqlSession.rollback();
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

public void testUpdateByIdSelective()

4.1.3 在INSERT动态插入列中使用if标签

在数据库表中插入数据的时候，如果某一列的参数值不为空，就使用传入的值，如果传入参数为空，就使用数据库中的默认值（通常是空），而不使用传入的空值。使用 if 就可以实现这种动态插入列的功能。

先修改 sys_user 表，在数据库中执行如下的 SQL 语句给 user_email 列增加默认值 test@mybatis.tk 。

ALTER TABLE `sys_user`
MODIFY COLUMN `user_email` varchar(50) NULL DEFAULT 'test@bjut.emails.cn' 
       COMMENT '邮箱' 
       AFTER `user_password`;

下面直接修改SysUserMapper.xml中的insert2方法。

    <insert id="insert22" useGeneratedKeys="true" keyProperty="id">
        insert into sys_user(
        user_name, user_password,
            <if test="userEmail != null and userEmail != ''">
            user_email,
            </if>
            user_info, head_img, create_time)
        values(
            #{userName}, #{userPassword},
            <if test="userEmail != null and userEmail != ''">
            #{userEmail},
            </if>
            #{userInfo}, #{headImg, jdbcType=BLOB}, #{createTime, jdbcType=TIMESTAMP})
    </insert>

在 INSERT 中使用时要注意，若在列的部分增加 if 条件，则 values 的部分也要增加相同的 if 条件，必须保证上下可以互相对应，完全匹配。

该方法的测试代码如下。

    @Test
    public void testInsert2Selective(){
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            //创建一个 user 对象
            SysUser user = new SysUser();
            user.setUserName("test-selective");
            user.setUserPassword("123456");
            user.setUserInfo("test info");
            user.setCreateTime(new Date());
            //插入数据库
            userMapper.insert2(user);
            //获取插入的这条数据
            user = userMapper.selectById(user.getId());
            Assert.assertEquals("test@mybatis.tk", user.getUserEmail());

        } finally {
            sqlSession.rollback();
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

View Code

在新增的 user 中，我们并没有给 userEmail 属性赋值，这样就会使用数据库的默认值，执行测试后，输出日志如下。

观察日志输出的 SQL 语句和查询结果，INSERT 语句中并没有插入 user_email 列，查询的结果中 user_email 的值就是我们设置的默认值 test@mybatis.tk 。

4.2 choose 用法

上一节的 if 标签提供了基本的条件判断，但是它无法实现 if...else 、 if...else... 的逻辑，要想实现这样的逻辑，就需要用到 choose when otherwise 标签。

choose 元素中包含 when 和 otherwise 两个标签，一个 choose 中至少有一个 when，有 0 个或者 1 个 otherwise 。

在已有的 sys_user 表中，除了主键 id 外，我们认为 user_name（用户名）也是唯一的，所有的用户名都不可以重复。现在进行如下查询：当参数 id 有值的时候优先使用 id 查询，当 id 没有值时就去判断用户名是否有值，如果有值就用用户名查询，

如果用户名也没有值，就使 SQL 查询无结果。

首先在 UserMapper接口中添加如下方法。

    /**
     * 根据用户 id 或用户名查询
     *
     * @param sysUser
     * @return
     */
    SysUser selectByIdOrUserName(SysUser sysUser);

View Code

然后在 UserMapper.xml 中添加如下 SQL。

    <select id="selectByIdOrUserName" resultType="SysUser">
        select id,
        user_name userName,
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
        from sys_user
        where 1 = 1
        <choose>
            <when test="id != null">
                and id = #{id}
            </when>
            <when test="userName != null and userName != ''">
                and user_name = #{userName}
            </when>
            <otherwise>
                limit 0
            </otherwise>
        </choose>
    </select>

使用 choose when otherwise 的时候逻辑要严密，避免由于某些值出现问题导致 SQL 出错。如果没有 otherwise 这个限制条件，所有的用户都会被查询出来，因为我们在对应的接口方法中使用了 SysUser 作为返回值，所以当实际查询结果是多个时就会报错。添加 otherwise 条件后，由于 where 条件不满足，因此在这种情况下就查询不到结果。

针对这个方法，编写如下测试。

    @Test
    public void testSelectByIdOrUserName(){
        SqlSession sqlSession = getSqlSession();
        try {
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            //只查询用户名时
            SysUser query = new SysUser();
            query.setId(1L);
            query.setUserName("admin");
            SysUser user = userMapper.selectByIdOrUserName(query);
            Assert.assertNotNull(user);
            //当没有 id 时
            query.setId(null);
            user = userMapper.selectByIdOrUserName(query);
            Assert.assertNotNull(user);
            //当 id 和 name 都为空时
            query.setUserName(null);
            user = userMapper.selectByIdOrUserName(query);
            Assert.assertNull(user);
        } finally {
            //不要忘记关闭 sqlSession
            sqlSession.close();
        }
    }

View Code

choose 用法并不复杂，通过这个例子以及 if 中的多个例子，相信大家应该很容易掌握 choose 的用法。

4.3 where、set、trim 用法

这 3 个标签解决了类似的问题，并且 where 和 set 都属于 trim 的一种具体用法。下面分别来看这 3 个标签。

4.3.1 where 用法

where 标签的作用：如果该标签包含的元素中有返回值，就插入一个 where；如果 where 后面的字符串是以 AND 和 OR 开头的，就将它们剔除。

首先修改 UserMapper.xml 中的 selectByUser 方法，注意这个方法在 4.1.1 节中的用法。此处将这个方法改成使用 where 标签，代码如下。

    <select id="selectByUser2" resultType="cn.bjut.simple.model.SysUser">
        select id,
        user_name userName,
        user_password userPassword,
        user_email userEmail,
        user_info userInfo,
        head_img headImg,
        create_time createTime
        from sys_user
        
        <where>
            <if test="userName != null and userName != ''">
                and user_name like concat('%', #{userName}, '%')
            </if>
            <if test="userEmail != '' and userEmail != null">
                and user_email = #{userEmail}
            </if>
        </where>
    </select>

当 if 条件都不满足的时候，where 元素中没有内容，所以在 SQL 中不会出现 where，也就不存在 4.1.1 节中 SQL 错误的问题。

如果 if 条件满足，where 元素的内容就是以 and 开头的条件，where 会自动去掉开头的 and，这也能保证 where 条件正确。

和 4.1.1 节中相比，这种情况下生成的 SQL 更干净、更贴切，不会在任何情况下都有 where 1=1 这样的条件。

4.3.2 set 用法

set 标签的作用：如果该标签包含的元素中有返回值，就插入一个 set；如果 set 后面的字符串是以逗号结尾的，就将这个逗号剔除。

修改 UserMapper.xml 中的 updateByIdSelective 方法，注意和 4.1.2 节中的区别，代码如下。

    <update id="updateByIdSelective2">

        update sys_user
        <set>
            <if test="userName != null and userName != ''">
                user_name = #{userName},
            </if>
            <if test="userPassword != null and userPassword != ''">
                user_password = #{userPassword},
            </if>
            <if test="userEmail != null and userEmail != ''">
                user_email = #{userEmail},
            </if>
            <if test="userInfo != null and userInfo != ''">
                user_info = #{userInfo},
            </if>
            <if test="headImg != null">
                head_img = #{headImg, jdbcType=BLOB},
            </if>
            <if test="createTime != null">
                create_time = #{createTime, jdbcType=TIMESTAMP},
            </if>
            id = #{id},
        </set>
        where id = #{id}
    </update>