• 概述
• 需求
• 實(shí)現(xiàn)步驟
  • 1.UserMapper接口中添加接口方法
  • 2.UserMapper.xml中添加動(dòng)態(tài)SQL
  • 3.單元測(cè)試

概述

我們?cè)趇f標(biāo)簽的博文MyBatis-11MyBatis動(dòng)態(tài)SQL之【if】
中介紹了if的在select、update、delete中的用法，可以實(shí)現(xiàn)基本的條件判斷， 但是卻無(wú)法實(shí)現(xiàn) if…else ,if ….else….的邏輯。

想要實(shí)現(xiàn) if…else ,if ….else….這樣的邏輯 就需要用到choose when otherwise標(biāo)簽了。

choose元素中包含了when和otherwise兩個(gè)標(biāo)簽 ，一個(gè)choose中至少有一個(gè)when，有0個(gè)或者1個(gè)otherwise

---

需求

前置條件： sys_user表中，除了id是主鍵外，我們認(rèn)為user_name也是唯一的，用戶名不可重復(fù)。

假設(shè)有個(gè)這樣的需求， 實(shí)現(xiàn)如下如下邏輯

• 當(dāng)參數(shù)id有值時(shí)優(yōu)先使用id查詢
• 當(dāng)沒(méi)有id時(shí)就去判斷用戶名是否有值，如果有值就根據(jù)用戶名全配
• 如果用戶名也沒(méi)有值，就是sql查詢?yōu)榭?/li>

---

實(shí)現(xiàn)步驟

1.UserMapper接口中添加接口方法

/*** * * @Title: selectSysUserByIdOrByUserName* * @Description: 根據(jù)用戶id或者用戶名查詢用戶* * @param sysUser* @return* * @return: SysUser*/SysUser selectSysUserByIdOrByUserName(SysUser sysUser);

---

2.UserMapper.xml中添加動(dòng)態(tài)SQL

<select id="selectSysUserByIdOrByUserName" resultType="com.artisan.mybatis.xml.domain.SysUser">SELECTa.id,a.user_name userName,a.user_password userPassword,a.user_email userEmail,a.user_info userInfo,a.head_img headImg,a.create_time createTimeFROMsys_user aWHERE 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>

使用choose when otherwise的時(shí)候邏輯要嚴(yán)密，避免由于某些值出現(xiàn)問(wèn)題導(dǎo)致SQL出錯(cuò)。

在上述查詢中，如果沒(méi)有otherwise這個(gè)條件限制，當(dāng)id和username都為空時(shí)，所有的用戶都會(huì)被查詢出來(lái)，但是我們?cè)趯?duì)引得接口方法中使用了SysUser作為返回值，所以當(dāng)實(shí)際查詢到多個(gè)結(jié)果時(shí)就會(huì)報(bào)錯(cuò)， 添加otherwise后，由于where 后的條件不滿足，因此這種這種情況下查不到結(jié)果。

---

3.單元測(cè)試

@Testpublic void selectSysUserByIdOrByUserNameTest() {logger.info("selectSysUserByIdOrByUserNameTest");// 獲取SqlSessionSqlSession sqlSession = getSqlSession();try {// 獲取UserMapper接口UserMapper userMapper = sqlSession.getMapper(UserMapper.class);logger.info("======. 模擬傳入了正確的id 沒(méi)有傳username或者傳入了錯(cuò)誤的userName======");// 1. 模擬傳入了正確的id 沒(méi)有傳username或者傳入了錯(cuò)誤的userNameSysUser sysUser = new SysUser();sysUser.setId(1001L);sysUser.setUserName("noExistName");// 調(diào)用selectSysUserByIdOrByUserName,查詢單個(gè)用戶SysUser user = userMapper.selectSysUserByIdOrByUserName(sysUser);// 期望不為空Assert.assertNotNull(user);// userName 期望值為artisanAssert.assertEquals("artisan", user.getUserName());logger.info(user);logger.info("======2. 模擬不傳id,但是傳入了正確的 userName======");// 2. 模擬不傳id,但是傳入了正確的 userNamesysUser = new SysUser();sysUser.setId(null);sysUser.setUserName("admin");user = userMapper.selectSysUserByIdOrByUserName(sysUser);// 期望不為空Assert.assertNotNull(user);// 根據(jù)id查詢 sysuser,然后獲取userName 期望值為artisanAssert.assertEquals("admin", user.getUserName());logger.info(user);logger.info("======3.什么都不傳======");// 2. 模擬不傳id,但是傳入了正確的 userNamesysUser = 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 ");}}

日志

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 沒(méi)有傳username或者傳入了錯(cuò)誤的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=測(cè)試用戶, 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

注意觀察每個(gè)條件匹配的SQL語(yǔ)句

---

總結(jié)

以上是生活随笔為你收集整理的MyBatis-12MyBatis动态SQL之【choose when otherwise】的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。