概述

我们在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接口中添加接口方法

  1. /**
  2.      * 
  3.      * 
  4.      * @Title: selectSysUserByIdOrByUserName
  5.      * 
  6.      * @Description: 根据用户id或者用户名查询用户
  7.      * 
  8.      * @param sysUser
  9.      * @return
  10.      * 
  11.      * @return: SysUser
  12.      */
  13.     SysUser selectSysUserByIdOrByUserName(SysUser sysUser);

2.UserMapper.xml中添加动态SQL

  1.         <select id="selectSysUserByIdOrByUserName" resultType="com.artisan.mybatis.xml.domain.SysUser">
  2.         SELECT
  3.             a.id,
  4.             a.user_name userName,
  5.             a.user_password userPassword,
  6.             a.user_email userEmail,
  7.             a.user_info userInfo,
  8.             a.head_img headImg,
  9.             a.create_time createTime
  10.         FROM
  11.             sys_user a
  12.         WHERE 1=1
  13.         <choose>
  14.             <when test="id != null ">
  15.                 and id = #{id}
  16.             </when>
  17.             <when test="userName!= null ">
  18.                 and user_name = #{userName}
  19.             </when>
  20.             <otherwise>
  21.                 and 1 = 2
  22.             </otherwise>
  23.         </choose>
  24.     </select>

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

3.单元测试

  1. @Test
  2. public void selectSysUserByIdOrByUserNameTest() {
  3.     logger.info("selectSysUserByIdOrByUserNameTest");
  4.     // 获取SqlSession
  5.     SqlSession sqlSession = getSqlSession();
  6.     try {
  7.         // 获取UserMapper接口
  8.         UserMapper userMapper = sqlSession.getMapper(UserMapper.class);
  10.         logger.info("======. 模拟传入了正确的id 没有传username或者传入了错误的userName======");
  11.         // 1. 模拟传入了正确的id 没有传username或者传入了错误的userName
  12.         SysUser sysUser = new SysUser();
  13.         sysUser.setId(1001L);
  14.         sysUser.setUserName("noExistName");
  15.         // 调用selectSysUserByIdOrByUserName,查询单个用户
  16.         SysUser user = userMapper.selectSysUserByIdOrByUserName(sysUser);
  17.         // 期望不为空
  18.         Assert.assertNotNull(user);
  19.         // userName 期望值为artisan
  20.         Assert.assertEquals("artisan", user.getUserName());
  21.         logger.info(user);
  23.         logger.info("======2. 模拟不传id,但是传入了正确的 userName======");
  24.         // 2. 模拟不传id,但是传入了正确的 userName
  25.         sysUser = new SysUser();
  26.         sysUser.setId(null);
  27.         sysUser.setUserName("admin");
  28.         user = userMapper.selectSysUserByIdOrByUserName(sysUser);
  29.         // 期望不为空
  30.         Assert.assertNotNull(user);
  31.         // 根据id查询 sysuser,然后获取userName 期望值为artisan
  32.         Assert.assertEquals("admin", user.getUserName());
  33.         logger.info(user);
  35.         logger.info("======3.什么都不传======");
  36.         // 2. 模拟不传id,但是传入了正确的 userName
  37.         sysUser = new SysUser();
  38.         sysUser.setId(null);
  39.         sysUser.setUserName(null);
  40.         user = userMapper.selectSysUserByIdOrByUserName(sysUser);
  41.         // 期望为空
  42.         Assert.assertNull(user);
  43.         logger.info(user);
  45.     } catch (Exception e) {
  46.         e.printStackTrace();
  47.     } finally {
  48.         sqlSession.close();
  49.         logger.info("sqlSession close successfully ");
  50.     }
  52. }

日志

  1. 2018-04-21 19:46:51,942  INFO [main] (BaseMapperTest.java:26) - sessionFactory bulit successfully
  2. 2018-04-21 19:46:51,947  INFO [main] (BaseMapperTest.java:29) - reader close successfully
  3. 2018-04-21 19:46:51,951  INFO [main] (UserMapperTest.java:541) - selectSysUserByIdOrByUserNameTest
  4. 2018-04-21 19:46:51,979  INFO [main] (UserMapperTest.java:548) - ======. 模拟传入了正确的id 没有传username或者传入了错误的userName======
  5. 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 = ? 
  6. 2018-04-21 19:46:52,647 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 1001(Long)
  7. 2018-04-21 19:46:52,685 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
  8. 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
  9. 2018-04-21 19:46:52,694 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
  10. 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]
  11. 2018-04-21 19:46:52,699  INFO [main] (UserMapperTest.java:561) - ======2. 模拟不传id,但是传入了正确的 userName======
  12. 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 = ? 
  13. 2018-04-21 19:46:52,701 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: admin(String)
  14. 2018-04-21 19:46:52,702 TRACE [main] (BaseJdbcLogger.java:151) - <==    Columns: id, userName, userPassword, userEmail, userInfo, headImg, createTime
  15. 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
  16. 2018-04-21 19:46:52,703 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 1
  17. 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]
  18. 2018-04-21 19:46:52,706  INFO [main] (UserMapperTest.java:573) - ======3.什么都不传======
  19. 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 
  20. 2018-04-21 19:46:52,707 DEBUG [main] (BaseJdbcLogger.java:145) - ==> Parameters: 
  21. 2018-04-21 19:46:52,709 DEBUG [main] (BaseJdbcLogger.java:145) - <==      Total: 0
  22. 2018-04-21 19:46:52,709  INFO [main] (UserMapperTest.java:581) - 
  23. 2018-04-21 19:46:52,712  INFO [main] (UserMapperTest.java:587) - sqlSession close successfully