使用Mybatis批量操作的目的就是为了提高性能，批量操作常用的手段是使用foreach标签，但是在使用foreach标签时批量操作数据量不易过大，过大反而会导致性能下降。

1. 批量插入

public interface OrderDao {
    void batchInert(@Param("orderList") List<Order> orderList);
}

<insert id="batchInert" parameterType="java.util.List">
    insert into order.order
    (order_no,product_name,status,add_time,mod_time)
    values
    <foreach collection="orderList" item="item" index="index" separator=",">
        (#{item.orderNo},#{item.productName},#{item.status},#{item.addTime},unix_timestamp())
    </foreach>
</insert>

2. 批量更新

public interface OrderDao {
    void batchUpdate(@Param("orderList") List<Order> orderList);
}

<update id="batchUpdate" parameterType="java.util.List">
    <foreach collection="orderList" item="item" index="index" separator=";">
        update order.order set product_name = #{item.productName},mod_time = unix_timestamp()
        where id = #{item.id}
    </foreach>
</update>

3. 批量删除

public interface OrderDao {
    void batchDelete(@Param("idList")List<Long> idList);
}

<delete id="batchDelete" parameterType="java.util.List">
    delete from order.order
    where id in
    <foreach collection="idList" item="item" index="index" open="(" separator="," close=")">
        #{item}
    </foreach>
</delete>

4. 批量查询防止传入空集合

public interface OrderDao {
    List<Order> selectOrderList(@Param("idList")List<Long> idList);
}


<select id="selectOrderList" resultType="com.sff.test.Order">
    select * from order.order
    <if test="idList.size &gt; 0 ">
        where id in
        <foreach collection="idList" item="item" index="index" open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
    <if test="idList.size &lt;= 0">
        where 1 = 2
    </if>
    and status = 0
</select>

或者

<select id="selectOrderList" resultType="com.sff.test.Order">
    
    select * from order.order
    where status = 0 
      <choose>
          <when test="idList != null and idList.size() > 0">
              and id in
              <foreach collection='idList' item='item' open='(' close=')' separator=','>
                  #{item}
              </foreach>
          </when>
          <otherwise>
              and id = 0
          </otherwise>
      </choose>
    
</select>

或者

<select id="selectOrderList" resultType="com.sff.test.Order">
    select * from order.order
    WHERE id IN
    <if test="idList == null or idList.size() == 0">
        ('0')
    </if>
    <if test="idList !=null and idList.size() > 0">
        <foreach collection="idList" item="item" index="index"
                 open="(" separator="," close=")">
            #{item}
        </foreach>
    </if>
    AND status = 0
</select>

5. 批量插入如何返回自增主键

Mybatis插入操作要返回自增主键时主要使用到如下三个标签：

useGeneratedKeys ：配置后 Mybatis 使用 JDBC 的 getGeneratedKeys 方法来取出由数据库内部生成的自增主键，仅适用于 insert 和 update 操作。
keyProperty ：指定能够唯一识别对象的属性，Mybatis 会使用 getGeneratedKeys 的返回值或者 selectKey标签的值来给该属性设值，仅适用于 insert 和 update 操作。
keyColumn ：数据库列的属性名称。

5.1 单个对象插入

public interface OrderDao {
    void insert(Order order);
}

<insert id="insert" parameterType="com.sff.transaction.example.bean.Order" useGeneratedKeys="true" keyProperty="id"
        keyColumn="id">
    insert into order.order(order_no, status, product_name, add_time, mod_time)
    values (#{orderNo}, #{status}, #{productName}, #{addTime}, unix_timestamp())
</insert>

5.2 批量对象插入

public interface OrderDao {
    void batchSave(@Param("orderList") List<Order> orderList);
}

<insert id="batchSave" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id"
        keyColumn="id">
    insert into order.order
    (order_no,product_name,status,add_time,mod_time)
    values
    <foreach collection="orderList" item="item" index="index" separator=",">
        (#{item.orderNo},#{item.productName},#{item.status},#{item.addTime},unix_timestamp())
    </foreach>
</insert>

容易踩坑的地方

void batchSave(@Param("orderList") List<Order> orderList)

这里的 batchSave只有一个参数，如果有多个参数时怎么返回自增主键？

public interface OrderDao {
    void batchSave(@Param("orderList") List<Order> orderList, @Param("sharding") Integer sharding);
}

如果此时继续沿用上面的写法就会报如下所示的错误：

org.apache.ibatis.executor.ExecutorException: Could not determine which parameter to assign generated keys to. Note that when there are multiple parameters, 'keyProperty' must include the parameter name (e.g. 'param.id'). Specified key properties are [id] and available parameters are [orderList, param1, sharding, param2]

但是数据会正常的入库，程序执行流程中断。正确的写法如下：

<insert id="batchSave" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="orderList.id"
        keyColumn="id">
    insert into order.order
    (order_no,product_name,status,add_time,mod_time)
    values
    <foreach collection="orderList" item="item" index="index" separator=",">
        (#{item.orderNo},#{item.productName},#{item.status},#{item.addTime},unix_timestamp())
    </foreach>
</insert>

6. 写在最后

Mybatis 从 3.3.1 版本开始支持批量插入时返回自增主键，Mybatis 官方的 Issues 有说明

MyBatis批量操作实战