调用CopyManager

CopyManager是openGauss JDBC驱动中提供的一个API接口类，用于批量向openGauss中导入数据。

获取Copy管理器

Connection connection = DriverManager.getConnection(URL,properties);
PGConnection pgConnection = connection.unwrap(PGConnection.class);
CopyManager copyManager = pgConnection.getCopyAPI();

使用Copy管理器

copyIn(String sql, InputStream from, int bufferSize)

使用COPY FROM STDIN从InputStream中快速向数据库中的表加载数据。例如：

Connection connection = DriverManager.getConnection(URL,properties);
PGConnection pgConnection = connection.unwrap(PGConnection.class);
CopyManager copyManager = pgConnection.getCopyAPI();
String sql = "COPY copytest FROM STDIN";
copyManager.copyIn(sql,
new ByteArrayInputStream(testInfo.getBytes()));

存在变形，即bufferSize为DEFAULT_BUFFER_SIZE copyIn(String sql, InputStream from)。

copyIn(String sql, Reader from, int bufferSize)

使用COPY FROM STDIN从Reader中快速向数据库中的表加载数据。例如：

Connection connection = DriverManager.getConnection(URL,properties);
PGConnection pgConnection = connection.unwrap(PGConnection.class);
CopyManager copyManager = pgConnection.getCopyAPI()
String sql = "COPY copytest FROM STDIN";
copyManager.copyIn(sql,
        new StringReader(testInfo));

存在变形，即bufferSize为DEFAULT_BUFFER_SIZE。

copyOut(String sql, OutputStream to)

将一个COPY TO STDOUT的结果集从数据库发送到OutputStream类中。

Connection connection = DriverManager.getConnection(URL,properties);
PGConnection pgConnection = connection.unwrap(PGConnection.class);
CopyManager copyManager = pgConnection.getCopyAPI();
String sql = "COPY copytest to STDOUT";
ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
copyManager.copyOut(sql,byteArrayOutputStream);

copyOut(String sql, Writer to)

将一个COPY TO STDOUT的结果集从数据库发送到Writer类中。

Connection connection = DriverManager.getConnection(URL,properties);
PGConnection pgConnection = connection.unwrap(PGConnection.class);
CopyManager copyManager = pgConnection.getCopyAPI();
String sql = "COPY copytest to STDOUT";
StringWriter stringWriter = new StringWriter();
copyManager.copyOut(sql,stringWriter);

从MY向Vastabase进行数据迁移

下面示例演示如何通过CopyManager从MY向VastBase进行数据迁移。

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://1.2.4.5:8000/postgres"); //数据库URL 
        String user = new String("jack");            //用户名 
        String pass = new String("Password@123");             //密码 
        String tablename = new String("migration_table"); //定义表信息 
        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 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.MY.jdbc.Driver").newInstance();
            Connection conn = DriverManager.getConnection("jdbc:MY://1.2.3.4:3306/jack?useSSL=false&allowPublicKeyRetrieval=true", "jack", "Password@123");
            Statement stmt = conn.createStatement();
            rs = stmt.executeQuery("select * from migration_table");
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return rs;
    }
}

VastbaseG100