Mybatis批量更新数据

批量传参样例:

[{"sid": "111", "createTime": "2025-03-11 09:12:00", "pbilId": "pbil_id_111"}, 
{"sid": "222", "createTime": "2025-03-11 09:13:00", "pbilId": "pbil_id_222"}, 
{"sid": "333", "createTime": "2025-03-11 09:14:00", "pbilId": "pbil_id_333"},
{"sid": "444", "createTime": "2025-03-11 09:15:00", "pbilId": "pbil_id_444"},
{"sid": "555", "createTime": "2025-03-11 09:16:00", "pbilId": "pbil_id_555"}]

批量更新语句:

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zhongbao.alarm.mapper.alarm.AlarmAllMapper">

<update id="updateTTestBatch" parameterType="java.util.List">
	update  T_test
	<set>
	    <trim prefix="create_time = case" suffix="end,">
	        <foreach collection="list" item="item" index="index">
	            <if test="item.createTime != null">
	                when sid=#{item.sid} then cast(#{item.createTime} as timestamp)
	            </if>
	        </foreach>
	    </trim>
		<trim prefix="pbil_id = case" suffix="end,">
	        <foreach collection="list" item="item" index="index">
	            <if test="item.pbilId != null">
	                when sid=#{item.sid} then #{item.pbilId}
	            </if>
	        </foreach>
	    </trim>
	</set>
	where sid in
	<foreach collection="list" separator="," item="item" open="(" close=")">
	    #{item.sid}
	</foreach>
</update>
</mapper>

上述sql语句翻译过来即是

update  T_test		
set	
    create_time = case
					when sid='111' then cast('2025-03-11 09:12:00' as timestamp)
					when sid='222' then cast('2025-03-11 09:13:00' as timestamp)
					when sid='333' then cast('2025-03-11 09:14:00' as timestamp)
					when sid='444' then cast('2025-03-11 09:15:00' as timestamp)
					when sid='555' then cast('2025-03-11 09:16:00' as timestamp)
	              end,
	pbil_id = case
				when sid='111' then 'pbil_id_111'
				when sid='222' then 'pbil_id_222'
				when sid='333' then 'pbil_id_333'
				when sid='444' then 'pbil_id_444'
				when sid='555' then 'pbil_id_555'
			  end
where sid in (
	'111', '222', '333', '444', '555'
)	

表结构如下

create table T_test (

	id  		serial,
	sid  		varchar(32),
	create_time timestamp,
	pbil_id  	varchar(32)
)
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值