一 创建一个Service
public interface userService extends IService<User>{
User selectUserByIdOrByUsername(User user);
}
二 创建实现类
@Slf4j
@Service
public class userServiceImpl extends ServiceImpl<userMapper, User> implements userService {
@Override
public User selectSysUserByIdOrByUserName(User user) {
return userMapper.selectSysUserByIdOrByUserName(user);
}
}
三 添加xml
<select id="selectSysUserByIdOrByUserName" resultType="com.central.user.model.user">
SELECT *
FROM user u
WHERE
1=1
<choose>
<when test="userId != null">
and user_id = #{userId}
</when>
<when test="userName != null">
and user_name = #{userName}
</when>
<otherwise>
and 1=2
</otherwise>
</choose>
</select>
四 测试类
@Test
public void selectSysUserByIdOrByUserNameTest() {
log.info("selectSysUserByIdOrByUserNameTest");
// 获取SqlSession
//SqlSession sqlSession = getSqlSession();
try {
log.info("======. 模拟传入了正确的id 没有传username或者传入了错误的userName======");
// 1. 模拟传入了正确的id 没有传username或者传入了错误的userName
Tengyu_user tengyuUser = new Tengyu_user();
tengyuUser.setUserId(1);
tengyuUser.setUserName("admin");
// 调用selectSysUserByIdOrByUserName,查询单个用户
Tengyu_user user = tengyu_userService.selectSysUserByIdOrByUserName(tengyuUser);
// 期望不为空
Assert.assertNotNull(user);
// userName 期望值为artisan
Assert.assertEquals("admin", user.getUserName());
log.info("user", user);
log.info("======2. 模拟不传id,但是传入了正确的 userName======");
// 2. 模拟不传id,但是传入了正确的 userName
tengyuUser = new Tengyu_user();
tengyuUser.setUserId(null);
tengyuUser.setUserName("admin");
user = tengyu_userService.selectSysUserByIdOrByUserName(tengyuUser);
// 期望不为空
Assert.assertNotNull(user);
// 根据id查询 sysuser,然后获取userName 期望值为artisan
Assert.assertEquals("admin", user.getUserName());
log.info("user", user);
log.info("======3.什么都不传======");
// 2. 模拟不传id,但是传入了正确的 userName
tengyuUser = new Tengyu_user();
tengyuUser.setUserId(null);
tengyuUser.setUserName(null);
user = tengyu_userService.selectSysUserByIdOrByUserName(tengyuUser);
// 期望为空
Assert.assertNull(user);
log.info("user", user);
} catch (Exception e) {
e.printStackTrace();
} finally {
//sqlSession.close();
log.info("sqlSession close successfully ");
}
}
五 查看结果
六 总结
使用choose when otherwise的时候逻辑要严密,避免由于某些值出现问题导致SQL出错。
在上述查询中,如果没有otherwise这个条件限制,当userId和userName都为空时,所有的用户都会被查询出来,添加otherwise后,由于where 后的条件不满足,因此这种这种情况下查不到结果。