MyBatis-12MyBatis动态SQL之【choose when otherwise】
【摘要】
概述需求实现步骤
1.UserMapper接口中添加接口方法2.UserMapper.xml中添加动态SQL3.单元测试
概述
我们在if标签的博文MyBatis...
概述
我们在if标签的博文MyBatis-11MyBatis动态SQL之【if】
中介绍了if的在select、update、delete中的用法，可以实现基本的条件判断， 但是却无法实现 if…else ,if ….else….的逻辑。
想要实现 if…else ,if ….else….这样的逻辑 就需要用到choose when otherwise标签了。
choose元素中包含了when和otherwise两个标签 ，一个choose中至少有一个when，有0个或者1个otherwise
需求
前置条件： sys_user表中，除了id是主键外，我们认为user_name也是唯一的，用户名不可重复。
假设有个这样的需求， 实现如下如下逻辑
- 当参数id有值时优先使用id查询
- 当没有id时就去判断用户名是否有值，如果有值就根据用户名全配
- 如果用户名也没有值，就是sql查询为空
实现步骤
1.UserMapper接口中添加接口方法
/**
*
*
* @Title: selectSysUserByIdOrByUserName
*
* @Description: 根据用户id或者用户名查询用户
*
* @param sysUser
* @return
*
* @return: SysUser
*/
SysUser selectSysUserByIdOrByUserName(SysUser sysUser);
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
2.UserMapper.xml中添加动态SQL
<select id="selectSysUserByIdOrByUserName" 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
<choose>
<when test="id != null ">
and id = #{id}
</when>
<when test="userName!= null ">
and user_name = #{userName}
</when>
<otherwise>
and 1 = 2
</otherwise>
</choose>
</select>
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
使用choose when otherwise的时候逻辑要严密，避免由于某些值出现问题导致SQL出错。
在上述查询中，如果没有otherwise这个条件限制，当id和username都为空时，所有的用户都会被查询出来，但是我们在对引得接口方法中使用了SysUser作为返回值，所以当实际查询到多个结果时就会报错， 添加otherwise后，由于where 后的条件不满足，因此这种这种情况下查不到结果。
3.单元测试
@Test
public void selectSysUserByIdOrByUserNameTest() {
logger.info("selectSysUserByIdOrByUserNameTest");
// 获取SqlSession
SqlSession sqlSession = getSqlSession();
try {
// 获取UserMapper接口
UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
logger.info("======. 模拟传入了正确的id 没有传username或者传入了错误的userName======");
// 1. 模拟传入了正确的id 没有传username或者传入了错误的userName
SysUser sysUser = new SysUser();
sysUser.setId(1001L);
sysUser.setUserName("noExistName");
// 调用selectSysUserByIdOrByUserName,查询单个用户
SysUser user = userMapper.selectSysUserByIdOrByUserName(sysUser);
// 期望不为空
Assert.assertNotNull(user);
// userName 期望值为artisan
Assert.assertEquals("artisan", user.getUserName());
logger.info(user);
logger.info("======2. 模拟不传id,但是传入了正确的 userName======");
// 2. 模拟不传id,但是传入了正确的 userName
sysUser = new SysUser();
sysUser.setId(null);
sysUser.setUserName("admin");
user = userMapper.selectSysUserByIdOrByUserName(sysUser);
// 期望不为空
Assert.assertNotNull(user);
// 根据id查询 sysuser,然后获取userName 期望值为artisan
Assert.assertEquals("admin", user.getUserName());
logger.info(user);
logger.info("======3.什么都不传======");
// 2. 模拟不传id,但是传入了正确的 userName
sysUser = new SysUser();
sysUser.setId(null);
sysUser.setUserName(null);
user = userMapper.selectSysUserByIdOrByUserName(sysUser);
// 期望为空
Assert.assertNull(user);
logger.info(user);
} catch (Exception e) {
e.printStackTrace();
} finally {
sqlSession.close();
logger.info("sqlSession close successfully ");
}
}
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
- 25
- 26
- 27
- 28
- 29
- 30
- 31
- 32
- 33
- 34
- 35
- 36
- 37
- 38
- 39
- 40
- 41
- 42
- 43
- 44
- 45
- 46
- 47
- 48
- 49
- 50
- 51
- 52
- 53
日志
2018-04-21 19:46:51,942 INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
2018-04-21 19:46:51,947 INFO [main] (BaseMapperTest.java:29) - reader close successfully
2018-04-21 19:46:51,951 INFO [main] (UserMapperTest.java:541) - selectSysUserByIdOrByUserNameTest
2018-04-21 19:46:51,979 INFO [main] (UserMapperTest.java:548) - ======. 模拟传入了正确的id 没有传username或者传入了错误的userName======
2018-04-21 19:46:52,569 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 1=1 and id = ?
2018-04-21 19:46:52,647 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1001(Long)
2018-04-21 19:46:52,685 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 19:46:52,686 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1001, artisan, 123456, test@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 19:46:52,694 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 19:46:52,695 INFO [main] (UserMapperTest.java:559) - 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 19:46:52,699 INFO [main] (UserMapperTest.java:561) - ======2. 模拟不传id,但是传入了正确的 userName======
2018-04-21 19:46:52,700 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 1=1 and user_name = ?
2018-04-21 19:46:52,701 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: admin(String)
2018-04-21 19:46:52,702 TRACE [main] (BaseJdbcLogger.java:151) - <== Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
2018-04-21 19:46:52,702 TRACE [main] (BaseJdbcLogger.java:151) - <== Row: 1, admin, 123456, admin@artisan.com, <<BLOB>>, <<BLOB>>, 2018-04-13 21:12:47.0
2018-04-21 19:46:52,703 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 1
2018-04-21 19:46:52,706 INFO [main] (UserMapperTest.java:571) - 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 19:46:52,706 INFO [main] (UserMapperTest.java:573) - ======3.什么都不传======
2018-04-21 19:46:52,707 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 1=1 and 1 = 2
2018-04-21 19:46:52,707 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters:
2018-04-21 19:46:52,709 DEBUG [main] (BaseJdbcLogger.java:145) - <== Total: 0
2018-04-21 19:46:52,709 INFO [main] (UserMapperTest.java:581) -
2018-04-21 19:46:52,712 INFO [main] (UserMapperTest.java:587) - sqlSession close successfully
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
- 11
- 12
- 13
- 14
- 15
- 16
- 17
- 18
- 19
- 20
- 21
- 22
- 23
- 24
注意观察每个条件匹配的SQL语句
文章来源: artisan.blog.csdn.net，作者：小小工匠，版权归原作者所有，如需转载，请联系作者。
原文链接：artisan.blog.csdn.net/article/details/80035677
【版权声明】本文为华为云社区用户转载文章，如果您发现本社区中有涉嫌抄袭的内容，欢迎发送邮件进行举报，并提供相关证据，一经查实，本社区将立刻删除涉嫌侵权内容，举报邮箱：
cloudbbs@huaweicloud.com
- 点赞
- 收藏
- 关注作者
评论（0）