示例1：通过本地文件导入导出数据

在使用JAVA语言基于openGauss进行二次开发时，可以使用CopyManager接口，通过流方式，将数据库中的数据导出到本地文件或者将本地文件导入数据库中，文件格式支持CSV、TEXT等格式。

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

1. import java.sql.Connection;
2. import java.sql.DriverManager;
3. import java.io.IOException;
4. import java.io.FileInputStream;
5. import java.io.FileOutputStream;
6. import java.sql.SQLException;
7. import org.postgresql.copy.CopyManager;
8. import org.postgresql.core.BaseConnection;

10. public class Copy{

12. public static void main(String[] args)
13. {
14. String urls = new String("jdbc:postgresql://localhost:8000/postgres"); //数据库URL
15. String username = new String("username");            //用户名
16. String password = new String("passwd");             //密码
17. String tablename = new String("migration_table"); //定义表信息
18. String tablename1 = new String("migration_table_1"); //定义表信息
19. String driver = "org.postgresql.Driver";
20. Connection conn = null;

22. try {
23. Class.forName(driver);
24. conn = DriverManager.getConnection(urls, username, password);
25. } catch (ClassNotFoundException e) {
26. e.printStackTrace(System.out);
27. } catch (SQLException e) {
28. e.printStackTrace(System.out);
29. }

31. // 将表migration_table中数据导出到本地文件d:/data.txt
32. try {
33. copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");
34. } catch (SQLException e) {
35. // TODO Auto-generated catch block
36. e.printStackTrace();
37. } catch (IOException e) {
38. // TODO Auto-generated catch block
39. e.printStackTrace();
40. }
41. //将d:/data.txt中的数据导入到migration_table_1中。
42. try {
43. copyFromFile(conn, "d:/data.txt", tablename1);
44. } catch (SQLException e) {
45. // TODO Auto-generated catch block
46. e.printStackTrace();
47. } catch (IOException e) {
48. // TODO Auto-generated catch block
49. e.printStackTrace();
50. }

52. // 将表migration_table_1中的数据导出到本地文件d:/data1.txt
53. try {
54. copyToFile(conn, "d:/data1.txt", tablename1);
55. } catch (SQLException e) {
56. // TODO Auto-generated catch block
57. e.printStackTrace();
58. } catch (IOException e) {
59. // TODO Auto-generated catch block
60. e.printStackTrace();
61. }
62. }

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

67. FileInputStream fileInputStream = null;

69. try {
70. CopyManager copyManager = new CopyManager((BaseConnection)connection);
71. fileInputStream = new FileInputStream(filePath);
72. copyManager.copyIn("COPY " + tableName + " FROM STDIN with (" + "DELIMITER"+"'"+ delimiter +  "'" + "ENCODING " + "'" + encoding + "')", fileInputStream);
73. } finally {
74. if (fileInputStream != null) {
75. try {
76. fileInputStream.close();
77. } catch (IOException e) {
78. e.printStackTrace();
79. }
80. }
81. }
82. }
83. public static void copyToFile(Connection connection, String filePath, String tableOrQuery)
84. throws SQLException, IOException {

86. FileOutputStream fileOutputStream = null;

88. try {
89. CopyManager copyManager = new CopyManager((BaseConnection)connection);
90. fileOutputStream = new FileOutputStream(filePath);
91. copyManager.copyOut("COPY " + tableOrQuery + " TO STDOUT", fileOutputStream);
92. } finally {
93. if (fileOutputStream != null) {
94. try {
95. fileOutputStream.close();
96. } catch (IOException e) {
97. e.printStackTrace();
98. }
99. }
100. }
101. }
102. }