Mybatis之动态SQL foreach使用

foreach

1、根据用户角色列表，获取该角色列表下用户列表信息-foreach_array用法：

Mapper.java接口中新增方法：

//根据用户角色列表，获取该角色列表下用户列表信息-foreach_array
	public List<User> getUserByRoleId_foreach_array(Integer[] roleIds);

Mapper.xml：

	<resultMap type="User" id="userMapByRole">
		<id property="id" column="id"/>
		<result property="userCode" column="userCode"/>
		<result property="userName" column="userName"/>
	</resultMap>
	<select id="getUserByRoleId_foreach_array" resultMap="userMapByRole">
		select * from smbms_user where userRole in
		<foreach collection="array" item="roleIds" 
		open="(" separator="," close=")">
		#{roleIds}
		</foreach>
	</select>

注：其中resultMap为后面测试公用映射

test测试类：

@Test
	public void testGetUserByRoleId_foreach_array(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		Integer[] roleIds = {2,3};
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_array(roleIds);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", userRole: " + user.getUserRole());
		}
	}

2、根据用户角色列表，获取该角色列表下用户列表信息-foreach_list用法

Mapper.java接口中新增方法：

// 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list
	public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);

Mapper.xml：

<!-- 根据用户角色列表，获取该角色列表下用户列表信息-foreach_list -->
	<select id="getUserByRoleId_foreach_list" resultMap="userMapByRole">
		select * from smbms_user where userRole in 
			<foreach collection="list" item="roleList" open="(" separator="," close=")">
				#{roleList}
			</foreach>
	</select>

test测试类：

@Test
	public void testGetUserByRoleId_foreach_list(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		List<Integer> roleList = new ArrayList<Integer>();
		roleList.add(2);
		roleList.add(3);
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_list(roleList);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", userRole: " + user.getUserRole());
		}
	}

3、根据用户角色列表和性别(多参数)，获取该角色列表下指定性别的用户列表信息-foreach_map用法

mapper.java接口:

	// 根据用户角色列表和性别(多参数)，获取该角色列表下指定性别的用户列表信息-foreach_map
	public List<User> getUserByConditionMap_foreach_map(Map<String,Object> conditionMap);

mapper.xml:

<!-- 根据用户角色列表和性别(多参数)，获取该角色列表下并指定性别的用户列表信息-foreach_map -->
	<select id="getUserByConditionMap_foreach_map" resultMap="userMapByRole">
		select * from smbms_user where gender = #{gender} and userRole in 
			<foreach collection="roleIds" item="roleMap" open="(" separator="," close=")">
				#{roleMap}
			</foreach>
	</select>

测试类：

@Test
	public void testGetUserByConditionMap_foreach_map(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		Map<String, Object> conditionMap = new HashMap<String,Object>();
		List<Integer> roleList = new ArrayList<Integer>();
		roleList.add(2);
		roleList.add(3);
		conditionMap.put("gender", 1);
		conditionMap.put("roleIds",roleList);
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByConditionMap_foreach_map(conditionMap);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", gender: " + user.getGender() +
						", userRole: " + user.getUserRole());
		}
	}

4、根据用户角色列表，获取该角色列表下用户列表信息-foreach_map(单参数封装成map)用法

mapper.java接口

	// 根据用户角色列表，获取该角色列表下用户列表信息-foreach_map(单参数封装成map)
	public List<User> getUserByRoleId_foreach_map(Map<String,Object> roleMap);

mapper.xml:

<!-- 根据用户角色列表(单参数)，获取该角色列表下用户列表信息-foreach_map -->
	<select id="getUserByRoleId_foreach_map" resultMap="userMapByRole">
		select * from smbms_user where userRole in 
			<foreach collection="rKey" item="roleMap" open="(" separator="," close=")">
				#{roleMap}
			</foreach>
	</select>

测试类：

@Test
	public void testGetUserByRoleId_foreach_map(){
		SqlSession sqlSession = null;
		List<User> userList = new ArrayList<User>();
		List<Integer> roleList = new ArrayList<Integer>();
		roleList.add(2);
		roleList.add(3);
		Map<String, Object> roleMap = new HashMap<String,Object>();
		roleMap.put("rKey", roleList);
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_map(roleMap);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}finally{
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		logger.debug("userList.size ----> " + userList.size());
		for(User user : userList){
			logger.debug("user ===========> id: " + user.getId()+
						", userCode: " + user.getUserCode() + 
						", userName: " + user.getUserName() +
						", userRole: " + user.getUserRole());
		}
	}