xml里mysql数据库的if else标签

最新推荐文章于 2025-04-15 15:18:53 发布

原创最新推荐文章于 2025-04-15 15:18:53 发布 · 1.8k 阅读

CC 4.0 BY-SA版权

本文介绍了如何在MyBatis中使用choose标签进行条件选择查询，通过一个UserService接口及其实现类展示了根据userId和userName查询User的方法。测试案例涵盖了不同参数组合的情况，强调了otherwise在确保SQL逻辑正确性上的作用，防止查询结果出错。

一创建一个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 ");
        }

    }