PG_JDBC

PG_JDBC

CopyTest

package com.jdbc;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.Properties;
public class CopyTest {
   
    public static void main(String args[]) throws Exception {
   
        // System.out.println(copyToFile(getConnection(),"/Users/apple/Downloads/aa.txt","( SELECT * from sys_user where create_date >= '2017-11-01'\n)"));;
        copyFromFile(getConnection(), "D:\\code\\pgJDBC\\src\\main\\resources\\student.txt", "STUDENTS");
    }
    public static Connection getConnection() throws Exception {
   
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://192.168.1.104:5432/venus2";
        Properties props = new Properties();
        props.setProperty("user", "aaa");
        props.setProperty("password", "aaa");
        return DriverManager.getConnection(url, props);
    }
    /**
     * 导入文件到数据库
     *
     * @param connection
     * @param filePath
     * @param tableName
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public static long copyFromFile(Connection connection, String filePath, String tableName)
            throws SQLException, IOException {
   
        long count = 0;
        FileInputStream fileInputStream = null;
        try {
   
            CopyManager copyManager = new CopyManager((BaseConnection) connection);
            fileInputStream = new FileInputStream(filePath);
            count = copyManager.copyIn("COPY " + tableName + " FROM STDIN" + " DELIMITER AS ','", fileInputStream);
        } finally {
   
            if (fileInputStream != null) {
   
                try {
   
                    fileInputStream.close();
                } catch (IOException e) {
   
                    e.printStackTrace();
                }
            }
        }
        return count;
    }
    /**
     * 导出到txt
     *
     * @param connection
     * @param filePath
     * @param tableOrQuery 可以为tablename ,also (sql语句)
     * @return
     * @throws SQLException
     * @throws IOException
     */
    public static long copyToFile(Connection connection, String filePath, String tableOrQuery)
            throws SQLException, IOException {
   
        FileOutputStream fileOutputStream = null;
        long count = 0;
        try {
   
            CopyManager copyManager = new CopyManager((BaseConnection)