Mybatis 批量操作（批量插入、批量更新、批量删除）总结

---

一、批量插入

<insert id="insertBatch" parameterType="java.util.List">
   INSERT INTO business_database
   (id, person_id, name, id_card, cman, ctime)
   VALUES
   <foreach collection="list" index="index" separator="," item="item">
       (#{item.id,jdbcType=VARCHAR}, #{item.personId,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR},
       #{item.idCard,jdbcType=VARCHAR}, #{item.cman,jdbcType=VARCHAR}, #{item.ctime,jdbcType=TIMESTAMP})
   </foreach>
</insert>

foreach元素的属性主要有 item，index，collection，open，separator，close。
item表示集合中每一个元素进行迭代时的别名
index指定一个名字，用于表示在迭代过程中，每次迭代到的位置
open表示该语句以什么开始
separator表示在每次进行迭代之间以什么符号作为分隔符
close表示以什么结束
在使用foreach的时候最关键的也是最容易出错的就是collection属性，该属性是必须指定的，但是在不同情况 下，该属性的值是不一样的，主要有一下3种情况：
1.如果传入的是单参数且参数类型是一个List的时候，collection属性值为list
2.如果传入的是单参数且参数类型是一个array数组的时候，collection的属性值为array
3.如果传入的参数是多个的时候，我们就需要把它们封装成一个Map了，当然单参数也可以封装成map
使用批量插入执行的SQL语句应该等价于：

insert into business_database (id, person_id, name, id_card, cman, ctime)
values (?,?,?,?,?,?),(?,?,?,?,?,?),(?,?,?,?,?,?),(?,?,?,?,?,?)

二、批量更新

1、第一种方法，通过接收传进来的参数list进行循环着组装sql，最后一条记录一个update语句，性能较差，量大了就有可能造成sql阻塞。

<update id="updateBatch" parameterType="java.util.List" >
  <foreach collection="list" item="item" index="index" open="" close="" separator=";">
      update business_database
      <set >
          <if test="item.name != null" >
              name = #{item.name,jdbcType=VARCHAR},
          </if>
          <if test="item.idCard != null" >
              id_card = #{item.idCard,jdbcType=VARCHAR},
          </if>
          <if test="item.uman != null" >
              uman = #{item.uman,jdbcType=VARCHAR},
          </if>
          <if test="item.utime != null" >
              utime = #{item.utime,jdbcType=TIMESTAMP},
          </if>
      </set>
      where id = #{item.id,jdbcType=VARCHAR}
  </foreach>
</update>

2、第二种方法，通过 case when语句变相的进行批量更新，虽然最后只会有一条更新语句，但是每一个case when 都要循环一遍list集合，所以大批量拼sql的时候会比较慢，所以效率问题严重。

<update id="updateBatch" parameterType="java.util.List" >
    update business_database
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="name =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.name!=null">
                    when id=#{item.id} then #{item.name}
                </if>
            </foreach>
        </trim>
        <trim prefix="id_card =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.idCard!=null">
                    when id=#{item.id} then #{item.idCard}
                </if>
            </foreach>
        </trim>
        <trim prefix="uman =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.uman!=null">
                    when id=#{item.id} then #{item.uman}
                </if>
            </foreach>
        </trim>
        <trim prefix="utime =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.utime!=null">
                    when id=#{item.id} then #{item.utime}
                </if>
            </foreach>
        </trim>
    </trim>
    where
    <foreach collection="list" separator="or" item="item" index="index" >
        id=#{item.id}
    </foreach>
</update>

执行的sql等价于：

UPDATE business_database
    SET name = CASE id 
        WHEN "1" THEN 'name1'
        WHEN "2" THEN 'name2'
        WHEN "3" THEN 'name3'
    END, 
    id_card = CASE id 
        WHEN "1" THEN 'id_card1'
        WHEN "2" THEN 'id_card2'
        WHEN "3" THEN 'id_card3'
    END,
    uman = CASE id 
        WHEN "1" THEN 'uman1'
        WHEN "2" THEN 'uman2'
        WHEN "3" THEN 'uman3'
    END,
    utime = CASE id 
        WHEN "1" THEN 'utime1'
        WHEN "2" THEN 'utime2'
        WHEN "3" THEN 'utime3'
    END
WHERE id IN ("1","2","3")

3、第三种方法，用ON DUPLICATE KEY UPDATE（MySQL），是最快的一种方式，但是一般大公司都禁用，这种sql有可能会造成数据丢失和主从上表的自增id值不一致。而且用这个更新时，记得一定要加上id，而且values（）括号里面放的是数据库字段，不是java对象的属性字段。

<insert id="updateBatch" parameterType="java.util.List">
    INSERT INTO business_database
    (id, person_id, name, id_card, uman, utime)
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (#{item.id,jdbcType=VARCHAR}, #{item.personId,jdbcType=VARCHAR}, #{item.name,jdbcType=VARCHAR},
        #{item.idCard,jdbcType=VARCHAR}, #{item.uman,jdbcType=VARCHAR}, #{item.utime,jdbcType=TIMESTAMP})
    </foreach>
    ON DUPLICATE KEY UPDATE
    id=VALUES(id),person_id = VALUES(person_id),name = VALUES(name),idCard = VALUES(idCard),
    uman = VALUES(uman),utime = VALUES(utime)
</insert>

三、批量删除

1、传数组

 int deletesData(String[] idArr);

<delete id="deletesData" parameterType="java.lang.String">
    delete from business_database where id in
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

2、传map

map.put("fileIds","\"1\",\"2\",\"3\""); 

<delete id="deletesData" parameterType="java.util.Map">
   delete from business_database where id in (${fileIds})
</delete>

执行的sql等价于：

delete from business_database where id in ("1","2","3")

3、多参数批量删除

int deleteBatch(@Param("name") String name, @Param("idCard") String idCard, @Param("ids") String[] ids);

<delete id="deleteBatch">
    delete from business_database
    where name = #{name,jdbcType=VARCHAR} AND id_card = #{idCard,jdbcType=VARCHAR} and id in
    <foreach collection="ids" item="item" index="index" open="(" separator="," close=")">
        #{item}
    </foreach>
</delete>

@Param是MyBatis所提供的(org.apache.ibatis.annotations.Param)，作为Dao层的注解，作用是用于传递参数，从而可以与SQL中的的字段名相对应，一般在2=<参数数<=5时使用最佳。