Postgres CopyManager and connection from Connection Pool-蒲公英云

Postgres CopyManager and connection from Connection Pool

迷南。 2022-05-07 20:06 285阅读 0赞

1. PG CopyManager的使用示例代码：

package test.simple;
//You need to include postgres jdbc jar into your project lib
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import java.io.FileInputStream;
import java.io.FileWriter;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Properties;
import java.io.File;
/**
 * Created with IntelliJ IDEA.
 * User: leon
 * Date: 13-2-5
 * Time: 下午12:18
 * To change this template use File | Settings | File Templates.
 */
public class TestCopy {
    public static void main(String args[])throws Exception{
        Class.forName("org.postgresql.Driver");
        String url = "jdbc:postgresql://localhost:5432/postgres";
        Properties props = new Properties();
        props.setProperty("user", "postgres");
        props.setProperty("password", "postgres");
        Connection conn = DriverManager.getConnection(url, props);
        CopyManager cm = new CopyManager((BaseConnection)conn);
        File file = new File("a.txt");
        if(!file.exists()){
            file.createNewFile();
        }
        /*FileWriter fw = new FileWriter(file);
        cm.copyOut("COPY test_delete TO STDOUT WITH DELIMITER AS '|'", fw);
        fw.close();*/
        FileInputStream fis = new FileInputStream(file);
        cm.copyIn("COPY test_delete FROM STDIN WITH DELIMITER AS '|'", fis);
        fis.close();
    }
}

非常简单，将表中数据导出到txt文件中，可以自己增加一步压缩导出的数据文件，可以大大减少文件所占磁盘空间。

2. connection from Connection Pool

但遗憾的是CopyManager是PG的特有功能，使用CopyManager的话，必须从BaseConnection(就是PG的Connection)中获取。而实际项目中通常会使用到数据库连接池。
研究了一下，从PG连接池取到的connection无法强制转化成BaseConnection。
看下简单的PG 连接池代码，并将其中的一个connection转化成BaseConnection时的错误:

import java.sql.Connection;
import org.postgresql.core.BaseConnection;
import org.postgresql.ds.PGPoolingDataSource;
public class PGPool {
    PGPoolingDataSource source = new PGPoolingDataSource();
    public PGPool(){
        source.setServerName("localhost");
        source.setPortNumber(5432);
        source.setDatabaseName("postgres");
        source.setUser("postgres");
        source.setPassword("postgres");
        source.setMaxConnections(10);
    }
    public Connection getConn(){
        try{
            return source.getConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    public static void main(String args[]){
        PGPool pool = new PGPool();
        Connection con = pool.getConn();
        BaseConnection bConn = (BaseConnection) con;
    }
}
Exception in thread "main" java.lang.ClassCastException: $Proxy0 cannot be cast to org.postgresql.core.BaseConnection
    at PGPool.main(PGPool.java:35)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:601)
    at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

完整的代码样例如下(从连接池中获取一个连接,连进行PG的CopyManager操作)：

/**
 * Created with IntelliJ IDEA.
 * User: leon
 * Date: 13-2-12
 * Time: 下午8:20
 * To change this template use File | Settings | File Templates.
 */
import java.io.File;
import java.io.FileInputStream;
import java.sql.Connection;
//Remember, You need to include PG jdbc jar into your project build path.
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;
import org.postgresql.ds.PGPoolingDataSource;
public class PGPool {
    PGPoolingDataSource source = new PGPoolingDataSource();
    public PGPool(){
        //PG database server name
        source.setServerName("localhost");
        //PG db port number
        source.setPortNumber(5432);
        //PG database name
        source.setDatabaseName("postgres");
        source.setUser("postgres");
        source.setPassword("postgres");
        source.setMaxConnections(10);
    }
    public Connection getConn(){
        try{
            return source.getConnection();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
    public static void main(String args[]) throws Exception{
        PGPool pool = new PGPool();
        Connection con = pool.getConn();
        CopyManager cm = new CopyManager((BaseConnection)con.getMetaData().getConnection());
        File file = new File("a.txt");
        if(!file.exists()){
            file.createNewFile();
        }
        FileInputStream fis = new FileInputStream(file);
        //According to our simple example, your should have a table named test_delete in your database
        cm.copyIn("COPY test_delete FROM STDIN WITH DELIMITER AS '|'", fis);
        fis.close();
    }
}