MyBatis 批量插入之Oracle 与MySQL比较

我就是我 2023-10-10 11:45 52阅读 0赞

## MyBatis + MySQL 实现批量新增和修改 ##

#### 批量新增 ####

Mapper 接口定义

void batchInsert(List<UcasUnitProj> list);

Mapper.xml 文件配置

<insert id="batchInsert" parameterType="java.util.List">
    		insert into ucas_unit_proj (sid, individual_proj_sid, eng_proj_sid,
    		unit_proj_name, unit_proj_type, unit_proj_no,
    		basis_type_code, cons_type_code, building_usage_function,
    		unit_proj_add, start_date, end_date,
    		seismic_grade, seismic_for_intensity, fire_resistance_rating,
    		design_working_life, transfer_unit, archiving_date,
    		unit_proj_id, old_unit_proj_id, note,
    		created_by, created_dt, updated_by,
    		updated_dt, cons_unit_name,
    		cons_social_credit_code, cons_person_name, cons_person_card_id,
    		subCons_unit_name, subCons_social_credit_code,
    		subCons_person_name, subCons_person_card_id,
    		surveying_unit_name, surveying_social_credit_code,
    		surveying_person_name, surveying_person_card_id,
    		quality_supervision_unit, quaSup_social_credit_code,
    		ownership_unit, ownership_social_credit_code,
    		maintenance_unit, maintenance_social_credit_code,
    		cons_permit_no, planning_verify_certificate_no,
    		proj_accep_record_no, individual_unit_no, abnormal_filed)
    		values
    		<foreach collection="list" item="item" index="index"
    			separator=",">
    			(#{item.sid,jdbcType=VARCHAR},
    			#{item.individualProjSid,jdbcType=VARCHAR},
    			#{item.engProjSid,jdbcType=VARCHAR},
    			#{item.unitProjName,jdbcType=VARCHAR},
    			#{item.unitProjType,jdbcType=VARCHAR},
    			#{item.unitProjNo,jdbcType=VARCHAR},
    			#{item.basisTypeCode,jdbcType=VARCHAR},
    			#{item.consTypeCode,jdbcType=VARCHAR},
    			#{item.buildingUsageFunction,jdbcType=VARCHAR},
    			#{item.unitProjAdd,jdbcType=VARCHAR},
    			#{item.startDate,jdbcType=TIMESTAMP},
    			#{item.endDate,jdbcType=TIMESTAMP},
    			#{item.seismicGrade,jdbcType=VARCHAR},
    			#{item.seismicForIntensity,jdbcType=VARCHAR},
    			#{item.fireResistanceRating,jdbcType=VARCHAR},
    			#{item.designWorkingLife,jdbcType=VARCHAR},
    			#{item.transferUnit,jdbcType=VARCHAR},
    			#{item.archivingDate,jdbcType=TIMESTAMP},
    			#{item.unitProjId,jdbcType=VARCHAR},
    			#{item.oldUnitProjId,jdbcType=VARCHAR},
    			#{item.note,jdbcType=VARCHAR},
    			#{item.createdBy,jdbcType=VARCHAR}, #{item.createdDt,jdbcType=TIMESTAMP},
    			#{item.updatedBy,jdbcType=VARCHAR},
    			#{item.updatedDt,jdbcType=TIMESTAMP},
    			#{item.consUnitName,jdbcType=VARCHAR},
    			#{item.consSocialCreditCode,jdbcType=VARCHAR},
    			#{item.consPersonName,jdbcType=VARCHAR},
    			#{item.consPersonCardId,jdbcType=VARCHAR},
    			#{item.subconsUnitName,jdbcType=VARCHAR},
    			#{item.subconsSocialCreditCode,jdbcType=VARCHAR},
    			#{item.subconsPersonName,jdbcType=VARCHAR},
    			#{item.subconsPersonCardId,jdbcType=VARCHAR},
    			#{item.surveyingUnitName,jdbcType=VARCHAR},
    			#{item.surveyingSocialCreditCode,jdbcType=VARCHAR},
    			#{item.surveyingPersonName,jdbcType=VARCHAR},
    			#{item.surveyingPersonCardId,jdbcType=VARCHAR},
    			#{item.qualitySupervisionUnit,jdbcType=VARCHAR},
    			#{item.quasupSocialCreditCode,jdbcType=VARCHAR},
    			#{item.ownershipUnit,jdbcType=VARCHAR},
    			#{item.ownershipSocialCreditCode,jdbcType=VARCHAR},
    			#{item.maintenanceUnit,jdbcType=VARCHAR},
    			#{item.maintenanceSocialCreditCode,jdbcType=VARCHAR},
    			#{item.consPermitNo,jdbcType=VARCHAR},
    			#{item.planningVerifyCertificateNo,jdbcType=VARCHAR},
    			#{item.projAccepRecordNo,jdbcType=VARCHAR},
    			#{item.individualUnitNo,jdbcType=VARCHAR},#{item.abnormalFiled,jdbcType=VARCHAR})
    		</foreach>
    	</insert>

### 批量修改 ###

Mapper 接口定义

void batchUpdate(List<UcasUnitProj> list);

Mapper.xml 文件配置

<update id="batchUpdate" parameterType="java.util.List">
    		<foreach collection="list" item="item" index="index" open=""
    			close="" separator=";">
    			update ucas_unit_proj
    			<set>
    				<if test="item.individualProjSid != null">
    					individual_proj_sid = #{item.individualProjSid,jdbcType=VARCHAR},
    				</if>
    				<if test="item.engProjSid != null">
    					eng_proj_sid = #{item.engProjSid,jdbcType=VARCHAR},
    				</if>
    				<if test="item.unitProjName != null">
    					unit_proj_name = #{item.unitProjName,jdbcType=VARCHAR},
    				</if>
    				<if test="item.unitProjType != null">
    					unit_proj_type = #{item.unitProjType,jdbcType=VARCHAR},
    				</if>
    				<if test="item.unitProjNo != null">
    					unit_proj_no = #{item.unitProjNo,jdbcType=VARCHAR},
    				</if>
    				<if test="item.basisTypeCode != null">
    					basis_type_code = #{item.basisTypeCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.consTypeCode != null">
    					cons_type_code = #{item.consTypeCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.buildingUsageFunction != null">
    					building_usage_function = #{item.buildingUsageFunction,jdbcType=VARCHAR},
    				</if>
    				<if test="item.unitProjAdd != null">
    					unit_proj_add = #{item.unitProjAdd,jdbcType=VARCHAR},
    				</if>
    				<if test="item.startDate != null">
    					start_date = #{item.startDate,jdbcType=TIMESTAMP},
    				</if>
    				<if test="item.endDate != null">
    					end_date = #{item.endDate,jdbcType=TIMESTAMP},
    				</if>
    				<if test="item.seismicGrade != null">
    					seismic_grade = #{item.seismicGrade,jdbcType=VARCHAR},
    				</if>
    				<if test="item.seismicForIntensity != null">
    					seismic_for_intensity = #{item.seismicForIntensity,jdbcType=VARCHAR},
    				</if>
    				<if test="item.fireResistanceRating != null">
    					fire_resistance_rating = #{item.fireResistanceRating,jdbcType=VARCHAR},
    				</if>
    				<if test="item.designWorkingLife != null">
    					design_working_life = #{item.designWorkingLife,jdbcType=VARCHAR},
    				</if>
    				<if test="item.transferUnit != null">
    					transfer_unit = #{item.transferUnit,jdbcType=VARCHAR},
    				</if>
    				<if test="item.archivingDate != null">
    					archiving_date = #{item.archivingDate,jdbcType=TIMESTAMP},
    				</if>
    				<if test="item.unitProjId != null">
    					unit_proj_id = #{item.unitProjId,jdbcType=VARCHAR},
    				</if>
    				<if test="item.oldUnitProjId != null">
    					old_unit_proj_id = #{item.oldUnitProjId,jdbcType=VARCHAR},
    				</if>
    				<if test="item.note != null">
    					note = #{item.note,jdbcType=VARCHAR},
    				</if>
    				<if test="item.createdBy != null">
    					created_by = #{item.createdBy,jdbcType=VARCHAR},
    				</if>
    				<if test="item.createdDt != null">
    					created_dt = #{item.createdDt,jdbcType=TIMESTAMP},
    				</if>
    				<if test="item.updatedBy != null">
    					updated_by = #{item.updatedBy,jdbcType=VARCHAR},
    				</if>
    				<if test="item.updatedDt != null">
    					updated_dt = #{item.updatedDt,jdbcType=TIMESTAMP},
    				</if>
    				<if test="item.consUnitName != null">
    					cons_unit_name = #{item.consUnitName,jdbcType=VARCHAR},
    				</if>
    				<if test="item.consSocialCreditCode != null">
    					cons_social_credit_code = #{item.consSocialCreditCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.consPersonName != null">
    					cons_person_name = #{item.consPersonName,jdbcType=VARCHAR},
    				</if>
    				<if test="item.consPersonCardId != null">
    					cons_person_card_id = #{item.consPersonCardId,jdbcType=VARCHAR},
    				</if>
    				<if test="item.subconsUnitName != null">
    					subCons_unit_name = #{item.subconsUnitName,jdbcType=VARCHAR},
    				</if>
    				<if test="item.subconsSocialCreditCode != null">
    					subCons_social_credit_code = #{item.subconsSocialCreditCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.subconsPersonName != null">
    					subCons_person_name = #{item.subconsPersonName,jdbcType=VARCHAR},
    				</if>
    				<if test="item.subconsPersonCardId != null">
    					subCons_person_card_id = #{item.subconsPersonCardId,jdbcType=VARCHAR},
    				</if>
    				<if test="item.surveyingUnitName != null">
    					surveying_unit_name = #{item.surveyingUnitName,jdbcType=VARCHAR},
    				</if>
    				<if test="item.surveyingSocialCreditCode != null">
    					surveying_social_credit_code = #{item.surveyingSocialCreditCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.surveyingPersonName != null">
    					surveying_person_name = #{item.surveyingPersonName,jdbcType=VARCHAR},
    				</if>
    				<if test="item.surveyingPersonCardId != null">
    					surveying_person_card_id = #{item.surveyingPersonCardId,jdbcType=VARCHAR},
    				</if>
    				<if test="item.qualitySupervisionUnit != null">
    					quality_supervision_unit = #{item.qualitySupervisionUnit,jdbcType=VARCHAR},
    				</if>
    				<if test="item.quasupSocialCreditCode != null">
    					quaSup_social_credit_code = #{item.quasupSocialCreditCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.ownershipUnit != null">
    					ownership_unit = #{item.ownershipUnit,jdbcType=VARCHAR},
    				</if>
    				<if test="item.ownershipSocialCreditCode != null">
    					ownership_social_credit_code = #{item.ownershipSocialCreditCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.maintenanceUnit != null">
    					maintenance_unit = #{item.maintenanceUnit,jdbcType=VARCHAR},
    				</if>
    				<if test="item.maintenanceSocialCreditCode != null">
    					maintenance_social_credit_code = #{item.maintenanceSocialCreditCode,jdbcType=VARCHAR},
    				</if>
    				<if test="item.consPermitNo != null">
    					cons_permit_no = #{item.consPermitNo,jdbcType=VARCHAR},
    				</if>
    				<if test="item.planningVerifyCertificateNo != null">
    					planning_verify_certificate_no = #{item.planningVerifyCertificateNo,jdbcType=VARCHAR},
    				</if>
    				<if test="item.projAccepRecordNo != null">
    					proj_accep_record_no = #{item.projAccepRecordNo,jdbcType=VARCHAR},
    				</if>
    				<if test="item.individualUnitNo != null">
    					individual_unit_no = #{item.individualUnitNo,jdbcType=VARCHAR},
    				</if>
    				<if test="item.deleteFlag != null">
    					delete_flag = #{item.deleteFlag,jdbcType=VARCHAR},
    				</if>
    				<if test="item.abnormalFiled != null">
    					abnormal_filed = #{item.abnormalFiled,jdbcType=VARCHAR},
    				</if>
    			</set>
    			where sid = #{item.sid,jdbcType=VARCHAR}
    		</foreach>
    	</update>

### MyBatis + Oracle实现批量新增 ###

#### 批量新增 ####

Mapper 接口定义

void batchInsert(List<TConstructionProject> list);

Mapper.xml 文件配置

<insert id="batchInsert" parameterType="java.util.List" useGeneratedKeys="false">
      insert into T_CONSTRUCTION_PROJECT (CONSTRUCTIONPROJECTID, COMPANYID, 
          PROJECTTYPE, XMH, XMMC, 
          XMDD, LXPZDW, TDSYZH, 
          YDGHXKZH, TDCRNX, ZYDMJ, 
          ZJZMJ, DS, GCJS, LHL, 
          JZMD, ZCD, CQZHS, LYD, 
          LXD, GP, CP, SWJP, 
          BGQX, JGRQ, YJDW, 
          DH, FZ, GCQY, DSTCWS, 
          DXTCWS, ZTCWS, JZJDMJ, 
          JRJLMJ, ZDJ, YDPZSJ, DKBM, 
          YDXZ, GCSJBH, GCKCBH, 
          LXPZWH, GCGHXKZH, JSYDPZSH, 
          ZYS, ZJS, RJL, XMBAH, 
          DXGCMC, DXGCDD, DXGCBH)
            <foreach collection="list" item="item" index="index" separator="UNION ALL" >
            select
        	 #{item.constructionprojectid,jdbcType=DECIMAL},
        	 #{item.companyid,jdbcType=DECIMAL}, 
          	 #{item.projecttype,jdbcType=VARCHAR}, 
          	 #{item.xmh,jdbcType=VARCHAR}, 
          	 #{item.xmmc,jdbcType=VARCHAR}, 
          	 #{item.xmdd,jdbcType=VARCHAR}, 
          	 #{item.lxpzdw,jdbcType=VARCHAR}, 
          	 #{item.tdsyzh,jdbcType=VARCHAR}, 
          	 #{item.ydghxkzh,jdbcType=VARCHAR}, 
          	 #{item.tdcrnx,jdbcType=DECIMAL}, 
          	 #{item.zydmj,jdbcType=FLOAT}, 
          	 #{item.zjzmj,jdbcType=FLOAT}, 
          	 #{item.ds,jdbcType=DECIMAL}, 
          	 #{item.gcjs,jdbcType=FLOAT}, 
          	 #{item.lhl,jdbcType=FLOAT}, 
          	 #{item.jzmd,jdbcType=FLOAT}, 
          	 #{item.zcd,jdbcType=FLOAT}, 
          	 #{item.cqzhs,jdbcType=DECIMAL}, 
          	 #{item.lyd,jdbcType=DECIMAL}, 
          	 #{item.lxd,jdbcType=DECIMAL}, 
          	 #{item.gp,jdbcType=DECIMAL}, 
          	 #{item.cp,jdbcType=DECIMAL}, 
          	 #{item.swjp,jdbcType=DECIMAL}, 
          	 #{item.bgqx,jdbcType=VARCHAR}, 
          	 #{item.jgrq,jdbcType=TIMESTAMP}, 
          	 #{item.yjdw,jdbcType=VARCHAR}, 
          	 #{item.dh,jdbcType=VARCHAR}, 
          	 #{item.fz,jdbcType=VARCHAR}, 
          	 #{item.gcqy,jdbcType=VARCHAR}, 
          	 #{item.dstcws,jdbcType=DECIMAL}, 
          	 #{item.dxtcws,jdbcType=DECIMAL}, 
          	 #{item.ztcws,jdbcType=DECIMAL}, 
          	 #{item.jzjdmj,jdbcType=VARCHAR}, 
          	 #{item.jrjlmj,jdbcType=FLOAT}, 
          	 #{item.zdj,jdbcType=FLOAT}, 
          	 #{item.ydpzsj,jdbcType=VARCHAR}, 
          	 #{item.dkbm,jdbcType=VARCHAR}, 
          	 #{item.ydxz,jdbcType=VARCHAR}, 
          	 #{item.gcsjbh,jdbcType=VARCHAR}, 
          	 #{item.gckcbh,jdbcType=VARCHAR}, 
          	 #{item.lxpzwh,jdbcType=VARCHAR}, 
          	 #{item.gcghxkzh,jdbcType=VARCHAR}, 
          	 #{item.jsydpzsh,jdbcType=VARCHAR}, 
          	 #{item.zys,jdbcType=FLOAT}, 
          	 #{item.zjs,jdbcType=FLOAT}, 
          	 #{item.rjl,jdbcType=FLOAT}, 
          	 #{item.xmbah,jdbcType=VARCHAR}, 
          	 #{item.dxgcmc,jdbcType=VARCHAR}, 
          	 #{item.dxgcdd,jdbcType=VARCHAR}, 
          	 #{item.dxgcbh,jdbcType=VARCHAR}
          	from dual
            </foreach>  	 
      </insert>

**在Oracle的版本中，有几点需要注意的：**

1.SQL中没有VALUES；

2.<foreach>标签中的(selece ..... from dual)；

3.<foreach>标签中的separator的属性为"UNION ALL"，将查询合并结果集。