批量传参样例:
[{"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)
)