- 如果传入的是单参数且参数类型是一个List的时候,collection属性值为list
- 如果传入的是单参数且参数类型是一个array数组的时候,collection的属性值为array
- 如果传入的参数是多个的时候,我们就需要把它们封装成一个Map了,当然单参数也可以封装成map,实际上如果你在传入参数的时候,在MyBatis里面也是会把它封装成一个Map的,map的key就是参数名,所以这个时候collection属性值就是传入的List或array对象在自己封装的map里面的key
1.单参数List的类型:
<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>
public List<Blog> dynamicForeachTest(List<Integer> ids);
@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();
}
2.单参数array数组的类型:
<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>
public List<Blog> dynamicForeach2Test(int[] ids);
@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();
}
3 自己把参数封装成Map的类型
<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>
public List<Blog> dynamicForeach3Test(Map<String, Object> params);
@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();
}
项目中的例子:
<insert id="insertBatchBusiness_people" parameterType="map">
insert into tbl_business_people_#{businessId} (business_id,uid)
values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.business_id,jdbcType=VARCHAR},
#{item.uid,jdbcType=VARCHAR}
)
</foreach>
ON DUPLICATE KEY UPDATE updatetime=NOW()
</insert>
dao 层:
public Integer insertBatchBusiness_people(@Param("businessId") Integer businessId,
@Param("list") List<Business_peopleEntity> list);
sevice interface 层
public Integer insertBatchBusiness_people(List<Business_peopleEntity> item);
sevice impl 层
@Override
public Integer insertBatchBusiness_people(
List<Business_peopleEntity> item) {
return business_peopleDao.insertBatchBusiness_people(item.get(0).getBusiness_id(),item);
}
<insert id="insertBatchSelect" parameterType="java.util.List">
insert into tbl_outcalldetail (outcalltaskid, user_id,
telephoneNo, typeno1,
price_range1, typeno2, price_range2,
typeno3, price_range3, kw,
url, recommend, state,
isaccept, remark, outcalluserid,
outcallusername, outcalltime) values
<foreach collection="list" item="item" index="index" separator=",">
(
#{item.outcalltaskid,jdbcType=INTEGER}, #{item.userId,jdbcType=VARCHAR},
#{item.telephoneno,jdbcType=VARCHAR}, #{item.typeno1,jdbcType=VARCHAR},
#{item.priceRange1,jdbcType=VARCHAR}, #{item.typeno2,jdbcType=VARCHAR},
#{item.priceRange2,jdbcType=VARCHAR}, #{item.typeno3,jdbcType=VARCHAR},
#{item.priceRange3,jdbcType=VARCHAR}, #{item.kw,jdbcType=VARCHAR},
#{item.url,jdbcType=VARCHAR}, #{item.recommend,jdbcType=VARCHAR}, #{item.state,jdbcType=INTEGER},
#{item.isaccept,jdbcType=INTEGER}, #{item.remark,jdbcType=VARCHAR}, #{item.outcalluserid,jdbcType=INTEGER},
#{item.outcallusername,jdbcType=VARCHAR}, #{item.outcalltime,jdbcType=TIMESTAMP}
)
</foreach>
</insert>
<update id="updateBatchByPrimaryKeySelective" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update tbl_outcalldetail
<set >
<if test="item.state != null" >
state = #{item.state,jdbcType=INTEGER},
</if>
<if test="item.isaccept != null" >
isaccept = #{item.isaccept,jdbcType=INTEGER},
</if>
<if test="item.remark != null" >
remark = #{item.remark,jdbcType=VARCHAR},
</if>
<if test="item.outcalluserid != null" >
outcalluserid = #{item.outcalluserid,jdbcType=INTEGER},
</if>
<if test="item.outcallusername != null" >
outcallusername = #{item.outcallusername,jdbcType=VARCHAR},
</if>
<if test="item.outcalltime != null" >
outcalltime = #{item.outcalltime,jdbcType=TIMESTAMP},
</if>
</set>
where 1=1
<if test="item.outcalldetailid !=null" >
and id = #{item.outcalldetailid,jdbcType=INTEGER}
</if>
<if test="item.outcalltaskid !=null" >
and outcalltaskid = #{item.outcalltaskid,jdbcType=INTEGER}
</if>
<if test="item.userId !=null" >
and user_id = #{item.userId,jdbcType=VARCHAR}
</if>
</foreach>
</update>