postgreSQL库函数copyIn流式高效导入大批量数据,效率要比使用copy从文件中导入更快

一、在自己本机postgresDB创建测试表如下：

create table student (
    id          serial constraint student_pkey primary key,
    sex         integer,
    name        varchar(1024) not null,
    birthday    timestamp(6),
    create_time timestamp(6) default now(),
    update_time timestamp(6)
);
create index idx_student_id  on student (id);

表中数据如下:
控制台输出pg表数据

db=#copy student to stdout(delimiter '|');
db=#select * from student;
 id | sex |  name  |      birthday       | create_time       | update_time 
----+-----+--------+---------------------+------+---------------------+-------------
  1 |   2 | 李霞   | 2021-08-03 23:23:04 |2021-08-03 23:23:08 |
  2 |   1 | 李四   | 2021-08-03 23:23:34 |2021-08-03 23:23:36 |
  3 |   1 | 张三   | 2021-08-03 23:22:31 |2021-08-03 23:20:39 |

二、下面是实现怎么快速使用copyIn导入数据到PG库。

import java.io.ByteArrayInputStream;
import java.io.InputStream;
import java.nio.charset.StandardCharsets;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.ArrayList;
import java.util.List;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

import lombok.extern.slf4j.Slf4j;

/**
 * stream import data to postgres db
 *
 * @author houxiurong.com 
 * @date 2021-08-05
 */
@Slf4j
public class PostgresCopyInDemo {
    private static String url = "jdbc:postgresql://localhost:5432/student";
    private static String user = "postgres";
    private static String password = "";
    public static void main(String[] args) {
        //id,sex,name,birthday,geom,create_time,update_time\n"

        //3|1|张三|2021-08-03 23:22:31|2021-08-03 23:20:39|

        //4|1|赵丽丽|2021-05-03 23:24:16|2021-05-03 23:24:20|

        String studentTable = "student";

        List<String> studentList = new ArrayList<>();

        studentList.add("11|1|张三|2021-08-07 23:24:16|2021-05-03 23:24:20|");

        studentList.add("12|1|李四|2021-08-07 23:24:16|2021-05-03 23:24:20|");

        StringBuilder stringBuilder = new StringBuilder();

        studentList.forEach(m -> stringBuilder.append(m + "\r\n"));

        try (Connection connection = DriverManager.getConnection(url, user, password); 
             InputStream inputStream = new ByteArrayInputStream(stringBuilder.toString().getBytes(StandardCharsets.UTF_8))) {
            log.info("init PGConnectPool.");
            CopyManager copyManager = new CopyManager((BaseConnection) connection);
            String copyIn = "COPY " + studentTable + " FROM STDIN DELIMITER E'|' CSV";
            long copyInLine = copyManager.copyIn(copyIn, inputStream);
            log.info("import db copyInLine={}", copyInLine);
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}

三、测试结果如下：

> Task :CopyInDemo.main()
23:54:28.567 [main] INFO com.houxiurong.PostgresCopyInDemo - init PGConnectPool.
23:54:28.624 [main] INFO com.houxiurong.PostgresCopyInDemo - import db copyInLine=2
BUILD SUCCESSFUL in 1s
3 actionable tasks: 2 executed, 1 up-to-date
23:54:28: Task execution finished 'CopyInDemo.main()'.