Mybatis07-动态sql语句foreach

• foreach

动态 SQL 的另外一个常用的操作需求是对一个集合进行遍历，通常是在构建 IN 条件语句的时候。比如：

<select id="selectPostIn" resultType="domain.blog.Post">
  SELECT *
  FROM POST P
  WHERE ID in
  <foreach item="item" index="index" collection="list"
      open="(" separator="," close=")">
        #{item}
  </foreach>
</select>

 foreach 元素的功能非常强大，它允许你指定一个集合，声明可以在元素体内使用的集合项（item）和索引（index）变量。它也允许你指定开头与结尾的字符串以及在迭代结果之间放置分隔符。这个元素是很智能的，因此它不会偶然地附加多余的分隔符。

注意 :你可以将任何可迭代对象（如 List、Set 等）、Map 对象或者数组对象传递给 foreach 作为集合参数。当使用可迭代对象或者数组时，index 是当前迭代的次数，item 的值是本次迭代获取的元素。当使用 Map 对象（或者 Map.Entry 对象的集合）时，index 是键，item 是值。

1.当传递过来的参数为array（collection="array"）或list（collection="list"）时。

UserMap.xml如下所示：

<delete id="delAll">
	delete from user where user_id in
	<foreach collection="array" item="user_id" open="(" separator="," close=")">
		#{user_id}
	</foreach>	
</delete>
<delete id="delAll1">
	delete from user where user_id in
	<foreach collection="list" item="user_id" open="(" separator="," close=")">
		#{user_id}
	</foreach>	
</delete>

UserDaoImpl.java如下所示：

@Override
public void delAll(Integer[] user_ids) {
	try {
		sqlSession = MybatisUtil.getSqlSession();
		sqlSession.delete(User.class.getName()+".delAll",user_ids);
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException("删除失败,"+e.getMessage());
	}finally {
		MybatisUtil.closeSqlSession(sqlSession);
	}
		
}
@Override
public void delAll1(List<Integer> user_ids) {
	try {
		sqlSession = MybatisUtil.getSqlSession();
		sqlSession.delete(User.class.getName()+".delAll1",user_ids);
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException("删除失败,"+e.getMessage());
	}finally {
		MybatisUtil.closeSqlSession(sqlSession);
	}
		
}

测试类如下所示：

@Test
public void delAll() {
	Integer[] user_ids = new Integer[]{2,3,4};
	userDao.delAll(user_ids);
}
@Test
public void delAll1() {
	ArrayList<Integer> list = new ArrayList<Integer>();
	list.add(2);
	list.add(3);
	userDao.delAll1(list);
}

2.当传递过来的参数为map时，collection属性的值为map中键对应的值。（Map里面放List）

UserMap.xml如下所示：

<delete id="delAll2" parameterType="map">
	delete from user where user_id in
	<foreach collection="user_ids" item="user_id" open="(" separator="," close=")">
		#{user_id}
	</foreach>	
</delete>

UserDaoImpl.java如下所示：

@Override
public void delAll2(HashMap<String, Object> user_ids) {
	try {
		sqlSession = MybatisUtil.getSqlSession();
		sqlSession.delete(User.class.getName()+".delAll2",user_ids);
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException("删除失败,"+e.getMessage());
    }finally {
		MybatisUtil.closeSqlSession(sqlSession);
	}
		
}

测试类如下所示：

@Test
public void delAll2() {
	ArrayList<Integer> list = new ArrayList<Integer>();
	list.add(2);
	HashMap<String, Object> map = new HashMap<String,Object>();
	map.put("user_ids", list);
	userDao.delAll2(map);
}

3.当List中存放Map时。

UserMap.xml如下所示：

<select id="selByAccount" resultType="com.shxt.model.User">
	select <include refid="shxt"></include> from user where account in
	<foreach collection="list" item="user" open="(" separator="," close=")">
		#{user.account}
	</foreach>
</select>

UserDaoImpl.java如下所示：

@Override
public List<User> selByAccount(ArrayList<HashMap<String, Object>> list) {
	try {
		sqlSession = MybatisUtil.getSqlSession();
		List<User> userList = sqlSession.selectList(User.class.getName()+".selByAccount",list);
		return userList;
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException("查询失败,"+e.getMessage());
	}finally {
		MybatisUtil.closeSqlSession(sqlSession);
	}
}

测试类如下所示：

@Test
public void selByAccount() {
	HashMap<String, Object> m1 = new HashMap<String,Object>();
	m1.put("account", "2");
	HashMap<String, Object> m2 = new HashMap<String,Object>();
	m2.put("account", "3");
	ArrayList<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
	list.add(m1);
	list.add(m2);
	userDao.selByAccount(list);	
}

4.当List中存放为POJO类时。

UserMap.xml如下所示：

<select id="selByAccount1" resultType="com.shxt.model.User">
	select <include refid="shxt"></include> from user where account in
	<foreach collection="list" item="user" open="(" separator="," close=")">
		#{user.account}
	</foreach>
</select>

UserDaoImpl.java如下所示：

@Override
public List<User> selByAccount1(ArrayList<User> list) {
	try {
		sqlSession = MybatisUtil.getSqlSession();
		List<User> userList = sqlSession.selectList(User.class.getName()+".selByAccount1",list);
		return userList;
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException("查询失败,"+e.getMessage());
	}finally {
		MybatisUtil.closeSqlSession(sqlSession);
	}
}

测试类如下所示：

@Test
public void selByAccount1() {
	User u1 = new User();
	u1.setAccount("2");
	User u2 = new User();
	u2.setAccount("3");
	ArrayList<User> list = new ArrayList<User>();
	list.add(u1);
	list.add(u2);
	userDao.selByAccount1(list);	
}

5.批量添加数据

UserMap.xml如下所示：

<insert id="addAll">
	insert into user(user_name,account,password) values
	<foreach collection="list" item="user"  separator=",">
		(#{user.userName},#{user.account},#{user.pwd})
	</foreach>	
</insert>

UserDaoImpl.java如下所示：

@Override
public void addAll(ArrayList<User> list) {
	try {
		sqlSession = MybatisUtil.getSqlSession();
		sqlSession.insert(User.class.getName()+".addAll",list);
		sqlSession.commit();
	} catch (Exception e) {
		e.printStackTrace();
		throw new RuntimeException("添加失败,"+e.getMessage());
	}finally {
		MybatisUtil.closeSqlSession(sqlSession);
	}
		
}

测试类如下所示：

@Test
public void addAll() {
	User u1 = new User();
	u1.setAccount("5");
	u1.setUserName("5");
	u1.setPwd("5");
	User u2 = new User();
	u2.setAccount("6");
	u2.setUserName("6");
	u2.setPwd("6");
	ArrayList<User> list = new ArrayList<User>();
	list.add(u1);
	list.add(u2);
	userDao.addAll(list);	
}

• in语句格式

1.用在select语句中：

select user_name,account from user where account in ( '123' , '456' ) 

2.用在add语句中：

insert into user(user_name,account) values ('Amy','123') , ('Tom','456')