【Postgresql】PG文件导入导出数据库

Github

地址：https://github.com/ithuhui/hui-base-springboot/
分支：master

Note

Postgresql用的不习惯。这里记录一下Postgresql的通过文件导入到表，或者Postgresql表导出文件。

自己做了一点小改造，可以指定列名

这里有个注意，如果使用mybatis获取Connection，不能直接使用SqlSession.getConnection()

Code

maven

<!--PG SQL-->
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>${postgresql.version}</version>
</dependency>

工具类

 /**
     * 用文件导入数据到PG.(可以指定列名)
     *
     * @param connection the connection
     * @param filePath   the file path
     * @param tableName  the table name
     * @author : Hu weihui
     * @since hui_project v1
     */
    public static void importFromFile(Connection connection, String filePath, String tableName, String columnInfo) {
        try (FileInputStream fileInputStream = new FileInputStream(filePath)) {
            CopyManager copyManager = new CopyManager((BaseConnection) connection.getMetaData().getConnection());
            String sql = "";
            if (StringUtils.isEmpty(columnInfo)) {
                sql = String.format("COPY %s FROM STDIN WITH CSV", tableName);
            } else {
                sql = String.format("COPY %s (%s) FROM STDIN WITH CSV", tableName, columnInfo);
            }
            copyManager.copyIn(sql, fileInputStream);
        } catch (FileNotFoundException e) {
            log.error("[PostgreImportDataUtil]-copyFromFile Can not to find :{}", filePath);
        } catch (IOException e) {
            log.error("[PostgreImportDataUtil]-copyFromFile fail to get FileInputStream");
        } catch (SQLException e) {
            log.error("[PostgreImportDataUtil]-copyFromFile BAD SQL,{}", e);
        }
    }

    /**
     * 导出到文件.(tableName 或者 querySQL)
     *
     * @param connection   the connection
     * @param filePath     the file path
     * @param tableOrQuery the table or query
     * @throws SQLException the sql exception
     * @throws IOException  the io exception
     * @author : Hu weihui
     * @since hui_project v1
     */
    public static void copyToFile(Connection connection, String filePath, String tableOrQuery) throws SQLException, IOException {
        try (FileOutputStream fileOutputStream = new FileOutputStream(filePath)) {
            String sql = "";
            if (tableOrQuery.trim().startsWith("select")){
                sql = String.format("COPY (%s) TO STDOUT ",tableOrQuery);
            }else {
                sql = String.format("COPY %s TO STDOUT ",tableOrQuery);
            }
            CopyManager copyManager = new CopyManager((BaseConnection) connection.getMetaData().getConnection());
            copyManager.copyOut(sql, fileOutputStream);
        }
    }

Mybatis使用该工具

@Autowired
private SqlSessionTemplate sqlSessionTemplate;

Connection connection = sqlSessionTemplate.getConfiguration().getEnvironment().getDataSource().getConnection();

PostgreImportDataUtil.importFromFile(connection,"D:/xxx/xxx",null);

Author

 作者：HuHui
 转载：欢迎一起讨论web和大数据问题,转载请注明作者和原文链接，感谢