概述

where set trim 这3个标签解决了类似的问题，并且where 和 set 都属于trim的一种具体用法，下面我们通过具体的示例来学习下。

where 作用及用法

作用

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

用法

我们使用动态SQL if 元素中的例子，点击跳转到该示例

不使用where元素的SQL如下：

<select id="selectSysUsersAdvanced" resultType="com.artisan.mybatis.xml.domain.SysUser">
        SELECT
            a.id,
            a.user_name userName,
            a.user_password userPassword,
            a.user_email userEmail,
            a.user_info userInfo,
            a.head_img headImg,
            a.create_time createTime
        FROM
            sys_user a
        WHERE 1=1
        <if test="userName != null and userName != '' ">
            and user_name like concat('%',#{userName},'%')
        </if>
        <if test="userEmail != null and userEmail != '' ">
            and user_email = #{userEmail}
        </if>
    </select>

我们在这里通过where元素来实现一遍

<select id="selectSysUsersAdvancedWithWhere" resultType="com.artisan.mybatis.xml.domain.SysUser">
        SELECT
            a.id,
            a.user_name userName,
            a.user_password userPassword,
            a.user_email userEmail,
            a.user_info userInfo,
            a.head_img headImg,
            a.create_time createTime
        FROM
            sys_user a
        <where>
            <if test="userName != null and userName != '' ">
                and user_name like concat('%',#{userName},'%')
            </if>
            <if test="userEmail != null and userEmail != '' ">
                and user_email = #{userEmail}
            </if>
        </where>
    </select>

当if 条件都不满足的时候where元素中没有内容，所以SQL中不会出现Where。如果if条件满足，where元素的内容就是以and开头的条件，where会自动去掉开头的and ,这也能保证where条件的正确。相比上个案例，这种情况下生成的SQL更干净，不会在任何情况下都有where 1 = 1 这样的条件。

增加接口方法

/**
     * 
     * 
     * @Title: selectSysUsersAdvancedWithWhere
     * 
     * @Description: selectSysUsersAdvancedWithWhere
     * 
     * @param sysUser
     * @return
     * 
     * @return: List<SysUser>
     */
    List<SysUser> selectSysUsersAdvancedWithWhere(SysUser sysUser);

单元测试

@Test
    public void selectSysUsersAdvancedWithWhere() {
        logger.info("selectSysUsersAdvancedWithWhere");
        // 获取SqlSession
        SqlSession sqlSession = getSqlSession();
        List<SysUser> userList = null;
        try {
            // 获取UserMapper接口
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            logger.info("===========1.当用户只输入用户名时，需要根据用户名模糊查询===========");
            // 模拟前台传参 1.当用户只输入用户名时，需要根据用户名模糊查询
            SysUser sysUser = new SysUser();
            sysUser.setUserName("ad");
            // 调用selectSysUsersAdvanced,根据查询条件查询用户
            userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);
            // 根据数据库sys_user表中的记录,可以匹配到admin, 期望userList不为空
            Assert.assertNotNull(userList);
            // 根据查询条件，期望只有1条数据
            Assert.assertTrue(userList.size() == 1);
            Assert.assertEquals("admin", userList.get(0).getUserName());
            logger.info("userList:" + userList);
            // 为了测试 匹配多条记录的情况，我们将id=1001这条数据的userName 由test 改为artisan
            sysUser.setUserName("i");
            // 调用selectSysUsersAdvanced,根据查询条件查询用户
            userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);
            // 根据数据库sys_user表中的记录,可以匹配到admin和artisan, 期望userList不为空
            Assert.assertNotNull(userList);
            // 根据查询条件，期望只有2条数据
            Assert.assertTrue(userList.size() == 2);
            logger.info("userList:" + userList);
            logger.info("===========2.当用户只输入邮箱使，根据邮箱进行完全匹配===========");
            // 模拟前台传参 2.当用户只输入邮箱使，根据邮箱进行完全匹配
            sysUser.setUserEmail("admin@artisan.com");
            userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);
            Assert.assertNotNull(userList);
            Assert.assertTrue(userList.size() == 1);
            logger.info(userList);
            sysUser.setUserEmail("1admin@artisan.com");
            userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);
            Assert.assertTrue(userList.size() == 0);
            logger.info("===========3.当用户同时输入用户名和密码时，用这两个条件查询匹配的用户===========");
            // 模拟组合查询条件,存在记录的情况
            sysUser.setUserName("i");
            sysUser.setUserEmail("admin@artisan.com");
            userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);
            Assert.assertNotNull(userList);
            Assert.assertEquals("admin@artisan.com", sysUser.getUserEmail());
            Assert.assertTrue(userList.size() == 1);
            logger.info(userList);
            logger.info("===========4.当用户同时输入无法匹配的用户名和密码===========");
            // 模拟组合查询条件,不存在记录的情况
            sysUser.setUserName("x");
            sysUser.setUserEmail("admin@artisan.com");
            userList = userMapper.selectSysUsersAdvancedWithWhere(sysUser);
            Assert.assertTrue(userList.size() == 0);
            logger.info(userList);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
            logger.info("sqlSession close successfully ");
        }
    }

日志

2018-04-21 21:32:11,318  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-21 21:32:11,323  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-21 21:32:11,327  INFO [main] (UserMapperTest.java:601) - selectSysUsersAdvancedWithWhere
2018-04-21 21:32:11,357  INFO [main] (UserMapperTest.java:610) - ===========1.当用户只输入用户名时，需要根据用户名模糊查询===========
2018-04-21 21:32:11,962 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') 
2018-04-21 21:32:12,046 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: ad(String)
2018-04-21 21:32:12,081 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 21:32:12,082 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:32:12,091 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-21 21:32:12,096  INFO [main] (UserMapperTest.java:622) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 21:32:12,096 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') 
2018-04-21 21:32:12,097 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String)
2018-04-21 21:32:12,098 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 21:32:12,099 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:32:12,100 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:32:12,102 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 2
2018-04-21 21:32:12,104  INFO [main] (UserMapperTest.java:633) - userList:[SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018], SysUser [id=1001, userName=artisan, userPassword=123456, userEmail=test@artisan.com, userInfo=测试用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 21:32:12,105  INFO [main] (UserMapperTest.java:635) - ===========2.当用户只输入邮箱使，根据邮箱进行完全匹配===========
2018-04-21 21:32:12,105 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') and user_email = ? 
2018-04-21 21:32:12,106 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), admin@artisan.com(String)
2018-04-21 21:32:12,107 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 21:32:12,108 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:32:12,109 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-21 21:32:12,109  INFO [main] (UserMapperTest.java:641) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 21:32:12,110 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') and user_email = ? 
2018-04-21 21:32:12,111 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: i(String), 1admin@artisan.com(String)
2018-04-21 21:32:12,112 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 0
2018-04-21 21:32:12,113  INFO [main] (UserMapperTest.java:647) - ===========3.当用户同时输入用户名和密码时，用这两个条件查询匹配的用户===========
2018-04-21 21:32:12,113  INFO [main] (UserMapperTest.java:655) - [SysUser [id=1, userName=admin, userPassword=123456, userEmail=admin@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]]
2018-04-21 21:32:12,114  INFO [main] (UserMapperTest.java:657) - ===========4.当用户同时输入无法匹配的用户名和密码===========
2018-04-21 21:32:12,115 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: SELECT a.id, a.user_name userName, a.user_password userPassword, a.user_email userEmail, a.user_info userInfo, a.head_img headImg, a.create_time createTime FROM sys_user a WHERE user_name like concat('%',?,'%') and user_email = ? 
2018-04-21 21:32:12,116 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: x(String), admin@artisan.com(String)
2018-04-21 21:32:12,117 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 0
2018-04-21 21:32:12,117  INFO [main] (UserMapperTest.java:663) - []
2018-04-21 21:32:12,119  INFO [main] (UserMapperTest.java:669) - sqlSession close successfully

set 作用及用法

作用

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

用法

我们来改下动态SQL if 元素中的例子跳转到改示例

修改之前的SQL如下

<update id="updateSysUserByIdSelective">
        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>

使用set改造后的SQL如下

<update id="updateSysUserByIdWithSetSelective">
        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>
                    <!-- 如果set后面的字符串是以逗号结尾的，就将这个逗号剔除,加个逗号演示下 -->
                    id = #{id},
            </set>
            where id = #{id}
    </update>

增加接口方法

/**
     * 
     * 
     * @Title: updateSysUserByIdWithSetSelective
     * 
     * @Description: 根据主键更新SysUser
     * 
     * @param sysUser
     * @return
     * 
     * @return: int
     */
    int updateSysUserByIdWithSetSelective(SysUser sysUser);

单元测试

@Test
    public void updateSysUserByIdWithSetSelectiveTest() {
        logger.info("updateSysUserByIdWithSetSelective");
        // 获取SqlSession
        SqlSession sqlSession = getSqlSession();
        try {
            // 获取UserMapper接口
            UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
            // 先根据ID查询出对应的sysuser
            SysUser sysUser = userMapper.selectSysUserById((long) 1);
            // 当前数据库用户的userName期望为admin
            Assert.assertEquals("admin", sysUser.getUserName());
            // 修改用户名
            sysUser.setUserName("dynamicUpdate");
            // 修改邮件
            sysUser.setUserEmail("dynamicUpdate@artisan.com");
            // 修改用户 ,返回受影响的行数
            int result = userMapper.updateSysUserByIdWithSetSelective(sysUser);
            // 只插入一条数据 ,期望是1
            Assert.assertEquals(1, result);
            logger.info("受影响的行数:" + result);
            // 重新查询（虽然未提交但是在一个会话中）
            sysUser = userMapper.selectSysUserById((long) 1);
            // 期望的用户名为dynamicUpdate
            Assert.assertEquals("dynamicUpdate", sysUser.getUserName());
            // 期望的邮箱为dynamicUpdate@artisan.com
            Assert.assertEquals("dynamicUpdate@artisan.com", sysUser.getUserEmail());
            // 检查其他字段有没有被更新为null 或者 空值
            Assert.assertEquals("123456", sysUser.getUserPassword());
            Assert.assertEquals("管理员用户", sysUser.getUserInfo());
            logger.info(sysUser);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            // 为了保持测试数据的干净，这里选择回滚
            // 由于默认的sqlSessionFactory.openSession()是不自动提交的
            // 除非显式的commit，否则不会提交到数据库
            sqlSession.rollback();
            logger.info("为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成");
            sqlSession.close();
            logger.info("sqlSession close successfully ");
        }
    }

日志

2018-04-21 21:30:34,774  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-21 21:30:34,779  INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-21 21:30:34,783  INFO [main] (UserMapperTest.java:675) - updateSysUserByIdWithSetSelective
2018-04-21 21:30:35,425 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ? 
2018-04-21 21:30:35,509 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long)
2018-04-21 21:30:35,551 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
2018-04-21 21:30:35,552 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:30:35,560 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-21 21:30:35,621 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: update sys_user SET user_name = ?, user_password = ?, user_email = ?, user_info = ?, head_img = ?, create_time = ?, id = ? where id = ? 
2018-04-21 21:30:35,627 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: dynamicUpdate(String), 123456(String), dynamicUpdate@artisan.com(String), 管理员用户(String), java.io.ByteArrayInputStream@1969526c(ByteArrayInputStream), 2018-04-13 21:12:47.0(Timestamp), 1(Long), 1(Long)
2018-04-21 21:30:35,629 DEBUG [main] (BaseJdbcLogger.java:145) - <==    Updates: 1
2018-04-21 21:30:35,629  INFO [main] (UserMapperTest.java:696) - 受影响的行数:1
2018-04-21 21:30:35,630 DEBUG [main] (BaseJdbcLogger.java:145) - ==>  Preparing: select a.id, a.user_name, a.user_password, a.user_email, a.user_info, a.head_img, a.create_time from sys_user a where id = ? 
2018-04-21 21:30:35,631 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1(Long)
2018-04-21 21:30:35,632 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, user_name, user_password, user_email, user_info, head_img, create_time
2018-04-21 21:30:35,633 TRACE [main] (BaseJdbcLogger.java:151) - <==        Row: 1, dynamicUpdate, 123456, dynamicUpdate@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 21:30:35,636 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
2018-04-21 21:30:35,637  INFO [main] (UserMapperTest.java:707) - SysUser [id=1, userName=dynamicUpdate, userPassword=123456, userEmail=dynamicUpdate@artisan.com, userInfo=管理员用户, headImg=[18, 49, 35, 18, 48], createTime=Fri Apr 13 21:12:47 BOT 2018]
2018-04-21 21:30:35,644  INFO [main] (UserMapperTest.java:716) - 为了保持测试数据的干净，这里选择回滚,不写入mysql,请观察日志，回滚完成
2018-04-21 21:30:35,646  INFO [main] (UserMapperTest.java:719) - sqlSession close successfully

trim 作用及用法

where 和 set 标签的功能都可以用trim标签实现，并且在底层就是通过TrimSqlNode 实现的。

where 标签对应的trim的实现如下

<trim prefix="WHERE" prefixOverrides="ADN |OR ">
....
</trim>

这里”ADN |OR ” 后面的空格不能省略，为了避免匹配到 andes 、orders 等单词。

set 标签对应的trim的实现如下

<trim prefix="SET" suffixOverrides=",">
....
</trim>

trim标签属性

属性名	含义
prefix	当trim元素包含内容时，会给内容增加prefix指定的前缀
prefixOverrides	当trim元素包含内容时，会把内容中匹配的前缀字符串去掉

suffix	当trim元素包含内容时，会给内容增加suffix指定的后缀
suffixOverrides	当trim元素包含内容时，会把内容中匹配的后缀字符串去掉

MyBatis-13MyBatis动态SQL之【where、set、trim】

概述

where 作用及用法

作用

用法

set 作用及用法

作用

用法

trim 作用及用法

热门文章

最新文章

相关课程

相关电子书

相关实验场景