Postgres CopyManager and connection from Connection Pool

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

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

  1. package test.simple;
  2. //You need to include postgres jdbc jar into your project lib
  3. import org.postgresql.copy.CopyManager;
  4. import org.postgresql.core.BaseConnection;
  5. import java.io.FileInputStream;
  6. import java.io.FileWriter;
  7. import java.sql.Connection;
  8. import java.sql.DriverManager;
  9. import java.util.Properties;
  10. import java.io.File;
  11. /**
  12. * Created with IntelliJ IDEA.
  13. * User: leon
  14. * Date: 13-2-5
  15. * Time: 下午12:18
  16. * To change this template use File | Settings | File Templates.
  17. */
  18. public class TestCopy {
  19. public static void main(String args[])throws Exception{
  20. Class.forName("org.postgresql.Driver");
  21. String url = "jdbc:postgresql://localhost:5432/postgres";
  22. Properties props = new Properties();
  23. props.setProperty("user", "postgres");
  24. props.setProperty("password", "postgres");
  25. Connection conn = DriverManager.getConnection(url, props);
  26. CopyManager cm = new CopyManager((BaseConnection)conn);
  27. File file = new File("a.txt");
  28. if(!file.exists()){
  29. file.createNewFile();
  30. }
  31. /*FileWriter fw = new FileWriter(file);
  32. cm.copyOut("COPY test_delete TO STDOUT WITH DELIMITER AS '|'", fw);
  33. fw.close();*/
  34. FileInputStream fis = new FileInputStream(file);
  35. cm.copyIn("COPY test_delete FROM STDIN WITH DELIMITER AS '|'", fis);
  36. fis.close();
  37. }
  38. }

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


2. connection from Connection Pool

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

  1. import java.sql.Connection;
  2. import org.postgresql.core.BaseConnection;
  3. import org.postgresql.ds.PGPoolingDataSource;
  4. public class PGPool {
  5. PGPoolingDataSource source = new PGPoolingDataSource();
  6. public PGPool(){
  7. source.setServerName("localhost");
  8. source.setPortNumber(5432);
  9. source.setDatabaseName("postgres");
  10. source.setUser("postgres");
  11. source.setPassword("postgres");
  12. source.setMaxConnections(10);
  13. }
  14. public Connection getConn(){
  15. try{
  16. return source.getConnection();
  17. }catch (Exception e){
  18. e.printStackTrace();
  19. }
  20. return null;
  21. }
  22. public static void main(String args[]){
  23. PGPool pool = new PGPool();
  24. Connection con = pool.getConn();
  25. BaseConnection bConn = (BaseConnection) con;
  26. }
  27. }
  28. Exception in thread "main" java.lang.ClassCastException: $Proxy0 cannot be cast to org.postgresql.core.BaseConnection
  29. at PGPool.main(PGPool.java:35)
  30. at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
  31. at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
  32. at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
  33. at java.lang.reflect.Method.invoke(Method.java:601)
  34. at com.intellij.rt.execution.application.AppMain.main(AppMain.java:120)

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

  1. /**
  2. * Created with IntelliJ IDEA.
  3. * User: leon
  4. * Date: 13-2-12
  5. * Time: 下午8:20
  6. * To change this template use File | Settings | File Templates.
  7. */
  8. import java.io.File;
  9. import java.io.FileInputStream;
  10. import java.sql.Connection;
  11. //Remember, You need to include PG jdbc jar into your project build path.
  12. import org.postgresql.copy.CopyManager;
  13. import org.postgresql.core.BaseConnection;
  14. import org.postgresql.ds.PGPoolingDataSource;
  15. public class PGPool {
  16. PGPoolingDataSource source = new PGPoolingDataSource();
  17. public PGPool(){
  18. //PG database server name
  19. source.setServerName("localhost");
  20. //PG db port number
  21. source.setPortNumber(5432);
  22. //PG database name
  23. source.setDatabaseName("postgres");
  24. source.setUser("postgres");
  25. source.setPassword("postgres");
  26. source.setMaxConnections(10);
  27. }
  28. public Connection getConn(){
  29. try{
  30. return source.getConnection();
  31. }catch (Exception e){
  32. e.printStackTrace();
  33. }
  34. return null;
  35. }
  36. public static void main(String args[]) throws Exception{
  37. PGPool pool = new PGPool();
  38. Connection con = pool.getConn();
  39. CopyManager cm = new CopyManager((BaseConnection)con.getMetaData().getConnection());
  40. File file = new File("a.txt");
  41. if(!file.exists()){
  42. file.createNewFile();
  43. }
  44. FileInputStream fis = new FileInputStream(file);
  45. //According to our simple example, your should have a table named test_delete in your database
  46. cm.copyIn("COPY test_delete FROM STDIN WITH DELIMITER AS '|'", fis);
  47. fis.close();
  48. }
  49. }

转自:https://blog.csdn.net/leonskenedy/article/details/8579564

发表评论

表情:
评论列表 (有 0 条评论,280人围观)

还没有评论,来说两句吧...

相关阅读