示例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.opengauss.copy.CopyManager;
  8. import org.opengauss.core.BaseConnection;
  9. public class Copy{
  10. public static void main(String[] args)
  11. {
  12. String urls = new String("jdbc:opengauss://localhost:8000/postgres"); //数据库URL
  13. String username = new String("username"); //用户名
  14. String password = new String("passwd"); //密码
  15. String tablename = new String("migration_table"); //定义表信息
  16. String tablename1 = new String("migration_table_1"); //定义表信息
  17. String driver = "org.opengauss.Driver";
  18. Connection conn = null;
  19. try {
  20. Class.forName(driver);
  21. conn = DriverManager.getConnection(urls, username, password);
  22. } catch (ClassNotFoundException e) {
  23. e.printStackTrace(System.out);
  24. } catch (SQLException e) {
  25. e.printStackTrace(System.out);
  26. }
  27. // 将表migration_table中数据导出到本地文件d:/data.txt
  28. try {
  29. copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");
  30. } catch (SQLException e) {
  31. // TODO Auto-generated catch block
  32. e.printStackTrace();
  33. } catch (IOException e) {
  34. // TODO Auto-generated catch block
  35. e.printStackTrace();
  36. }
  37. //将d:/data.txt中的数据导入到migration_table_1中。
  38. try {
  39. copyFromFile(conn, "d:/data.txt", tablename1);
  40. } catch (SQLException e) {
  41. // TODO Auto-generated catch block
  42. e.printStackTrace();
  43. } catch (IOException e) {
  44. // TODO Auto-generated catch block
  45. e.printStackTrace();
  46. }
  47. // 将表migration_table_1中的数据导出到本地文件d:/data1.txt
  48. try {
  49. copyToFile(conn, "d:/data1.txt", tablename1);
  50. } catch (SQLException e) {
  51. // TODO Auto-generated catch block
  52. e.printStackTrace();
  53. } catch (IOException e) {
  54. // TODO Auto-generated catch block
  55. e.printStackTrace();
  56. }
  57. }
  58. public static void copyFromFile(Connection connection, String filePath, String tableName)
  59. throws SQLException, IOException {
  60. FileInputStream fileInputStream = null;
  61. try {
  62. CopyManager copyManager = new CopyManager((BaseConnection)connection);
  63. fileInputStream = new FileInputStream(filePath);
  64. copyManager.copyIn("COPY " + tableName + " FROM STDIN ", fileInputStream);
  65. } finally {
  66. if (fileInputStream != null) {
  67. try {
  68. fileInputStream.close();
  69. } catch (IOException e) {
  70. e.printStackTrace();
  71. }
  72. }
  73. }
  74. }
  75. public static void copyToFile(Connection connection, String filePath, String tableOrQuery)
  76. throws SQLException, IOException {
  77. FileOutputStream fileOutputStream = null;
  78. try {
  79. CopyManager copyManager = new CopyManager((BaseConnection)connection);
  80. fileOutputStream = new FileOutputStream(filePath);
  81. copyManager.copyOut("COPY " + tableOrQuery + " TO STDOUT", fileOutputStream);
  82. } finally {
  83. if (fileOutputStream != null) {
  84. try {
  85. fileOutputStream.close();
  86. } catch (IOException e) {
  87. e.printStackTrace();
  88. }
  89. }
  90. }
  91. }
  92. }