- 概述
- where 作用及用法
- 作用
- 用法
- set 作用及用法
- 作用
- 用法
- trim 作用及用法
概述
where set trim 这3个标签解决了类似的问题，并且where 和 set 都属于trim的一种具体用法，下面我们通过具体的示例来学习下。
where 作用及用法
作用
作用：如果该标签包含的元素中有返回值，就插入一个where ，如果where后面的字符串以AND 和 OR 开头的，就将他们剔除。
用法
我们使用动态SQL if 元素中的例子 ，点击跳转到该示例
不使用where元素的SQL如下：
代码语言：javascript复制"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>我们在这里通过where元素来实现一遍
代码语言：javascript复制"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
<if test="userName != null and userName != '' ">
and user_name like concat('%',#{userName},'%')
if>
<if test="userEmail != null and userEmail != '' ">
and user_email = #{userEmail}
if>当if 条件都不满足的时候where元素中没有内容，所以SQL中不会出现Where。 如果if条件满足，where元素的内容就是以and开头的条件，where会自动去掉开头的and ,这也能保证where条件的正确。 相比上个案例，这种情况下生成的SQL更干净，不会在任何情况下都有where 1 = 1 这样的条件。
增加接口方法
代码语言：javascript复制/**
*
*
* @Title: selectSysUsersAdvancedWithWhere
*
* @Description: selectSysUsersAdvancedWithWhere
*
* @param sysUser
* @return
*
* @return: List
*/
List selectSysUsersAdvancedWithWhere(SysUser sysUser);单元测试
代码语言：javascript复制@Test
public void selectSysUsersAdvancedWithWhere() {
logger.info("selectSysUsersAdvancedWithWhere");
// 获取SqlSession
SqlSession sqlSession = getSqlSession();
List 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 ");
}
}日志
代码语言：javascript复制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, <>, <>, 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, <>, <>, 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, <>, <>, 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, <>, <>, 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如下
代码语言：javascript复制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}使用set改造后的SQL如下
代码语言：javascript复制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}增加接口方法
代码语言：javascript复制/**
*
*
* @Title: updateSysUserByIdWithSetSelective
*
* @Description: 根据主键更新SysUser
*
* @param sysUser
* @return
*
* @return: int
*/
int updateSysUserByIdWithSetSelective(SysUser sysUser);单元测试
代码语言：javascript复制@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 ");
}
}日志
代码语言：javascript复制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, <>, <>, 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, <>, <>, 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的实现如下
代码语言：javascript复制<trim prefix="WHERE" prefixOverrides="ADN |OR ">
....
trim>这里”ADN |OR ” 后面的空格不能省略，为了避免匹配到 andes 、orders 等单词。
set 标签对应的trim的实现如下
代码语言：javascript复制<trim prefix="SET" suffixOverrides=",">
....
trim>trim标签属性
属性名 | 含义 |
|---|---|
prefix | 当trim元素包含内容时，会给内容增加prefix指定的前缀 |
prefixOverrides | 当trim元素包含内容时，会把内容中匹配的前缀字符串去掉 |
suffix | 当trim元素包含内容时，会给内容增加suffix指定的后缀 |
suffixOverrides | 当trim元素包含内容时，会把内容中匹配的后缀字符串去掉 |
