This topic describes how to import data to Hologres by using Java Database Connectivity (JDBC).
Limits
To import data to Hologres, you can use only PostgreSQL JDBC Driver version 42.2.2 or later.
Usage notes
- We recommend that you use a virtual private cloud (VPC) network to test data import performance after you connect to Hologres.
- Hologres does not support multiple inserts in a transaction. Therefore, if you need
to write data to Hologres multiple times in a transaction, you must set auto-commit
to true. If you use PostgreSQL JDBC Driver, the auto-commit mode is true by default.
Execute the following statement to set the auto-commit mode:
Connection conn = DriverManager.getConnection(url, user, password); conn.setAutoCommit(true);
If the
ERROR:INSERT in transaction is not supported now
error occurred, you must disable the auto-commit mode.
Connect to Hologres by using JDBC
Example
Execute the following statements to import data to Hologres:
package test;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
public class WriteHolo {
private static void Init(Connection conn) throws Exception {
try (Statement stmt = conn.createStatement()) {
stmt.execute("drop table if exists test_tb;");
stmt.execute("create table if not exists test_tb(pk bigint primary key, f1 text, f2 timestamptz, f3 float);");
}
}
private static void WriteBatchWithPreparedStatement(Connection conn) throws Exception {
try (PreparedStatement stmt = conn.prepareStatement("insert into test_tb values (?,?,?,?)")) {
int batchSize = 256;
for (int i = 0; i < batchSize; i) {
stmt.setInt( 1, 1000 i);
stmt.setString( 2, "1");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date parsedDate = dateFormat.parse("1990-11-11 00:00:00");
stmt.setTimestamp( 3, new java.sql.Timestamp(parsedDate.getTime()));
stmt.setDouble( 4, 0.1);
stmt.addBatch();
}
stmt.executeBatch();
}
}
private static void InsertOverwrite(Connection conn) throws Exception {
try (PreparedStatement stmt = conn.prepareStatement("insert into test_tb values (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?), (?,?,?,?) "
"on conflict(pk) do update set f1 = excluded.f1, f2 = excluded.f2, f3 = excluded.f3")) {
int batchSize = 6;
for (int i = 0; i < batchSize; i) {
stmt.setInt(i * 4 1, i);
stmt.setString(i * 4 2, "1");
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
Date parsedDate = dateFormat.parse("1990-11-11 00:00:00");
stmt.setTimestamp(i * 4 3, new java.sql.Timestamp(parsedDate.getTime()));
stmt.setDouble(i * 4 4, 0.1);
}
int affected_rows = stmt.executeUpdate();
System.out.println("affected rows => " affected_rows);
}
}
public static void main(String[] args) throws Exception {
Class.forName("org.postgresql.Driver").newInstance();
String host = "127.0.0.1:13737";
String db = "postgres";
String user = "xx";
String password = "xx";
String url = "jdbc:postgresql://" host "/" db "? reWriteBatchedInserts=true";
try (Connection conn = DriverManager.getConnection(url, user, password)) {
Init(conn);
WriteBatchWithPreparedStatement(conn);
InsertOverwrite(conn);
}
}
}