Mybatis Oracle批量操作的几种方式

<insert id="insertSelective"> 2 <foreach collection="fltCapacityAuths" index="index" item="item" separator=";" open="begin" close=";end;"> 3 insert into FLT_CAPACITY_AUTH 4 <trim prefix="(" suffix=")" suffixOverrides=","> 5 CP_AUTH_ID, 6 <if test="item.userId != null"> 7 USER_ID, 8 </if> 9 <if test="item.auditUserId != null"> 10 AUDIT_USER_ID, 11 </if> 12 <if test="item.validFlag != null"> 13 VALID_FLAG, 14 </if> 15 <if test="item.operIp != null"> 16 OPER_IP, 17 </if> 18 <if test="item.operHost != null"> 19 OPER_HOST, 20 </if> 21 <if test="item.createdBy != null"> 22 CREATED_BY, 23 </if> 24 <if test="item.createdTime != null"> 25 CREATED_TIME, 26 </if> 27 <if test="item.modifiedBy != null"> 28 MODIFIED_BY, 29 </if> 30 <if test="item.modifiedTime != null"> 31 MODIFIED_TIME, 32 </if> 33 </trim> 34 ( 35 select 36 <trim suffixOverrides=",">SEQ_FLT_CAPACITY_AUTH.Nextval, 37 <if test="item.userId != null"> 38 #{item.userId,jdbcType=DECIMAL}, 39 </if> 40 <if test="item.auditUserId != null"> 41 #{item.auditUserId,jdbcType=DECIMAL}, 42 </if> 43 <if test="item.validFlag != null"> 44 #{item.validFlag,jdbcType=VARCHAR}, 45 </if> 46 <if test="item.operIp != null"> 47 #{item.operIp,jdbcType=VARCHAR}, 48 </if> 49 <if test="item.operHost != null"> 50 #{item.operHost,jdbcType=VARCHAR}, 51 </if> 52 <if test="item.createdBy != null"> 53 #{item.createdBy,jdbcType=VARCHAR}, 54 </if> 55 <if test="item.createdTime != null"> 56 #{item.createdTime,jdbcType=TIMESTAMP}, 57 </if> 58 <if test="item.modifiedBy != null"> 59 #{item.modifiedBy,jdbcType=VARCHAR}, 60 </if> 61 <if test="item.modifiedTime != null"> 62 #{item.modifiedTime,jdbcType=TIMESTAMP}, 63 </if> 64 </trim> 65 from dual 66 ) 67 </foreach> 68 </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>

 

Mybatis Oracle批量操作的几种方式

标签：ast   sele   Fix   rms   efi   效率   pen   rip   port