mybatis批处理

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>

2. 删除参考 mysql ，更新参考 mysql 第一条