Mybatis中的批量操作（insert、update、delete）

一、批量新增

<insert id="saveTransportCost" parameterType="java.util.List">
    INSERT INTO
    transport_cost
    (
    col1,
    col2,
    col3
    )
    VALUES
    <foreach collection="list" item="item" index="index" separator=",">
        (
        #{items.field1},
        #{items.field2},
        #{items.field3}
        )
    </foreach>

</insert>
field1、field2、field3指的是实体对应的属性的名字

二、批量更新

<update id="updateTransportCost" parameterType="java.util.List">
    <foreach collection="list" item="item" separator=";">
        UPDATE
        transport_cost
        <set>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.costType)">
                cost_type = #{item.costType},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.payType)">
                pay_type = #{item.payType},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.payAmount)">
                pay_amount = #{item.payAmount},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.note)">
                note = #{item.note},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.startTime)">
                start_time = #{item.startTime},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.endTime)">
                end_time = #{item.endTime},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.amounts)">
                amounts = #{item.amounts},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.totalPeople)">
                total_people = #{item.totalPeople},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.place)">
                place = #{item.place},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.payTime)">
                pay_time = #{item.payTime},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.gasStationName)">
                gas_station_name = #{item.gasStationName},
            </if>
            <if test="@com.enn.zhwl.commons.dal.Ognl@isNotEmpty(item.gasStationId)">
                gas_station_id = #{item.gasStationId},
            </if>
            update_time = #{item.updateTime},
            operate_id = #{item.operateId}
        </set>
        <where>
            id=#{item.id}
        </where>
    </foreach>
</update>

三:批量删除
这里的collection是指的map的key,item是ids_eq这个key对应的集合中的每个元素。

<delete id="deleteTransportCosts" parameterType="java.util.Map">
    DELETE FROM transport_cost
    <where>
        id in
        <foreach collection="ids_eq" item="id" open="(" close=")" separator=",">
            #{id}
        </foreach>
    </where>
</delete>