示例：通过本地文件导入导出数据

在使用JAVA语言基于openGauss进行二次开发时，可以使用CopyManager接口，通过流方式，将数据库中的数据导出到本地文件或者将本地文件导入数据库中，文件格式支持CSV、TEXT等格式。

样例程序如下，执行时需要加载openGauss jdbc驱动。

  1. import java.sql.Connection; 
  2. import java.sql.DriverManager; 
  3. import java.io.IOException;
  4. import java.io.FileInputStream;
  5. import java.io.FileOutputStream;
  6. import java.sql.SQLException; 
  7. import org.postgresql.copy.CopyManager; 
  8. import org.postgresql.core.BaseConnection;
  10. public class Copy{ 
  12.      public static void main(String[] args) 
  13.      { 
  14.       String urls = new String("jdbc:postgresql://10.180.155.74:8000/postgres"); //数据库URL 
  15.       String username = new String("jack");            //用户名 
  16.       String password = new String("Gauss@123");             //密码 
  17.       String tablename = new String("migration_table"); //定义表信息 
  18.       String tablename1 = new String("migration_table_1"); //定义表信息 
  19.       String driver = "org.postgresql.Driver"; 
  20.       Connection conn = null; 
  22.       try { 
  23.             Class.forName(driver); 
  24.             conn = DriverManager.getConnection(urls, username, password);         
  25.           } catch (ClassNotFoundException e) { 
  26.                e.printStackTrace(System.out); 
  27.           } catch (SQLException e) { 
  28.                e.printStackTrace(System.out); 
  29.           } 
  31.       // 将SELECT * FROM migration_table查询结果导出到本地文件d:/data.txt  
  32.       try {
  33.      copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");
  34.    } catch (SQLException e) {
  35.   k
  36.   e.printStackTrace();
  37.    } catch (IOException e) {
  39.   e.printStackTrace();
  40.    }    
  41.       //将d:/data.txt中的数据导入到migration_table_1中。
  42.       try {
  43.       copyFromFile(conn, "d:/data.txt", tablename1);
  44.    } catch (SQLException e) {
  45.          e.printStackTrace();
  46.  } catch (IOException e) {
  48.   e.printStackTrace();
  49.  }  
  51.       // 将migration_table_1中的数据导出到本地文件d:/data1.txt  
  52.       try {
  53.       copyToFile(conn, "d:/data1.txt", tablename1);
  54.    } catch (SQLException e) {
  56.   e.printStackTrace();
  57.    } catch (IOException e) {
  60.   e.printStackTrace();
  61.  }        
  62.      } 
  63.   // 使用copyIn把数据从文件中导入数据库，
  64.   public static void copyFromFile(Connection connection, String filePath, String tableName)   
  65.          throws SQLException, IOException {  
  67.      FileInputStream fileInputStream = null;  
  69.      try {  
  70.          CopyManager copyManager = new CopyManager((BaseConnection)connection);  
  71.          fileInputStream = new FileInputStream(filePath);  
  72.          copyManager.copyIn("COPY " + tableName + " FROM STDIN", fileInputStream);  
  73.      } finally {  
  74.          if (fileInputStream != null) {  
  75.              try {  
  76.                  fileInputStream.close();  
  77.              } catch (IOException e) {  
  78.                  e.printStackTrace();  
  79.              }  
  80.          }  
  81.      }  
  82.  }  
  84.   // 使用copyOut把数据从数据库中导出到文件中
  85.   public static void copyToFile(Connection connection, String filePath, String tableOrQuery)   
  86.           throws SQLException, IOException {  
  88.       FileOutputStream fileOutputStream = null;  
  90.       try {  
  91.           CopyManager copyManager = new CopyManager((BaseConnection)connection);  
  92.           fileOutputStream = new FileOutputStream(filePath);  
  93.           copyManager.copyOut("COPY " + tableOrQuery + " TO STDOUT", fileOutputStream);  
  94.       } finally {  
  95.           if (fileOutputStream != null) {  
  96.               try {  
  97.                   fileOutputStream.close();  
  98.               } catch (IOException e) {  
  99.                   e.printStackTrace();  
  100.               }  
  101.           }  
  102.       }  
  103.   }  
  104. }