MyBatis 批量插入、更新、删除

1、批量更新

原生 SQL 语句

 数据库批量插入数据的 SQL 语句是：

insert into picture_bed.recycle_bin (aa,bb,cc) values(xx,xx,xx),(oo,oo,oo);

mapper

    <!--批量添加数据-->
    <insert id="insertMultiple" parameterType="java.util.List"  keyProperty="id" useGeneratedKeys="true">
        insert into picture_bed.recycle_bin (user_id, picture_id,picture_name, delete_time, url, oss_id) values
        <foreach collection="list" item="value" separator=",">
            (#{value.userId}, #{value.pictureId},  #{value.pictureName},#{value.deleteTime}, #{value.url}, #{value.ossId})
        </foreach>
    </insert>

2、批量更新

原生 SQL 语句

update picture_bed.recycle_bin set user_id = 2,picture_id = 3 where where id = 3;

执行多条 update

  使用 for 循环遍历多条 update 进行更新。

    <!-- 批量更新，通过接收传进来的参数list进行循环着组装sql -->
    <update id="updateListOther" parameterType="java.util.List">
        <foreach collection="list" item="value" index="index" open="" close="" separator=";">
            update picture_bed.other set content = #{value.content} where id = #{value.id}
        </foreach>
    </update>

多个 ID 的数据多个字段更新为相同的数据

  将多个 ID 数据的同一个字段更新为同一个值。list.size() == 0 是为了防止集合为空时，没有 where 语句，导致全部数据进行更新。

    <!--更新图片的删除状态-->
    <update id="updateDeletePicture">
        update picture_bed.picture
        <set>
            delete_state = #{delete}
        </set>
        <where>
            <foreach collection="list" item="value" separator="," open="id in(" close=")">
                #{value}
            </foreach>
        </where>
        <if test="list.size() == 0">
            <where>
                id = 0
            </where>
        </if>
    </update>

多个 ID 的数据多个字段更新为不同的数据

  使用 CASE WHEN 决定字段的值，SQL 语句是：

UPDATE picture_bed.recycle_bin
SET user_id= CASE id
WHEN 1 THEN 3
WHEN 2 THEN 2
WHEN 3 THEN 1
END
, picture_name = CASE id
WHEN 1 THEN 'xx1'
WHEN 2 THEN 'yy2'
WHEN 3 THEN 'zz4'
END
WHERE id IN (1,2,3);

  MyBatis 中 mapper.xml 的代码如下：

　　<!-- 批量更新数据 -->
    <update id="updateBatch">
        update wd_solr set
        name =
        <foreach collection="list" item="wdSolr" index="index"
            separator=" " open="case id" close="end">
            when #{wdSolr.id} then
            #{wdSolr.name}
        </foreach>
        ,logo =
        <foreach collection="list" item="wdSolr" index="index"
            separator=" " open="case id" close="end">
            when #{wdSolr.id} then
            #{wdSolr.logo}
        </foreach>  
        ,timestamp =
        <foreach collection="list" item="wdSolr" index="index"
            separator=" " open="case id" close="end">
            when #{wdSolr.id} then #{wdSolr.timestamp}
        </foreach>
        where id in
        <foreach collection="list" item="wdSolr" index="index" 
            separator="," open="(" close=")">
            #{wdSolr.id}
        </foreach>
    </update>

3、批量删除

原生 SQL 语句

delete from picture_bed.recycle_bin where id in(1,2,3);

mappeer

    <!--删除多个id的图片-->
    <delete id="deleteMultiple" parameterType="java.util.List"  >
        delete from picture_bed.recycle_bin
        <where>
            <foreach collection="list" item="value" separator="," open="id in(" close=")">
                #{value}
            </foreach>
        </where>
        <if test="list.size() == 0">
            <where>
                id = 0
            </where>
        </if>
    </delete>