使用复制Postgres jdbc的正确方法 [英] Correct way to use copy Postgres jdbc

问题描述

无法与jdbc Postgres一起使用复制命令.以下代码段示例出了什么问题.

Unable to use copy command with jdbc Postgres. Whats wrong with the below code snippet sample.

public boolean loadReportToDB(String date) {
        // TODO Auto-generated method stub
        Connection connection = DBUtil.getConnection("POSTGRESS");
        String fileName = "C:/_0STUFF/NSE_DATA/nseoi_" + date + ".csv";
        String sql = "\\copy fno_oi FROM 'C:\\_0STUFF\\NSE_DATA\\nseoi_27102017.csv' DELIMITER ',' CSV header";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            System.out.println("query"+ps.toString());
            int rowsaffected = ps.executeUpdate();
            System.out.println("INT+" + rowsaffected);
            return true;
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }

        return false;
    }

org.postgresql.util.PSQLException: ERROR: syntax error at or near "\"
  Position: 1
    at org.

如果我们使用

String sql = "copy fno_oi FROM 'C:\\_0STUFF\\NSE_DATA\\nseoi_27102017.csv' DELIMITER ',' CSV header";

然后没有行会更新

postgres版本postgresql-10.0-1-windows-x64

postgres version postgresql-10.0-1-windows-x64

推荐答案

这对我有用:

try (Connection conn = DriverManager.getConnection(connUrl, myUid, myPwd)) {
    long rowsInserted = new CopyManager((BaseConnection) conn)
            .copyIn(
                "COPY table1 FROM STDIN (FORMAT csv, HEADER)", 
                new BufferedReader(new FileReader("C:/Users/gord/Desktop/testdata.csv"))
                );
    System.out.printf("%d row(s) inserted%n", rowsInserted);
}

使用copyIn(String sql, Reader from)的优点是可以避免PostgreSQL服务器进程无法直接读取文件的问题，这可能是因为它缺少权限(例如在我的桌面上读取文件)，或者是因为该文件不是本地计算机所在的机器PostgreSQL服务器正在运行.

Using copyIn(String sql, Reader from) has the advantage of avoiding issues where the PostgreSQL server process is unable to read the file directly, either because it lacks permissions (like reading files on my Desktop) or because the file is not local to the machine where the PostgreSQL server is running.

这篇关于使用复制Postgres jdbc的正确方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！