AnalyticDB:Write data by using the Client SDK

When to use the Client SDK

Use the following table to choose the right tool for your use case:

The SDK outperforms direct COPY or INSERT for high-volume writes because its internal parallel processing batches and commits data efficiently. For SQL operations other than bulk writes, retrieve a JDBC connection from the SDK using getConnection() and use standard JDBC interfaces.

Prerequisites

Before you begin, ensure that you have:

• An AnalyticDB for PostgreSQL instance with a connection address and port

• A database user with write permissions on the target tables

• Maven or a compatible Java build tool

Install the SDK

<dependency>
  <groupId>com.alibaba.cloud.analyticdb</groupId>
  <artifactId>adb4pgclient</artifactId>
  <version>1.0.16</version>
</dependency>

Quick start

The following example shows the complete write workflow: configure the connection, add rows to the buffer, and commit.

public class Adb4pgClientUsage {
    public void demo() {
        // 1. Configure the connection
        DatabaseConfig databaseConfig = new DatabaseConfig();
        databaseConfig.setHost("100.100.100.100");   // Connection address of the instance
        databaseConfig.setPort(8888);                // Default: 5432
        databaseConfig.setUser("your user name");
        databaseConfig.setPassword("your password");
        databaseConfig.setDatabase("your database name");

        // 2. Specify the target tables and columns
        // Call addTable separately for tables in different schemas.
        // Pass null for the schema to use the default schema 'public'.
        List<String> tables = new ArrayList<String>();
        tables.add("your table name 1");
        tables.add("your table name 2");
        databaseConfig.addTable(tables, "table schema name");

        // Specify columns per table. Use "*" to write to all columns.
        List<String> columns = new ArrayList<String>();
        columns.add("column1");
        columns.add("column2");
        databaseConfig.setColumns(columns, "your table name 1", "table schema name");
        databaseConfig.setColumns(Collections.singletonList("*"), "your table name 2", "table schema name");

        // 3. Set optional write behavior (all settings must be configured before creating the client)
        databaseConfig.setEmptyAsNull(false);      // Whether to treat empty strings as null. Default: false.
        databaseConfig.setInsertIgnore(true);      // Whether to ignore rows that cause primary key conflicts. Default: false (conflicts overwrite existing rows).
        databaseConfig.setRetryTimes(3);           // Retry count on commit exception. Default: 3.
        databaseConfig.setRetryIntervalTime(1000); // Retry interval in milliseconds. Default: 1000.

        // 4. Initialize the client. No DatabaseConfig changes take effect after this point.
        Adb4pgClient adbClient = new Adb4pgClient(databaseConfig);

        // 5. Add rows to the buffer using setColumn (creates a new Row per record)
        for (int i = 0; i < 10; i++) {
            Row row = new Row(columns.size());
            row.setColumn(0, i);                   // Column index must match the column order set above
            row.setColumn(1, "string value");
            // If the buffer exceeds commitSize, an autocommit triggers before the new row is added.
            // Handle AdbClientException with error code COMMIT_ERROR_DATA_LIST if autocommit fails.
            adbClient.addRow(row, "your table name 1", "table schema name");
        }

        // Use updateColumn when you want to reuse the same Row instance across records
        Row row = new Row();
        row.setColumn(0, 10);
        row.setColumn(1, "2018-01-01 08:00:00");
        adbClient.addRow(row, "your table name 1", "table schema name");
        row.updateColumn(0, 11);
        row.updateColumn(1, "2018-01-02 08:00:00");
        adbClient.addRow(row, "your table name 1", "table schema name");

        // Add rows in Map format (column name to value)
        Map<String, String> rowMap = new HashMap<String, String>();
        rowMap.put("t1", "12");
        rowMap.put("t2", "string value");
        adbClient.addMap(rowMap, "your table name 2", "table schema name");

        // 6. Commit the buffer. Data is written to AnalyticDB for PostgreSQL only after a successful commit.
        try {
            adbClient.commit();
        } catch (Exception e) {
            // Handle exception: inspect the error code and decide whether to retry or log and skip
        } finally {
            adbClient.stop(); // Releases thread pools. Throws an exception if uncommitted data remains.
        }
    }
}

API reference

Usage notes