示例:从MY向openGauss进行数据迁移

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

  1. import java.io.StringReader;
  2. import java.sql.Connection;
  3. import java.sql.DriverManager;
  4. import java.sql.ResultSet;
  5. import java.sql.SQLException;
  6. import java.sql.Statement;
  7. import org.postgresql.copy.CopyManager;
  8. import org.postgresql.core.BaseConnection;
  9. public class Migration{
  10. public static void main(String[] args) {
  11. String url = new String("jdbc:postgresql://10.180.155.74:8000/postgres"); //数据库URL
  12. String user = new String("jack"); //openGauss用户名
  13. String pass = new String("Gauss@123"); //openGauss密码
  14. String tablename = new String("migration_table"); //定义表信息
  15. String delimiter = new String("|"); //定义分隔符
  16. String encoding = new String("UTF8"); //定义字符集
  17. String driver = "org.postgresql.Driver";
  18. StringBuffer buffer = new StringBuffer(); //定义存放格式化数据的缓存
  19. try {
  20. //获取源数据库查询结果集
  21. ResultSet rs = getDataSet();
  22. //遍历结果集,逐行获取记录
  23. //将每条记录中各字段值,按指定分隔符分割,由换行符结束,拼成一个字符串
  24. //把拼成的字符串,添加到缓存buffer
  25. while (rs.next()) {
  26. buffer.append(rs.getString(1) + delimiter
  27. + rs.getString(2) + delimiter
  28. + rs.getString(3) + delimiter
  29. + rs.getString(4)
  30. + "\n");
  31. }
  32. rs.close();
  33. try {
  34. //建立目标数据库连接
  35. Class.forName(driver);
  36. Connection conn = DriverManager.getConnection(url, user, pass);
  37. BaseConnection baseConn = (BaseConnection) conn;
  38. baseConn.setAutoCommit(false);
  39. //初始化表信息
  40. String sql = "Copy " + tablename + " from STDIN DELIMITER " + "'" + delimiter + "'" + " ENCODING " + "'" + encoding + "'";
  41. //提交缓存buffer中的数据
  42. CopyManager cp = new CopyManager(baseConn);
  43. StringReader reader = new StringReader(buffer.toString());
  44. cp.copyIn(sql, reader);
  45. baseConn.commit();
  46. reader.close();
  47. baseConn.close();
  48. } catch (ClassNotFoundException e) {
  49. e.printStackTrace(System.out);
  50. } catch (SQLException e) {
  51. e.printStackTrace(System.out);
  52. }
  53. } catch (Exception e) {
  54. e.printStackTrace();
  55. }
  56. }
  57. //********************************
  58. // 从源数据库返回查询结果集
  59. //*********************************
  60. private static ResultSet getDataSet() {
  61. ResultSet rs = null;
  62. try {
  63. Class.forName("com.MY.jdbc.Driver").newInstance();
  64. Connection conn = DriverManager.getConnection("jdbc:MY://10.119.179.227:3306/jack?useSSL=false&allowPublicKeyRetrieval=true", "jack", "Gauss@123");
  65. Statement stmt = conn.createStatement();
  66. rs = stmt.executeQuery("select * from migration_table");
  67. } catch (SQLException e) {
  68. e.printStackTrace();
  69. } catch (Exception e) {
  70. e.printStackTrace();
  71. }
  72. return rs;
  73. }
  74. }