Mybatis Oracle批量操作的几种方式

这是我在工作中用的几种方式，有兴趣可以试试。

1.用<froeach>标签加oracle的begin end语句块实现

　　动态新增，根据传入的字段新增，其中主键是用的sequence，所以是固定的

  <insert id="insertSelective">
        <foreach collection="fltCapacityAuths" index="index" item="item" separator=";" open="begin" close=";end;">
            insert into FLT_CAPACITY_AUTH
            <trim prefix="(" suffix=")" suffixOverrides=",">
                CP_AUTH_ID,
                <if test="item.userId != null">
                    USER_ID,
                </if>
                <if test="item.auditUserId != null">
                    AUDIT_USER_ID,
                </if>
                <if test="item.validFlag != null">
                    VALID_FLAG,
                </if>
                <if test="item.operIp != null">
                    OPER_IP,
                </if>
                <if test="item.operHost != null">
                    OPER_HOST,
                </if>
                <if test="item.createdBy != null">
                    CREATED_BY,
                </if>
                <if test="item.createdTime != null">
                    CREATED_TIME,
                </if>
                <if test="item.modifiedBy != null">
                    MODIFIED_BY,
                </if>
                <if test="item.modifiedTime != null">
                    MODIFIED_TIME,
                </if>
            </trim>
            (
            select
            <trim suffixOverrides=",">SEQ_FLT_CAPACITY_AUTH.Nextval,
                <if test="item.userId != null">
                    #{item.userId,jdbcType=DECIMAL},
                </if>
                <if test="item.auditUserId != null">
                    #{item.auditUserId,jdbcType=DECIMAL},
                </if>
                <if test="item.validFlag != null">
                    #{item.validFlag,jdbcType=VARCHAR},
                </if>
                <if test="item.operIp != null">
                    #{item.operIp,jdbcType=VARCHAR},
                </if>
                <if test="item.operHost != null">
                    #{item.operHost,jdbcType=VARCHAR},
                </if>
                <if test="item.createdBy != null">
                    #{item.createdBy,jdbcType=VARCHAR},
                </if>
                <if test="item.createdTime != null">
                    #{item.createdTime,jdbcType=TIMESTAMP},
                </if>
                <if test="item.modifiedBy != null">
                    #{item.modifiedBy,jdbcType=VARCHAR},
                </if>
                <if test="item.modifiedTime != null">
                    #{item.modifiedTime,jdbcType=TIMESTAMP},
                </if>
            </trim>
            from dual
            )
        </foreach>
    </insert>

　　动态修改

 <update id="updateByPrimaryKeySelective" parameterType="com.ceair.rmsfa.user.model.FltCapacityAuth">
        <foreach collection="fltCapacityAuths" item="item" index="index" separator=";" open="begin" close=";end;">
            update FLT_CAPACITY_AUTH
            <set>
                <if test="item.userId != null">
                    USER_ID = #{item.userId,jdbcType=DECIMAL},
                </if>
                <if test="item.auditUserId != null">
                    AUDIT_USER_ID = #{item.auditUserId,jdbcType=DECIMAL},
                </if>
                <if test="item.validFlag != null">
                    VALID_FLAG = #{item.validFlag,jdbcType=VARCHAR},
                </if>
                <if test="item.operIp != null">
                    OPER_IP = #{item.operIp,jdbcType=VARCHAR},
                </if>
                <if test="item.operHost != null">
                    OPER_HOST = #{item.operHost,jdbcType=VARCHAR},
                </if>
                <if test="item.createdBy != null">
                    CREATED_BY = #{item.createdBy,jdbcType=VARCHAR},
                </if>
                <if test="item.createdTime != null">
                    CREATED_TIME = #{item.createdTime,jdbcType=TIMESTAMP},
                </if>
                <if test="item.modifiedBy != null">
                    MODIFIED_BY = #{item.modifiedBy,jdbcType=VARCHAR},
                </if>
                <if test="item.modifiedTime != null">
                    MODIFIED_TIME = #{item.modifiedTime,jdbcType=TIMESTAMP},
                </if>
            </set>
            where CP_AUTH_ID = #{item.cpAuthId,jdbcType=DECIMAL}
        </foreach>
    </update>

2.比较死板，网上常见的批量新增，这种是单个sql语句执行的，效率应该会快一些，不过这种没法实现动态的新增

<insert id="batchFlt_Season_Sch_Pub_Temp" parameterType="java.util.List">
    INSERT INTO flt_season_sch_pub_temp
    (fssp_id, fsp_id,FCR_ID, version_id, uids, ssim_seq_id,
    leg_id, flight_dt, flight_dt_week, ori_airport, des_airport,
    dep_time, arr_time, dep_date_var, arr_date_var,
    flight_type, lane_type, ac_family, layout, ac_owner,
    carrier_company, carrier_flt_num, leg_type,INTER_LEG_TYPE,
     connect_leg_id,  dep_time_offset,SEATS,
    arr_time_offset,
     valid_flag, last_update_date,CPRL_ID )select
    seq_flt_season_sch_pub_temp.nextval fssp_id,A.* FROM(
        <foreach collection="list" item="item" index="index"
            separator="union all">
            select  #{item.FSP_ID},#{item.FCR_ID}, #{item.VERSION_ID,jdbcType=DECIMAL},
            #{item.UIDS}, #{item.SSIM_SEQ_ID,jdbcType=DECIMAL}, #{item.LEG_ID,jdbcType=VARCHAR},
            #{item.FLIGHT_DT}, #{item.FLIGHT_DT_WEEK}, #{item.ORI_AIRPORT},
            #{item.DES_AIRPORT},
            #{item.DEP_TIME,jdbcType=TIMESTAMP},#{item.ARR_TIME,jdbcType=TIMESTAMP},
            #{item.DEP_DATE_VAR}, #{item.ARR_DATE_VAR},
            #{item.FLIGHT_TYPE}, #{item.LANE_TYPE}, #{item.AC_FAMILY},
            #{item.LAYOUT,jdbcType=VARCHAR}, #{item.AC_OWNER}, 
            #{item.CARRIER_COMPANY}, #{item.CARRIER_FLT_NUM}, 
            #{item.LEG_TYPE}, #{item.INTER_LEG_TYPE}, #{item.CONNECT_LEG_ID,jdbcType=VARCHAR}, #{item.DEP_TIME_OFFSET,jdbcType=VARCHAR},
            #{item.SEATS,jdbcType=VARCHAR},
            #{item.ARR_TIME_OFFSET,jdbcType=VARCHAR}, #{item.VALID_FLAG}, sysdate,#{item.CPRL_ID} from dual
        </foreach>)A
    </insert>