Postgres + Mybatis 进行批量操作示例

1. 数据准备

在数据库的交互中，批量操作从整个功能上来说，肯定是效率最高的，比起循环里面单独去做事，会极大的提高接口性能和效率。

本篇文章介绍下，基于 Mybatis + Postgres 数据库，如何去进行数据的批量操作。

下面我们以书为例，包含书名、作者、价格三个属性，为此进行数据准备。

1.1 建表SQL

CREATE TABLE book (
	id bigserial,
	name varchar(20),
	author varchar(20),
	price int4,
	CONSTRAINT book_pk PRIMARY KEY (id)
);

1.2 Java 对象

@Data
public class Book {

    /** 主键 */
    @TableId(type = IdType.AUTO)
    private Long id;

    /** 书名 */
    private String name;

    /** 作者 */
    private String author;

    /** 单价（分） */
    private Integer price;

}

2. 批量操作

2.1 批量插入

2.1.1 Mapper 接口

    /**
     * 批量插入
     *
     * @param books 对象集合
     * @return 成功插入条数
     */
    Integer batchInsert(List<Book> books);

2.1.2 Mapper.xml

    <!-- 批量插入数据 -->
    <insert id="batchInsert">
        INSERT INTO book (name, author, price) VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.author}, #{item.price})
        </foreach>
    </insert>

2.1.3 数据模拟

        Book book1 = new Book();
        book1.setName("三国演义");
        book1.setAuthor("罗贯中");
        book1.setPrice(1980);

        Book book2 = new Book();
        book2.setName("红楼梦");
        book2.setAuthor("曹雪芹");
        book2.setPrice(2180);

        List<Book> bookList = new ArrayList<>();
        bookList.add(book1);
        bookList.add(book2);

        bookMapper.batchInsert(bookList);

2.1.4 结果展示

2.2 批量更新

2.2.1 Mapper 接口

    /**
     * 批量修改（根据id进行修改）
     *
     * @param books 修改对象集合
     * @return 成功修改条数
     */
    Integer batchUpdateById(List<Book> books);

2.2.2 Mapper.xml

    <!-- 根据ID批量修改对象 -->
    <update id="batchUpdateById">
        UPDATE book b SET name = a.name, author = a.author, price = a.price FROM (SELECT
            UNNEST(ARRAY<foreach collection="list" open="[" close="]" item="item" separator=","> #{item.id} </foreach>) AS id,
            UNNEST(ARRAY<foreach collection="list" open="[" close="]" item="item" separator=","> #{item.name} </foreach>) AS name,
            UNNEST(ARRAY<foreach collection="list" open="[" close="]" item="item" separator=","> #{item.author} </foreach>) AS author,
            UNNEST(ARRAY<foreach collection="list" open="[" close="]" item="item" separator=","> #{item.price} </foreach>) AS price
        ) AS a WHERE b.id = a.id
    </update>

2.2.3 数据模拟

        Book book1 = new Book();
        book1.setId(1L);
        book1.setName("西游记");
        book1.setAuthor("吴承恩");
        book1.setPrice(1780);

        Book book2 = new Book();
        book2.setId(2L);
        book2.setName("水浒传");
        book2.setAuthor("施耐庵");
        book2.setPrice(1680);

        List<Book> bookList = new ArrayList<>();
        bookList.add(book1);
        bookList.add(book2);

        bookMapper.batchUpdateById(bookList);

2.2.4 结果展示

2.3 批量新增（忽略冲突）

2.3.0 冲突原因

所谓冲突，就是在批量新增的时候，新增的实体中，可能和数据库的唯一性索引重复，导致批量插入报错（如下图）。

Cause: org.postgresql.util.PSQLException: ERROR: duplicate key value violates unique constraint “unique_name_index”

一般来说，如果数据冲突了，那么要么忽略，要么进行数据覆盖更新，在这里，我们首先演示下如何进行冲突忽略。

2.3.1 创建唯一索引

首先，要有冲突的话，需要有一个唯一性的索引，这里假设我们书名唯一，按下面 SQL 所示，在数据库中给 name 字段创建一个唯一性索引（索引名为 unique_name_index ）。

CREATE UNIQUE INDEX unique_name_index ON book (name);

2.3.2 Mapper 接口

    /**
     * 批量新增（忽略冲突），即插入的数据如果和唯一性索引冲突，则不进行处理
     *
     * @param books 对象集合
     * @return 成功插入条数
     */
    Integer batchInsertIgnoreConflict(List<Book> books);

2.3.3 Mapper.xml

可以看到，这里的SQL写法，只是比批量插入的SQL尾巴上多了一个 ON CONFLICT (name) DO NOTHING

	<!-- 批量新增，并忽略冲突（存在和唯一性索引重复的，则不再进行插入） -->
    <insert id="batchInsertIgnoreConflict">
        INSERT INTO book (name, author, price) VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.author}, #{item.price})
        </foreach>
        ON CONFLICT (name) DO NOTHING
    </insert>

2.3.4 数据模拟

模拟数据之前，我们可以先看到数据库中，只有四大名著四本书，现在我们新增两本，一本是数据库中已经存在的三国演义，一本则是数据中不存在的聊斋志异。

        // 这个对象数据库中已经存在（插入时存在冲突，但会被忽略）
        Book book1 = new Book();
        book1.setName("三国演义");
        book1.setAuthor("罗贯中");
        book1.setPrice(1888);

        // 这个对象数据库中不存在（成功插入到数据库）
        Book book2 = new Book();
        book2.setName("聊斋志异");
        book2.setAuthor("蒲松龄");
        book2.setPrice(2180);

        List<Book> bookList = new ArrayList<>();
        bookList.add(book1);
        bookList.add(book2);

        bookMapper.batchInsertIgnoreConflict(bookList);

2.3.5 结果展示

2.4 批量新增（冲突则更新）

简单的说就是批量新增或更新，即不冲突的数据进行新增，冲突的数据进行更新。
首先，你可以看下 2.3.1 和 2.3.2 了解到冲突的原因，以及创建一个导致冲突的唯一性索引。

2.4.1 Mapper

    /**
     * 批量新增或更新（和唯一性索引冲突的数据进行字段更新）
     *
     * @param books 对象集合
     * @return 插入&更新的成功条数
     */
    Integer batchInsertOrUpdate(List<Book> books);

2.4.2 Mapper.xml

这里和批量新增冲突忽略的 SQL 相比，把最后的 DO NOTHING，替换为了 DO UPATE，后面的 excluded 对象就指的是冲突对象。

    <!-- 批量新增或更新（和唯一性索引冲突的数据进行字段更新） -->
    <insert id="batchInsertOrUpdate">
        INSERT INTO book (name, author, price) VALUES
        <foreach collection="list" item="item" separator=",">
            (#{item.name}, #{item.author}, #{item.price})
        </foreach>
        ON CONFLICT (name) DO UPDATE SET author = excluded.author, price = excluded.price
    </insert>

2.4.3 数据模拟

现在我们数据中存在如下数据，现在我们准备新增一本书史记（不冲突数据），和一本三国演义（冲突数据，书价格我们这次改为1888）。

        // 这个对象数据库中已经存在（插入时存在冲突，但数据会被更新）
        Book book1 = new Book();
        book1.setName("三国演义");
        book1.setAuthor("罗贯中");
        book1.setPrice(1688);

        // 这个对象数据库中不存在（成功插入到数据库）
        Book book2 = new Book();
        book2.setName("史记");
        book2.setAuthor("司马迁");
        book2.setPrice(1980);

        List<Book> bookList = new ArrayList<>();
        bookList.add(book1);
        bookList.add(book2);

2.4.4 结果展示