一、批量插入
第一种方法(Oracle):
insert into tableName (colum1,colum2)
<foreach close=")" collection="mktCamCustVos" item="item" index="index" open="(" separator="union">
select
'#{item.targetObjType}',
'#{item.targetObjNbr}'
from dual
</foreach>
用序列值新增作为记录标识变形:
insert into tableName (colum1,colum2)
select sequence.nextValue,a.* from (
<foreach close=")" collection="mktCamCustVos" item="item" index="index" open="(" separator="union">
select
'#{item.targetObjType}',
'#{item.targetObjNbr}'
from dual
</foreach>
) a
第二种方法(MySQL):
<insert id="batchInsert" parameterType="java.util.List">
INSERT INTO STUDENT (id,name,sex,tel,address)
VALUES
<foreach collection="list" item="item" index="index" separator=",">
(#{item.id},#{item.name},#{item.sex},#{item.tel},#{item.address})
</foreach>
</insert>
二、批量更新
第一种方法(Oracle):
<update id="updateAttractionsBatch" parameterType="java.util.List">
begin
<foreach collection="list" item="item" index="index" separator=";" >
update ATTRACTIONS
<set>
<if test="item.id!=null and item.id!=''">
id = #{item.id},
</if>
<if test="item.head!=null and item.head!=''">
HEAD = #{item.head},
</if>
</set>
where id = #{item.id}
</foreach>
;end;
</update>
第二种方法(MySQL):
<update id="batchUpdate" parameterType="java.util.List">
UPDATE STUDENT SET name = "250" WHERE id IN
<foreach collection="list" item="item" index="index" open="(" separator="," close=")" >
#{item}
</foreach>
</update>
三、批量删除
<delete id="deleteAttractions" parameterType="java.util.List">
delete from ATTRACTIONS
<where>
<foreach collection="list" index="index" item="item" open="(" separator="or" close=")">
id=#{item.id}
</foreach>
</where>
</delete>