mybatis中foreach循环的使用
xml中构建的sql语句
<select id="selectUsers" parameterType="java.util.List" resultType="User">
select * from d_user where id in
<foreach collection = "Ids" item = "id" index="index" open = "(" separator= "," close=")" >
#{id}
</foreach>
</select>
实际执行的sql语句
select * from d_user where id in ('30dc0a58044740edb1014be2a0da33e2','89418cc55902404484a9c04c02971b86')
参数意义
- collection = “Ids” 是接收到的集合或者数组。
- item = “id” 是在循环过程中的ids被循环到的某一个id。
- index=“index” 是当前元素在集合中索引值。
- open = “(” 起始符号用为"("。
- separator= “,” 被循环的所有元素用 “,” 隔开。
- close=")" 结束符号用为")"。
关于传值
* foreach 内 collection 接收值时不使用 #{ xxx } 的形式的。
- 直接 parameterType=“java.util.List”, 根据 list 名获取。
- 通过对象中的list类型的属性传值,根据 list类型的属性名获取。
- 通过 parameterType=" java.util.Map",根据 list 名获取。
用法技巧示例
分割 separator=“union” 将多对象从dual查询出并合并,实现批量插入数据。
<insert id="insert" parameterType="java.util.List">
insert into d_school (
id,
name,
leavel,
label_id,
school_time_start,
school_time_end,
subordinate_province,
subordinate_province_name,
subordinate_city,
subordinate_city_name,
subordinate_region,
subordinate_region_name,
)
<foreach collection="list" item="item" index="index" separator="union">
select
#{item.id},
#{item.name},
#{item.leavel},
#{item.labelId},
#{item.schoolTimeStart},
#{item.schoolTimeEnd},
#{item.subordinateProvince},
#{item.subordinateProvinceName},
#{item.subordinateCity},
#{item.subordinateCityName},
#{item.subordinateRegion},
#{item.subordinateRegionName},
from dual
</foreach>
</insert>
分割 separator=";" 将需要修改的多个对象循环组件update语句,实现批量修改数据。
<update id="update" parameterType="java.util.List">
<foreach collection="list" item="item" index="index" open="" close="" separator=";">
update d_school
<set>
<if test="item.name != null and item.name != ''">
name = #{item.name},
</if>
<if test="item.leavel != null and item.leavel != ''">
leavel = #{item.leavel},
</if>
<if test="item.labelId != null and item.labelId != ''">
label_id = #{item.labelId},
</if>
<if test="item.schoolTimeStart != null and item.schoolTimeStart != ''">
school_time_start = #{item.schoolTimeStart},
</if>
<if test="item.schoolTimeEnd != null and item.schoolTimeEnd != ''">
school_time_end = #{item.schoolTimeEnd},
</if>
<if test="item.subordinateProvince != null and item.subordinateProvince != ''">
subordinate_province = #{item.subordinateProvince},
</if>
<if test="item.subordinateProvinceName != null and item.subordinateProvinceName != ''">
subordinate_province_name = #{item.subordinateProvinceName},
</if>
<if test="item.subordinateCity != null and item.subordinateCity != ''">
subordinate_city = #{item.subordinateCity},
</if>
<if test="item.subordinateCityName != null and item.subordinateCityName != ''">
subordinate_city_name = #{item.subordinateCityName},
</if>
<if test="item.subordinateRegion != null and item.subordinateRegion != ''">
subordinate_region = #{item.subordinateRegion},
</if>
<if test="item.subordinateRegionName != null and item.subordinateRegionName != ''">
subordinate_region_name = #{item.subordinateRegionName},
</if>
</set>
<where>
<if test="item.id != null and item.id != ''">
id = #{item.id}
</if>
</where>
</foreach>
</update>