openGauss example: import and export data through local files
Published: 2023-03-08 03:36:43

When using the Java language for secondary development based on openGauss, you can use the CopyManager interface to export the data in the database to a local file or import the local file into the database through the streaming method. The file format supports CSV, TEXT and other formats.

The sample program is as follows, and the openGauss JDBC driver needs to be loaded during execution.

```
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.opengauss.copy.CopyManager;
import org.opengauss.core .BaseConnection;

public class Copy{

public static void main(String[] args)
{
String urls = new String("jdbc:opengauss://10.180.155.74:8000/postgres"); //Database URL
String username = new String ("jack"); //User name
String password = new String("Gauss@123"); //Password
String tablename = new String("migration_table"); //Define table information
String tablename1 = new String("migration_table_1 ");//Define table information
String driver = "org.opengauss.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 the data in d:/data.txt into migration_table_1.
try {
copyFromFile(conn, "d:/data.txt", tablename1);
} catch (SQLException e) {
e.printStackTrace();
} catch (IOException e) {

e.printStackTrace();
}

// will migrate_table_1 Export the data to the local file d:/data1.txt
try {
copyToFile(conn, "d:/data1.txt", tablename1);
} catch (SQLException e) {

e.printStackTrace();
} catch (IOException e) {


e.printStackTrace();
}
}
// Use copyIn to import data from the file into 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) ; "COPY " + tableName + " FROM STDIN", fileInputStream); } finally { if (fileInputStream != null) { try { fileInputStream. close(); } catch (IOException e) { e. printStackTrace(); } }










}
}

// Use copyOut to export data from database to 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();
}
}
} }
}
```