JDBC中使用COPY命令导出导入数据

JDBC中使用COPY命令导出导入数据
操作场景
使用Java语言开发应用程序时，通过调用JDBC驱动的CopyManager接口，从文件或其他数据库向GaussDB写入数据。

示例1：通过本地文件导出导入数据
调用JDBC驱动的CopyManager接口，通过流方式，将数据库中的数据导出到本地文件或者将本地文件导入数据库中，文件格式支持CSV、TEXT等格式。

样例程序如下，执行时需要加载GaussDB的JDBC驱动。

import java.sql.Connection;
import java.sql.DriverManager;
import java.io.IOException;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException;
import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

public class Copy{

 public static void main(String[] args) 
 { 
  String urls = new String("jdbc:postgresql://localhost:8000/postgres"); //数据库URL 
  String username = System.getenv("EXAMPLE_USERNAME_ENV");    //用户名
  String password = System.getenv("EXAMPLE_PASSWORD_ENV");    //密码
  String tablename = new String("migration_table"); //定义表信息 
  String tablename1 = new String("migration_table_1"); //定义表信息 
  String driver = "org.postgresql.Driver"; 
  Connection conn = null; 
  
  try { 
      Class.forName(driver); 
      conn = DriverManager.getConnection(urls, username, password);         
  } catch (ClassNotFoundException e) { 
       e.printStackTrace(System.out); 
  } catch (SQLException e) { 
       e.printStackTrace(System.out); 
  } 
  
  // 将表migration_table中数据导出到本地文件d:/data.txt  
  try {
      copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");
  } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
  } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
  }    
  //将d:/data.txt中的数据导入到migration_table_1中。
  try {
      copyFromFile(conn, "d:/data.txt", tablename1);
  } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
  } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
  }  

  // 将表migration_table_1中的数据导出到本地文件d:/data1.txt  
  try {
      copyToFile(conn, "d:/data1.txt", tablename1);
  } catch (SQLException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
  } catch (IOException e) {
      // TODO Auto-generated catch block
      e.printStackTrace();
  }        

}

public static void copyFromFile(Connection connection, String filePath, String tableName)
throws SQLException, IOException {

 FileInputStream fileInputStream = null;  

 try {  
     CopyManager copyManager = new CopyManager((BaseConnection)connection);  
     fileInputStream = new FileInputStream(filePath);  
     copyManager.copyIn("COPY " + tableName + " FROM STDIN ", fileInputStream);                        
 } finally {  
     if (fileInputStream != null) {  
         try {  
             fileInputStream.close();  
         } catch (IOException e) {  
             e.printStackTrace();  
         }  
     }  
 }  

}
public static void copyToFile(Connection connection, String filePath, String tableOrQuery)
throws SQLException, IOException {

  FileOutputStream fileOutputStream = null;  

  try {  
      CopyManager copyManager = new CopyManager((BaseConnection)connection);  
      fileOutputStream = new FileOutputStream(filePath);  
      copyManager.copyOut("COPY " + tableOrQuery + " TO STDOUT", fileOutputStream);  
  } finally {  
      if (fileOutputStream != null) {  
          try {  
              fileOutputStream.close();  
          } catch (IOException e) {  
              e.printStackTrace();  
          }  
      }  
  }  

}
}

示例2：从B迁移数据
下面示例演示如何通过CopyManager从B向GaussDB进行数据迁移的过程。

import java.io.StringReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import org.postgresql.copy.CopyManager;
import org.postgresql.core.BaseConnection;

public class Migration{

public static void main(String[] args) {
    String url = new String("jdbc:postgresql://localhost:8000/postgres"); //数据库URL 
    String user = new String("username");            //GaussDB数据库用户名 
    String pass = new String("passwd");             //GaussDB数据库密码 
    String tablename = new String("migration_table_1"); //定义表信息 
    String delimiter = new String("|");              //定义分隔符 
    String encoding = new String("UTF8");            //定义字符集 
    String driver = "org.postgresql.Driver";
    StringBuffer buffer = new StringBuffer();       //定义存放格式化数据的缓存 

    try {
        //获取源数据库查询结果集 
        ResultSet rs = getDataSet();

        //遍历结果集，逐行获取记录 
        //将每条记录中各字段值，按指定分隔符分割，由换行符结束，拼成一个字符串 
        //把拼成的字符串，添加到缓存buffer 
        while (rs.next()) {
            buffer.append(rs.getString(1) + delimiter
                    + rs.getString(2) + delimiter
                    + rs.getString(3) + delimiter
                    + rs.getString(4)
                    + "\n");
        }
        rs.close();

        try {
            //建立目标数据库连接 
            Class.forName(driver);
            Connection conn = DriverManager.getConnection(url, user, pass);
            BaseConnection baseConn = (BaseConnection) conn;
            baseConn.setAutoCommit(false);

            //初始化表信息   
            String sql = "Copy " + tablename + " from STDIN with (DELIMITER " + "'" + delimiter + "'" +","+ " ENCODING " + "'" + encoding + "')";

            //提交缓存buffer中的数据                   
            CopyManager cp = new CopyManager(baseConn);
            StringReader reader = new StringReader(buffer.toString());
            cp.copyIn(sql, reader);
            baseConn.commit();
            reader.close();
            baseConn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace(System.out);
        } catch (SQLException e) {
            e.printStackTrace(System.out);
        }

    } catch (Exception e) {
        e.printStackTrace();
    }
}

//******************************** 
// 从源数据库返回查询结果集     
//********************************* 
private static ResultSet getDataSet() {
    ResultSet rs = null;
    try {
        Class.forName("com.B.jdbc.Driver").newInstance();
        Connection conn = DriverManager.getConnection("jdbc:MY://10.119.179.227:3306/jack?useSSL=false&allowPublicKeyRetrieval=true", "jack", "xxxxxxxxx");
        Statement stmt = conn.createStatement();
        rs = stmt.executeQuery("select * from migration_table");
    } catch (SQLException e) {
        e.printStackTrace();
    } catch (Exception e) {
        e.printStackTrace();
    }
    return rs;
}

}

相关链接
有关CopyManager的更多信息，请参见：

CopyManager（分布式）
CopyManager（主备版）
父主题： 数据库迁移