示例2：从MY迁移数据

下面示例演示如何通过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;

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

11. public class Migration{

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

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

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

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

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

49. //提交缓存buffer中的数据
50. CopyManager cp = new CopyManager(baseConn);
51. StringReader reader = new StringReader(buffer.toString());
52. cp.copyIn(sql, reader);
53. baseConn.commit();
54. reader.close();
55. baseConn.close();
56. } catch (ClassNotFoundException e) {
57. e.printStackTrace(System.out);
58. } catch (SQLException e) {
59. e.printStackTrace(System.out);
60. }

62. } catch (Exception e) {
63. e.printStackTrace();
64. }
65. }

67. //********************************
68. // 从源数据库返回查询结果集
69. //*********************************
70. private static ResultSet getDataSet() {
71. ResultSet rs = null;
72. try {
73. Class.forName("com.MY.jdbc.Driver").newInstance();
74. Connection conn = DriverManager.getConnection("jdbc:MY://10.119.179.227:3306/jack?useSSL=false&allowPublicKeyRetrieval=true", "jack", "xxxxxxxxx");
75. Statement stmt = conn.createStatement();
76. rs = stmt.executeQuery("select * from migration_table");
77. } catch (SQLException e) {
78. e.printStackTrace();
79. } catch (Exception e) {
80. e.printStackTrace();
81. }
82. return rs;
83. }
84. }