postgresql 实现Mybatis数据库批量新增或更新

最新推荐文章于 2024-07-15 06:44:37 发布

书读百遍其义自见

最新推荐文章于 2024-07-15 06:44:37 发布

阅读量2.6k

点赞数 3

文章标签： mybatis 数据库 postgresql

本文链接：https://blog.csdn.net/qq_32495261/article/details/128907528

版权

之前工作中都是用的MySQL，现在的数据库换成了postgresql，和MySQL的（INSERT INTO VALUES ON DUPLICATE KEY UPDATE）有区别，所以记录一下。
在PG数据库中执行的SQL

INSERT INTO
    users (nickname, login_name, notes)
VALUES
    ('Tim2', 'tim', 'This is Tim2')
ON CONFLICT (login_name)
    DO UPDATE SET nickname = EXCLUDED.nickname,
                  notes = EXCLUDED.notes

mybatis拼接如下所示 , user_id是唯一键

INSERT INTO user_permission ("user_id", "user_name", "modify_date","unit", "deleted")
        VALUES
        <foreach collection="list" item="item" index="index" separator=",">
            (
            #{item.userId},
            #{item.userName},
            #{item.modifyDate},
            #{item.unit},
            #{item.deleted}
            )
        </foreach>
        on conflict (user_id) do update set
            user_name = EXCLUDED.user_name,
            unit = EXCLUDED.unit,
            modify_date = EXCLUDED.modify_date,
            deleted = EXCLUDED.deleted

参考资料