Postgres CopyManager and connection from Connection Pool

迷南。 2022-05-07 20:06 274阅读 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();
        }
    }

--------------------

##### 转自：[https://blog.csdn.net/leonskenedy/article/details/8579564][https_blog.csdn.net_leonskenedy_article_details_8579564] #####

[https_blog.csdn.net_leonskenedy_article_details_8579564]: https://blog.csdn.net/leonskenedy/article/details/8579564