SSM（九）使用foreach完成复杂查询

本文链接：https://blog.youkuaiyun.com/Ada_yangyang/article/details/84797695

一、学习目标

掌握foreach的使用

掌握choose（when、otherwise）的使用

理解MyBatis的入参处理机制

二、foreach

迭代一个集合，通常用于in条件

属性：

item：表示集合里每一个元素进行迭代时的别名
index：迭代过程中的位置，可省略
collection:必须指定，指定属性的值

list：入参的单参数类型为List

array：入参是数组

map-key：入参封装成map

open：这个语句以什么为开始
separator：迭代之间以什么为间隔
close：语句以什么结束

三、需求：指定用户角色（1-n个），获取这些用户角色下的用户列表信息

查询SQL与含有in条件

select * from smbms_user where userRole in (参数1, 参数2, 参数3);

使用foreach实现：

参数：用户角色列表

参数类型：

数组
list

四、array

UserMapper.java


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

UserMapper.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>

UserMapperTest.java


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

五、list

UserMapper.java

public List<User> getUserByRoleId_foreach_list(List<Integer> roleList);

UserMapper.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_list" resultMap="userMapByRole">
		select * from smbms_user where userRole in
		<foreach collection="list" item="roleList" open="(" separator="," close=")">
			#{roleIds}
		</foreach>
	</select>

UserMapperTest.java


	@Test
	public void testGetUserByRoleId_foreach_list() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		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) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		for(User _user:userList) {
			logger.debug("testGetUserByRoleId_foreach_list userCode: " + _user.getUserCode()
					+ " and userName: " + _user.getUserName());
		}
	}

六、需求：在上一个例子中，增加一个参数：gender，要求查询出指定性别和用户角色列表下的用户列表。

多参数入参，封装成Map

UserMapper.java

public List<User> getUserByConditionMap_foreach_map(Map<String, Object> conditionMap);

UserMapper.xml


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

UserMapperTest.java

@Test
	public void testGetUserByConditionMap_foreach_map() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		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) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		for(User _user:userList) {
			logger.debug("testGetUserByRoleId_foreach_list userCode: " + _user.getUserCode()
					+ " and userName: " + _user.getUserName());
		}
	}

七、单参数也可以封装成Map吗？

实际上，MyBatis会把参数封装成Map进行入参

参数名：Map的key

参数值：Map的value

UserMapper.java

public List<User> getUserByRoleId_foreach_map(Map<String, Object> roleMap);

UserMapper.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_map" resultMap="userMapByRole">
	select * from smbms_user where userRole in
	<foreach collection="roleKey" item="key" open="(" separator="," close=")">
		#{key}
	</foreach>
</select>

UserMapperTest.java

@Test
	public void testGetUserByRoleId_foreach_map() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		Map<String, Object> roleMap = new HashMap<String, Object>();
		List<Integer> roleList = new ArrayList<Integer>();
		roleList.add(2);
		roleList.add(3);
		roleMap.put("roleKey", roleList);
		try {
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserByRoleId_foreach_map(roleMap);
		} catch(Exception e) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		for(User _user:userList) {
			logger.debug("testGetUserByRoleId_foreach_list userCode: " + _user.getUserCode()
					+ " and userName: " + _user.getUserName());
		}
	}

八、choose（when、otherwise）

相当于Java中的switch

当有条件满足when时，跳出choose

UserMapper.java

public List<User> getUserList_choose(@Param("userName")String userName,
	 @Param("userCode")String userCode,
	 @Param("userRole")Integer userRole,
	 @Param("creationDate")Date creationDate);

UserMapper.xml

<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(#{creationDate})
			</otherwise>
		</choose>
	</select>

UserMapperTest.java

@Test
	public void testGetUserList_choose() {
		List<User> userList = new ArrayList<User>();
		SqlSession sqlSession = null;
		String userName = "";
		String userCode = "";
		Integer userRole = 1;
		SimpleDateFormat format = new SimpleDateFormat("yyyy-MM-dd");
		try {
			Date creationDate = format.parse("2018-1-1");
			sqlSession = MyBatisUtil.createSqlSession();
			userList = sqlSession.getMapper(UserMapper.class).getUserList_choose(userName, userCode, userRole, creationDate);
		} catch (ParseException e) {
			e.printStackTrace();
		} finally {
			MyBatisUtil.closeSqlSession(sqlSession);
		}
		for(User _user:userList) {
			logger.debug("testGetUserByRoleId_foreach_list userCode: " + _user.getUserCode()
					+ " and userName: " + _user.getUserName()
					+ " and userRole: " + _user.getUserRole()
					+ " and creationDate: " + _user.getCreationDate());
		}
	}