【PostgreSQL】MyBatis使用List参数批量插入/更新数据

原创已于 2022-10-21 14:32:39 修改 · 4.5k 阅读

8 ·

CC 4.0 BY-SA版权

文章标签：

#postgresql

于 2022-05-31 23:00:30 首次发布

本文介绍了在PostgreSQL中，利用MyBatis进行数据批量插入和更新的三种方法。第一种方法是在遇到冲突时更新，适用于有主键的表；第二种方法是先更新再插入，适合Map或实体类参数；第三种方法通过concat连接where条件进行更新，但不适用于大数据量的表。

前两种方法，均可实现数据的批量插入/更新，具体选择哪种需根据具体情况，第三种方法仅用于批量更新数据，介绍如下：

1、先插入数据，遇到冲突时更新：

本方法要求表必须有主键，否则无法判断冲突。举例如下：

insert into xxtable
(col1, col2, col3)
values
<foreach collection="list" index="index" item="i" open="" close="" separator=",">
    (#{i.col1},
    <choose>
        <when test ="i.col2 != null and i.col2 != ''">
                cast(#{i.col2} as numeric), 
        </when>
        <otherwise>null, </otherwise>
    </choose>
    #{i.col3})
</foreach>
on conflict (col1, col2)
do update set
(col1, col2, col3) =
(EXCLUDED.col1, EXCLUDED.col2, EXCLUDED.col3)

注：如果希望主键冲突时不做任何操作，可以使用 on conflict (col1, ......) do nothing

2、先更新数据，无数据时插入：

本方法更适合使用Map或实体类参数，单条更新/插入数据，使用List参数时需另行改造。

with tmp as (update xxtable set col2 = 'xx', col3 = 'xxx' where col1= 'xxxx' returning *)
insert into xxtable (col1, col2, col3) select 'xxxx', 'xx', 'xxx' where (select count(*) from tmp) = 0

3、使用concat连接where条件以更新数据，本人理解是本SQL会全表扫描，不适合更新数据量较大的表

update tableName
set columnName1=
case concat(keyColumn1, keyColumn2, keyColumn3, ……) --括号中为where条件字段
<foreach collection="list" item="i" separator=" ">
when concat(#{i.keyColumn1}, #{i.keyColumn2}, #{i.keyColumn3}, ……) then #{i.valueColumn1}
</foreach>
else columnName1 end,
columnName2=
case concat(keyColumn1, keyColumn2, keyColumn3, ……)
<foreach collection="list" item="i" separator=" ">
when concat(#{i.keyColumn1}, #{i.keyColumn2}, #{i.keyColumn3}, ……) then #{i.valueColumn2}
</foreach>
else columnName2 end