Help Center/ GaussDB/ Developer Guide(Centralized_3.x)/ Application Development Guide/ Development Based on JDBC/ Example: Importing and Exporting Data Through Local Files
Updated on 2024-05-07 GMT+08:00
View PDF
Share

Example: Importing and Exporting Data Through Local Files

When Java is used for secondary development based on GaussDB, you can use the CopyManager API to export data from the database to a local file or import a local file to the database by streaming. The file can be in CSV or TEXT format.

The example is as follows. Load the GaussDB JDBC driver before executing it. For details about how to obtain and load the driver, see JDBC Package, Driver Class, and Environment Class.

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
 
// There will be security risks if the username and password used for authentication are directly written into code. It is recommended that the username and password be stored in the configuration file or environment variables (the password must be stored in ciphertext and decrypted when being used) to ensure security.
// In this example, the username and password are stored in environment variables. Before running this example, set environment variables EXAMPLE_USERNAME_ENV and EXAMPLE_PASSWORD_ENV in the local environment (set the environment variable names based on the actual situation).
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://$ip:$port/postgres"); // Database URL
      String username = System.getenv("EXAMPLE_USERNAME_ENV");            // Username
      String password = System.getenv("EXAMPLE_PASSWORD_ENV");             // Password
      String tablename = new String("migration_table"); // Table information
      String tablename1 = new String("migration_table_1"); // Table information
      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); 
          } 
      
      // Export the query result of SELECT * FROM migration_table to the local file d:/data.txt.
      try {
     copyToFile(conn, "d:/data.txt", "(SELECT * FROM migration_table)");
   } catch (SQLException e) {
  
  e.printStackTrace();
   } catch (IOException e) {
 
  e.printStackTrace();
   }    
      // Import data from the d:/data.txt file to the migration_table_1 table.
      try {
      copyFromFile(conn, "d:/data.txt", tablename1);
   } catch (SQLException e) {
         e.printStackTrace();
 } catch (IOException e) {
  
  e.printStackTrace();
 }  

      // Export the data from the migration_table_1 table to the d:/data1.txt file.
      try {
      copyToFile(conn, "d:/data1.txt", tablename1);
   } catch (SQLException e) {
  
  e.printStackTrace();
   } catch (IOException e) {
  

  e.printStackTrace();
 }        
     } 
  // Use copyIn to import data from a file to the database.
  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();  
             }  
         }  
     }  
 }  
  
  // Use copyOut to export data from the database to a file.
  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();  
              }  
          }  
      }  
  }  
}