mybatis对mysql进行插入或者更新(含批量)

最新推荐文章于 2025-03-05 17:59:14 发布

AlbenXie

最新推荐文章于 2025-03-05 17:59:14 发布

阅读量1.4w

点赞数 8

分类专栏：后端Java框架代码重构等优化面试文章标签： mybatis 批量更新或插入

本文链接：https://blog.csdn.net/AlbenXie/article/details/109641690

版权

面试同时被 3 个专栏收录

700 篇文章

订阅专栏

后端Java框架

335 篇文章

订阅专栏

代码重构等优化

143 篇文章

订阅专栏

一、插入或更新(insertOrUpdate) 单条记录

使用ON DUPLICATE KEY UPDATE，如果主键存在，即更新表

mybatis xml文件写法如下：

<insert id="insertOrUpdate" parameterType="xxxxx">
    INSERT into case_warning_rule (
    case_definition_dept_id,
    commitment_day,
    legal_period,
    approval_day,
    approval_warning,
    commitment_warning,
    commitment_unusual,
    legal_warning,
    legal_unusual,
    update_time
    ) VALUES (
    #{caseDefinitionDeptId},
    #{commitmentDay},
    #{legalPeriod},
    #{approvalDay},
    #{approvalWarning},
    #{commitmentWarning},
    #{commitmentUnusual},
    #{legalWarning},
    #{legalUnusual},
    #{updateTime}
    ) ON DUPLICATE KEY UPDATE
    commitment_day =VALUES (commitment_day),
    legal_period =VALUES (legal_period),
    approval_day =VALUES (approval_day),
    approval_warning =VALUES (approval_warning),
    commitment_warning =VALUES (commitment_warning),
    commitment_unusual =VALUES (commitment_unusual),
    legal_warning =VALUES (legal_warning),
    legal_unusual =VALUES (legal_unusual),
    update_time =VALUES (update_time)
</insert>

或者

<insert id="updateAndInsert" parameterType="com.codyy.ms.entity.IpAreaDO">
	INSERT INTO IP_AREA (IP,AREA_NAME,AREA_CODE,UPDATE_TIME)
	VALUE (
		#{ip,jdbcType=VARCHAR},
        #{areaName,jdbcType=VARCHAR},
        #{areaCode,jdbcType=VARCHAR},  
        #{updateTime,jdbcType=TIMESTAMP})

	ON DUPLICATE KEY UPDATE
    	AREA_NAME = #{areaName,jdbcType=VARCHAR},
    	AREA_CODE = #{areaCode,jdbcType=VARCHAR},
    	UPDATE_TIME = #{updateTime,jdbcType=TIMESTAMP}
</insert>

ip为主键或唯一键

或者

<insert id="insertOrUpdatePlan" 
        parameterType="com.happy.hhome.bean.plan.PlanFlowDO">

	insert into planflow
	(
    	plan_no,
    	flow_no,
    	plan_type,
    	plan_invest,
    	plan_profit,
    	plan_balance
	)
	values (
    	#{planNo},
    	#{flowNo},
    	#{planType},
    	#{planInvest},
    	#{planProfit},
    	#{planBalance}
	)
	on duplicate key update
    	plan_type     = values(plan_type),
    	plan_invest   = values(plan_invest),
    	plan_profit   = values(plan_profit),
    	plan_balance  = values(plan_balance)
</insert>

注意：on duplicate key update语句后面，等号前后两个值，均使用表字段，例：plan_type = values(plan_type)

一、插入或更新(insertOrUpdateBatch) 批量

mybatis 批量新增，如果列表里面有id那么则更新，mysql数据库。

VALUES里面取的是sql的字段，如果是关键字，注意要加上``

<insert id="batchAddOrUpdate" 
        parameterType="java.util.List" 
        useGeneratedKeys="true" 
        keyProperty="id">

	insert into t_schedule(
		id,
		doctorId,
		deptId,
		`type`,
		`date`,
		`desc`,
		startTime,
		endTime,
		`number`)
	values
	<foreach collection="list" item="item" index="index" separator=",">
    	(
    	#{item.id},
    	#{item.doctorId},
    	#{item.deptId},
    	#{item.type},
    	#{item.date},
    	#{item.desc},
    	#{item.startTime},
    	#{item.endTime},
    	#{item.number}
    	)
	</foreach>
	ON DUPLICATE KEY UPDATE
		doctorId=VALUES(doctorId),
		deptId=VALUES(deptId),
		`type`=VALUES(`type`),
		`date`=VALUES(`date`),
		`desc`=VALUES(`desc`),
		startTime=VALUES(startTime),
		endTime=VALUES(endTime),
		`number`=VALUES(`number`)
</insert>

或者

使用<foreach>生成values()语句即可，同样的，on duplicate key update语句后面，等号前后两个值，均使用表字段，例：plan_type = values(plan_type)

<insert id="insertOrUpdatePlanList"
        parameterType="com.happy.hhome.bean.plan.PlanFlowDO">

	insert into planflow
	(
    	plan_no,
    	flow_no,
    	plan_type,
    	plan_invest,
    	plan_profit,
    	plan_balance
	)
	values
	<foreach collection="list" item="item" index= "index" separator="," >
	(
    	#{item.planNo},
    	#{item.flowNo},
    	#{item.planType},
    	#{item.planInvest},
    	#{item.planProfit},
    	#{item.planBalance}
	)
	</foreach>
	on duplicate key update
    	plan_type     = values(plan_type),
    	plan_invest   = values(plan_invest),
    	plan_profit   = values(plan_profit),
    	plan_balance  = values(plan_balance)
</insert>

参考：

https://blog.csdn.net/qq_34820670/article/details/80941979?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.channel_param

https://blog.csdn.net/qq_24520119/article/details/78132491?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-2.channel_param

https://blog.csdn.net/u013788943/article/details/105405517?utm_medium=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.channel_param&depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromMachineLearnPai2-3.channel_param

https://blog.csdn.net/weixin_43822795/article/details/96587755?utm_medium=distribute.pc_relevant.none-task-blog-title-7&spm=1001.2101.3001.4242