MySQL mybatis 批量执行

批量新增

　　 <!-- 批量新增节点权限 -->
    <insert id="addNodeRightsBatch" parameterType="Map">
        INSERT INTO ${tableName}
        (id, nodeId, companyId, rights, operateOjbect,
        objType, createUser, updateUser, isAdmin, createDate, updateDate,view)
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (#{item.id},#{item.nodeId},#{item.companyId},#{item.rights},#{item.operateOjbect},#{item.objType},#{item.createUser},#{item.updateUser},
            #{item.isAdmin},now(),now(),#{item.view})
        </foreach>
    </insert>

批量修改

    <!-- 批量修改节点权限 -->
    <update id="updateNodeRightsBatch" parameterType="Map">
        UPDATE ${tableName} 
        <trim prefix="set" suffixOverrides=",">
            <trim prefix="rights =case" suffix="end,">
                <foreach collection="list" item="item" index="index">
                     WHEN id = #{item.id} THEN #{item.rights}
                </foreach>
            </trim>
        </trim>
        WHERE
        <foreach collection="list" separator=" ) or (" item="item" index="index" open="(" close=")">
            id = #{item.id}
        </foreach>
    </update>

批量删除

    <!-- 删除节点权限 -->
    <delete id="deleteByNodeIdAndOperateObject" parameterType="Map">
        DELETE FROM
        ${tableName}
        WHERE companyId=#{companyId}
        AND (nodeId = -1 
        <foreach collection="list" item="item">
            OR nodeId IN (${item.nodeId})
        </foreach>)
        AND operateOjbect = #{operateObject}
    </delete>

对list集合实现分组的一种方案：

使用List.subList（fromIndex，toIndex）-->（开始位置，从零开始；结束位置，不包含结束位置）

    int size = list.size();
    for (int i = 0; i < size; i=i+1000){
         List<NodeRights> subList =  list.subList(i, i+1000 > size? size:i+1000);                
         Map<String, Object> dataMap = new HashMap<>(1);
         dataMap.put("list", subList);
         ret = this.insert("JC_TABLE.XXX", dataMap);
    }