MyBatis批量操作(mysql)

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 
( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? ) ,
 ( ?, ?, ?, ?, ?, ?, ?, ? ) , ( ?, ?, ?, ?, ?, ?, ?, ? )