mybatis Oracle 批量插入、批量更新、批量查询

在使用Mybatis操作oracle数据库时，会涉及到批量操作，踩过好多的坑，留个记录吧。

1、批量插入

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false">
    INSERT INTO FLB_FLIGHT_LOG_FOC
    (
    ID,FLIGHT_ID,FLIGHT_NO,FLIGHT_DATE,FLIGHT_TYPE,AC_TYPE,AC_REG,CAPTAIN,CAPTAIN_NO,FLIGHT_DATE_ATD,DEPARTURE_AIRPORT,ARRIVAL_AIRPORT,
    BAY,BAY_2,INN,OUT,STD,STA,ETD,ETA,ATD,ATA,ADJUST_TYPE,FLG_VR,FLG_CS,FLG_PATCH,TOTAL_FUEL,REAL_FUEL,REMARK,
    RECEIVE_STATUS,PROCESS_STATUS,REASON,PROCESS_COUNT,CREATE_DATE,CREATOR_SN,CREATOR_NAME,UPDATE_DATE,UPDATOR_SN,UPDATOR_NAME
    )
    SELECT SEQ_FLB_FLIGHT_LOG_FOC.NEXTVAL,FOCLOG.* FROM (
    <foreach collection="list"  item="item" index="index" separator="union all">
        SELECT
        #{item.flightId},#{item.flightNo},#{item.flightDate},#{item.flightType},#{item.acType},#{item.acReg},#{item.captain},#{item.captainNo},#{item.flightDateAtd},#{item.departureAirport},#{item.arrivalAirport},
        #{item.bay},#{item.bay2},#{item.inn},#{item.out},#{item.std},#{item.sta},#{item.etd},#{item.eta},#{item.atd},#{item.ata},#{item.adjustType},#{item.flgVr},#{item.flgCs},#{item.flgPatch},#{item.totalFuel},#{item.realFuel},#{item.remark},
        #{item.receiveStatus},#{item.processStatus},#{item.reason},#{item.processCount},#{item.creatorSn},#{item.creatorName},#{item.createDate},#{item.updateDate},#{item.updatorSn},#{item.updatorName}
        FROM DUAL
    </foreach>
    ) FOCLOG
</insert>

2、批量跟新

<update id="batchUpdate"  parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="begin" close=";end;" separator=";">
        UPDATE FLB_FLIGHT_LOG_FOC
        <set>
            FLIGHT_ID=#{item.flightId},FLIGHT_NO=#{item.flightNo},FLIGHT_DATE=#{item.flightDate},FLIGHT_TYPE=#{item.flightType},AC_TYPE=#{item.acType},AC_REG=#{item.acReg},
            CAPTAIN=#{item.captain},CAPTAIN_NO=#{item.captainNo},FLIGHT_DATE_ATD=#{item.flightDateAtd},DEPARTURE_AIRPORT=#{item.departureAirport},ARRIVAL_AIRPORT=#{item.arrivalAirport},
            BAY=#{item.bay},BAY_2=#{item.bay2},INN=#{item.inn},OUT=#{item.out},STD=#{item.std},STA=#{item.sta},ETD=#{item.etd},ETA=#{item.eta},ATD=#{item.atd},ATA=#{item.ata},
            ADJUST_TYPE=#{item.adjustType},FLG_VR=#{item.flgVr},FLG_CS=#{item.flgCs},FLG_PATCH=#{item.flgPatch},TOTAL_FUEL=#{item.totalFuel},REAL_FUEL=#{item.realFuel},REMARK=#{item.remark},
            RECEIVE_STATUS=#{item.receiveStatus},PROCESS_STATUS=#{item.processStatus},REASON=#{item.reason},PROCESS_COUNT=#{item.processCount},CREATOR_SN=#{item.creatorSn},
            CREATOR_Name=#{item.creatorName},CREATE_DATE=#{item.createDate},UPDATE_DATE=#{item.updateDate},UPDATOR_SN=#{item.updatorSn},UPDATOR_NAME=#{item.updatorName}
        </set>
        <where>
            ID = #{item.id}
        </where>
    </foreach>

3、批量查询

<select id="batchSelectByIdList" resultType="com.bireturn.amro.flb.dao.entity.FlbFlightLog4Foc" parameterType="java.util.List">
    SELECT * FROM FLB_FLIGHT_LOG_FOC
    WHERE FLIGHT_ID IN
    <foreach collection="list" index="index" item="item" open="(" separator="," close=")">
        #{item}
    </foreach>
</select>