Mybatis之动态SQL choose(when、otherwise)使用

 

choose(when、otherwise)

与每一个case都带break的switch语句相似，满足一种即跳出：

下面进行代码演示：

首先创建mapper.xml接口:

//查询用户列表(choose)
	public List<User> getUserList_choose(@Param("userName")String userName,
										 @Param("userRole")Integer roleId,
										 @Param("userCode")String userCode,
										 @Param("creationDate")Date creationDate);

mapper.xml创建select语句：

<!-- 查询用户列表(choose) -->
	<select id="getUserList_choose" resultType="User">
		select * from smbms_user where 1=1
			<choose>
				<when test="userName != null and userName != ''">
					and userName like CONCAT ('%',#{userName},'%')
				</when>
				<when test="userCode != null and userCode != ''">
					and userCode like CONCAT ('%',#{userCode},'%')
				</when>
				<when test="userRole != null">
					and userRole=#{userRole}
				</when>
				<otherwise>
					<!-- and YEAR(creationDate) = YEAR(NOW()) -->
					and YEAR(creationDate) = YEAR(#{creationDate})
				</otherwise>
			</choose>
	</select>

测试类：

	@Test
	public void testGetUserList_choose(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			String userName = "";
			Integer roleId = null;
			String userCode = "";
			Date creationDate = new SimpleDateFormat("yyyy-MM-dd").parse("2017-01-01");
			userList = sqlSession.getMapper(UserMapper.class).getUserList_choose(userName,roleId,userCode,creationDate);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userlist.size ----> " + userList.size());
		for(User user: userList){
			logger.debug("testGetUserList_choose=======> id: " + user.getId() +
						" and userCode: " + user.getUserCode() + 
						" and userName: " + user.getUserName() + 
						" and userRole: " + user.getUserRole() + 
						" and creationDate: " + new SimpleDateFormat("yyyy-MM-dd").format(user.getCreationDate()));
		}
	}

可以看到结果没有进行时间查询，仅根据roleId进行查询。