0、参考文章
1、https://www.cnblogs.com/morewindows0/p/9320018.html
1、mysql
1. 批量插入操作
void insertBatch(@Param("itemList") List<xxx> listxxx);
<insert id="batchInsert" parameterType="java.util.List">
insert into tablename (xxx,xxx,xxx)
values
/*
collection的属性值为接口中对应的参数名称
#{item.xxx},#{item.xxx},#{item.xxx}
注意保留括号
*/
<foreach collection="itemList" item="item" separator=",">
(#{item.xxx},#{item.xxx},#{item.xxx})
</foreach>
</insert>
2. 批量更新操作
void batchUpdate(@Param("itemList") List<xxx> listxxx);
①使用循环 update 形式【这种方式需要特别注意,需要在数据库连接字符串中增加 allowMultiQueries=true ,不然会报异常】
<update id="batchUpdate" parameterType="java.util.List">
<foreach collection="itemList" item="item" separator=";">
update t_user
<set>
<if test="item.userName != null and item.userName != ''">
user_name = #{item.userName},
</if>
<if test="item.gender != null and item.gender != ''">
gender = #{item.gender},
</if>
</set>
where user_id = #{item.userId}
</foreach>
</update>
②使用 case 形式
<update id="batchUpdate" parameterType="java.util.List">
update t_user
set user_name =
<foreach collection="itemList" item="item" index="index" separator=" " open="case" close="end">
WHEN user_id = #{item.userId}
Then #{item.userName}
</foreach>,
gender =
<foreach collection="itemList" item="item" index="index" separator=" " open="case" close="end">
WHEN user_id = #{item.userId}
then #{item.gender}
</foreach>
where
<foreach collection="itemList" item="item" separator="or">
user_id = #{item.userId}
</foreach>
</update>
<update id="batchUpdate" parameterType="java.util.List">
UPDATE t_demo_user
<trim prefix="set" suffixOverrides=",">
<trim prefix="user_name = case" suffix="end,">
<foreach collection="itemList" item="item" index="index">
<if test="item.userName != null">
when user_id = #{item.userId} then #{item.userName}
</if>
</foreach>
</trim>
<trim prefix="gender = case" suffix="end,">
<foreach collection="itemList" item="item" index="index">
<if test="item.gender != null">
when user_id = #{item.userId} then #{item.gender}
</if>
</foreach>
</trim>
</trim>
where user_id in
<foreach collection="itemList" item="item" open="(" close=")" separator=",">
#{item.userId}
</foreach>
</update>
3. 批量删除
void batchDelete(@Param("itemList") List<xxx> listxxx);
<delete id="batchDelete" parameterType="java.util.List">
delete from tablename where id in
<foreach collection="itemList" item="item" open="(" close=")" separator=",">
#{item}
</foreach>
</delete>
2、oracle
1. 批量插入操作
void insertBatch(@Param("itemList") List<xxx> listxxx);
① id 可以自增,不需要序列
<insert id="insertImgList" parameterType="java.util.List">
insert all
<foreach collection="itemList" item="item" index="index">
into tablename (xxx, xxx, xxx)
values (#{item.xxx}, #{item.xxx}, #{item.xxx})
</foreach>
select 1 from dual
</insert>
② id 无法自增,需要序列协助
<insert id="batchInsert" parameterType="java.util.List">
insert into tablename (xxx,xxx,xxx)
select seq.nextval, A.* FROM(
<foreach collection="itemList" item="item" separator="UNION ALL">
select #{item.xxx} xxx, #{item.xxx} xxx from dual
</foreach>)A
</insert>