首页 编程语言正文

MyBatis 批量插入之Oracle 与MySQL比较

哈哈 阅读：654 2021-03-31 13:28:04 评论：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"，将查询合并结果集。

标签:MyBatis
声明

1.本站遵循行业规范，任何转载的稿件都会明确标注作者和来源；2.本站的原创文章，请转载时务必注明文章作者和来源，不尊重原创的行为我们将追究责任；3.作者投稿可能会经我们编辑修改或补充。

相关文章
关注我们

一个IT知识分享的公众号

友情链接IM LINKS