云数据库 GaussDB-示例2：从MY迁移数据

时间：2023-11-01 16:22:04

云数据库 GaussDB

示例2：从MY迁移数据

下面示例演示如何通过CopyManager从MY向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.MY.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;    }}