云数据库 GaussDB-示例1：通过本地文件导入导出数据

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

云数据库 GaussDB

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

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

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

     
          import java.sql.Connection; import java.sql.DriverManager; import java.io.IOException;import java.io.FileInputStream;import java.io.FileOutputStream;import java.sql.SQLException; import org.postgresql.copy.CopyManager; import org.postgresql.core.BaseConnection; public class Copy{      public static void main(String[] args)      {       String urls = new String("jdbc:postgresql://localhost:8000/postgres"); //数据库URL       String username = new String("username");            //用户名       String password = new String("passwd");             //密码       String tablename = new String("migration_table"); //定义表信息       String tablename1 = new String("migration_table_1"); //定义表信息       String driver = "org.postgresql.Driver";       Connection conn = null;             try {           Class.forName(driver);           conn = DriverManager.getConnection(urls, username, password);               } catch (ClassNotFoundException e) {            e.printStackTrace(System.out);       } catch (SQLException e) {            e.printStackTrace(System.out);       }             // 将表migration_table中数据导出到本地文件d:/data.txt        try {          copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");      } catch (SQLException e) {          // TODO Auto-generated catch block          e.printStackTrace();      } catch (IOException e) {          // TODO Auto-generated catch block          e.printStackTrace();      }          //将d:/data.txt中的数据导入到migration_table_1中。      try {          copyFromFile(conn, "d:/data.txt", tablename1);      } catch (SQLException e) {          // TODO Auto-generated catch block          e.printStackTrace();      } catch (IOException e) {          // TODO Auto-generated catch block          e.printStackTrace();      }        // 将表migration_table_1中的数据导出到本地文件d:/data1.txt        try {          copyToFile(conn, "d:/data1.txt", tablename1);      } catch (SQLException e) {          // TODO Auto-generated catch block          e.printStackTrace();      } catch (IOException e) {          // TODO Auto-generated catch block          e.printStackTrace();      }          }   public static void copyFromFile(Connection connection, String filePath, String tableName)            throws SQLException, IOException {              FileInputStream fileInputStream = null;          try {           CopyManager copyManager = new CopyManager((BaseConnection)connection);           fileInputStream = new FileInputStream(filePath);           copyManager.copyIn("COPY " + tableName + " FROM STDIN ", fileInputStream);                             } finally {           if (fileInputStream != null) {               try {                   fileInputStream.close();               } catch (IOException e) {                   e.printStackTrace();               }           }       }   }       public static void copyToFile(Connection connection, String filePath, String tableOrQuery)             throws SQLException, IOException {                FileOutputStream fileOutputStream = null;           try {            CopyManager copyManager = new CopyManager((BaseConnection)connection);            fileOutputStream = new FileOutputStream(filePath);            copyManager.copyOut("COPY " + tableOrQuery + " TO STDOUT", fileOutputStream);        } finally {            if (fileOutputStream != null) {                try {                    fileOutputStream.close();                } catch (IOException e) {                    e.printStackTrace();                }            }        }    }  }