mybatis批量操作（增删改）sql语句

批量插入

对应sql

insert into tabelname(colum1,colum2...)  values(aa,aa...),(bb,bb...)

　<!-- 批量插入数据 -->
    <insert id="insertBatch" parameterType="java.util.List"
        useGeneratedKeys="true">
        <selectKey resultType="long" keyProperty="id" order="AFTER">
            SELECT
            LAST_INSERT_ID()
        </selectKey>
        insert into  wd_solr(fayu_id,tablename,name,logo,description,section_no,look_count,favorite_count,create_uid,create_time,update_time,timestamp)
        values
        <foreach collection="list" item="wdSolr" index="index"
            separator=",">
            (
            #{wdSolr.fayuId},#{wdSolr.tablename},#{wdSolr.name},#{wdSolr.logo},
            #{wdSolr.description},#{wdSolr.sectionNo},#{wdSolr.lookCount},#{wdSolr.favoriteCount},
            #{wdSolr.createUid},#{wdSolr.createTime},#{wdSolr.updateTime},#{wdSolr.timestamp}
            )
        </foreach>
    </insert>

批量更新

对应Sql

update tablename set 
         colum1= case id when 1 then aa
         when 2 then bb
         when 3 then cc
         ....
         end,
         colum2=case id when 1 then aa
         when 2 then bb
         when 3 then cc
         ....
         end,
where id in (1,2,3....)

<!-- 批量更新数据 -->
    <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>

批量删除

对应sql

delete from tablename where id in (1,2,3....)

<!-- 批量删除数据 -->
    <delete id="deleteBatch">
        delete from wd_solr where
        id in (
        <foreach collection="list" item="wdSolr" index="index"
            separator=",">
            #{wdSolr.id}
        </foreach>
        )
    </delete>

注：批量插入、更新不建议使用，如果数据过大，参数过多同时操作于一条sql会极度占用连接资源，建议使用循环操作单条执行语句。