MyBatis批量操作(mysql)

分手后的思念是犯贱 2022-06-08 23:38 252阅读 0赞

mybatis批处理的思路是将多条sql合并为1条sql执行，提高效率。

1：批量删除

以数组为例的写法：

<delete id="batchDeleteGroupMember" parameterType="net.sinolbs.ycd.entity.group.GroupMember">
            delete from group_members where groupId in
            <foreach item="groupMember" collection="array" open="(" separator="," close=")">
                #{groupId}
            </foreach>
        </delete>

以集合为例的写法：

<delete id="batchDeleteGroupMember" parameterType="net.sinolbs.ycd.entity.group.GroupMember">
            delete from group_members where groupId in
            <foreach item="groupMember" collection="list" open="(" separator="," close=")">
                #{groupMember.groupId}
            </foreach>
       </delete>

**mapper**

void batchDeleteGroupMember(List<GroupMember> list);

说明：

item集合或数组里的元素（对象）

collection集合类型（数组或集合）

open以什么开始

close以什么结束

separator中间以什么相连

2：批量新增

<insert id="insertByBatch" parameterType="java.util.List">  
                insert into car_album
                 (id, smallImage, largeImage, imgName, carModelsId,type,  imgPageHref, largeImgPageHref)
                 values
                 <foreach collection ="list" item="img" index= "index" separator =",">
                     (
                     #{img.id}, #{img.smallImage}, #{img.largeImage},  #{img.imgName},
                     #{img.carModelsId}, #{img.type}, #{img.imgPageHref}, #{img.largeImgPageHref}
                     )
                 </foreach >
        </insert>

生成的sql

insert into car_album (id, smallImage, largeImage, imgName, carModelsId,type, imgPageHref, largeImgPageHref) values 
    ( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? ) ,
     ( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? )