1.dao层
//批量删除
public void deleteRoleManagers(List<Integer> list);
//批量删除2
public void deleteRoles(Map<String,Object> map);
2.xml文件
<delete id="deleteRoleManagers" >
delete from user_role
where ID in
<foreach collection="list" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
<delete id="deleteRoles">
delete from user_role
where ID in
<foreach collection="ids" item="id" open="(" separator="," close=")">
#{id}
</foreach>
</delete>
3.测试代码
@Test
public void deleteRoleManagers(){
RoleManagerDao dao=ctx.getBean("roleManagerDao",RoleManagerDao.class);
//User user=new User(0,"chang",27,new Date(),"hu",new Date(),"jian");
List<Integer> list=new ArrayList<Integer>();
list.add(14);
list.add(15);
dao.deleteRoleManagers(list);
System.out.println(list);
}
@Test
public void deleteRoles(){
RoleManagerDao dao=ctx.getBean("roleManagerDao",RoleManagerDao.class);
Map<String,Object> map=new HashMap<String,Object>();
List<Integer> list=new ArrayList<Integer>();
list.add(12);
list.add(13);
map.put("ids", list);
dao.deleteRoles(map);
System.out.println(map);
}
4.各种用法
- 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
- 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
- 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
- <select id="dynamicForeachTest" resultType="Blog">
- select * from t_blog where id in
- <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
- #{item}
- </foreach>
- </select>
- @Test
- public void dynamicForeachTest() {
- SqlSession session = Util.getSqlSessionFactory().openSession();
- BlogMapper blogMapper = session.getMapper(BlogMapper.class);
- List<Integer> ids = new ArrayList<Integer>();
- ids.add(1);
- ids.add(3);
- ids.add(6);
- List<Blog> blogs = blogMapper.dynamicForeachTest(ids);
- for (Blog blog : blogs)
- System.out.println(blog);
- session.close();
- }
- <select id="dynamicForeach2Test" resultType="Blog">
- select * from t_blog where id in
- <foreach collection="array" index="index" item="item" open="(" separator="," close=")">
- #{item}
- </foreach>
- </select>
- @Test
- public void dynamicForeach2Test() {
- SqlSession session = Util.getSqlSessionFactory().openSession();
- BlogMapper blogMapper = session.getMapper(BlogMapper.class);
- int[] ids = new int[] {1,3,6,9};
- List<Blog> blogs = blogMapper.dynamicForeach2Test(ids);
- for (Blog blog : blogs)
- System.out.println(blog);
- session.close();
- }
- <select id="dynamicForeach3Test" resultType="Blog">
- select * from t_blog where title like "%"#{title}"%" and id in
- <foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
- #{item}
- </foreach>
- </select>
- @Test
- public void dynamicForeach3Test() {
- SqlSession session = Util.getSqlSessionFactory().openSession();
- BlogMapper blogMapper = session.getMapper(BlogMapper.class);
- final List<Integer> ids = new ArrayList<Integer>();
- ids.add(1);
- ids.add(2);
- ids.add(3);
- ids.add(6);
- ids.add(7);
- ids.add(9);
- Map<String, Object> params = new HashMap<String, Object>();
- params.put("ids", ids);
- params.put("title", "中国");
- List<Blog> blogs = blogMapper.dynamicForeach3Test(params);
- for (Blog blog : blogs)
- System.out.println(blog);
- session.close();
- }
2.扩展中: collection:
1. 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
<foreach collection="list" index="index" item="item" open="(" separator="," close=")">
2. 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
<foreach collection="array" index="index" item="item" open="(" separator="," close=")">
3. 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可
<foreach collection="ids" index="index" item="item" open="(" separator="," close=")">
3.
sql语句是这样:
select * from t_news n where ? in n.tags or ? in n.tags
分解就是:
<select id="selectTestForEach" parameterType="News" resultMap="NewsResultMapper">
select * from t_news n where
<foreach collection="listTag" index="index" item="tag" open=""
separator="or" close="">
#{tag} in n.tags
</foreach>
</select>
4.
语句:select * from t_news n where n.tags like ? or n.tags like ?
分解是:
<select id="selectTestForEach" parameterType="News" resultMap="NewsResultMapper">
select * from t_news n where
<foreach collection="listTag" index="index" item="tag" open=""
separator="or" close="">
n.tags like '%'||#{tag}||'%'
</foreach>
<select>