Hologres:JDBC

Step 1: Add the driver dependency

Most SQL client tools include a built-in PostgreSQL driver — use it if available. For Java applications, add the PostgreSQL JDBC Driver to your Maven project. Download it from jdbc.postgresql.org/download and use version 42.3.2 or later (the latest stable version is recommended).

Hologres uses the standard PostgreSQL JDBC Driver — there is no separate Hologres-specific driver to install.

Add the following dependency to your pom.xml:

<dependencies>
    <dependency>
        <groupId>org.postgresql</groupId>
        <artifactId>postgresql</artifactId>
        <!-- Use 42.3.2 or later. The latest stable version is recommended. -->
        <version>42.7.13</version>
    </dependency>
</dependencies>

Step 2: Build the connection string

The connection string format is:

jdbc:postgresql://<ENDPOINT>:<PORT>/<DBNAME>?user=<ACCESS_ID>&password=<ACCESS_KEY>

Required parameters

Optional parameters

Append these to the connection string as needed. Separate multiple parameters with &.

About preferQueryMode

Under the extended protocol, PreparedStatement parameters are sent with explicit types and the driver does not perform implicit type conversion, so comparing an integer column with a varchar parameter can fail with an error such as ERROR: operator does not exist: integer = character varying. If you encounter this type-mismatch error, add preferQueryMode=simple to the connection string. The driver then sends the SQL as plain text and lets the server infer parameter types from context.

Example connection string with recommended options:

jdbc:postgresql://<ENDPOINT>:<PORT>/<DBNAME>?user=<ACCESS_ID>&password=<ACCESS_KEY>&ApplicationName=myApp&reWriteBatchedInserts=true

If a PreparedStatement query fails with an error such as ERROR: operator does not exist: integer = character varying due to a type mismatch between the query parameter and the column, add preferQueryMode=simple to the connection string:

jdbc:postgresql://<ENDPOINT>:<PORT>/<DBNAME>?user=<ACCESS_ID>&password=<ACCESS_KEY>&preferQueryMode=simple

Step 3: Store credentials in environment variables

Hardcoding credentials in connection strings creates a security risk. Store them as environment variables instead. On Linux, add the following to your ~/.bash_profile:

export ALIBABA_CLOUD_USER=<ACCESS_ID>
export ALIBABA_CLOUD_PASSWORD=<ACCESS_KEY>

Step 4: Connect and run a query

The following example reads credentials from environment variables, sets timeout properties, connects to Hologres, and runs a basic SELECT query using a standard Statement:

import org.postgresql.PGProperty;

import java.sql.*;
import java.util.Properties;

public class HologresTest {

    private void jdbcExample() throws SQLException {
        String user = System.getenv("ALIBABA_CLOUD_USER");
        String password = System.getenv("ALIBABA_CLOUD_PASSWORD");
        String url = String.format(
            "jdbc:postgresql://<ENDPOINT>:<PORT>/<DBNAME>?currentSchema=<SCHEMA_NAME>&user=%s&password=%s",
            user, password
        );
        Properties props = new Properties();
        // Set SOCKET_TIMEOUT based on actual query execution time to avoid
        // premature timeout before the query finishes.
        PGProperty.SOCKET_TIMEOUT.set(props, 3600);
        PGProperty.LOGIN_TIMEOUT.set(props, 60);
        PGProperty.TCP_KEEP_ALIVE.set(props, true);
        try (Connection conn = DriverManager.getConnection(url, props)) {
            try (Statement st = conn.createStatement()) {
                String sql = "SELECT pk, f1 FROM test_tb WHERE pk > 0 LIMIT 100";
                try (ResultSet rs = st.executeQuery(sql)) {
                    while (rs.next()) {
                        // Read the first column value
                        String c1 = rs.getString(1);
                    }
                }
            }
        }
    }
}

Write data

You can write data using the Statement or Prepared Statement mode in JDBC. Use Prepared Statement mode for write operations. In this mode, the server caches SQL compilation results, which reduces write latency and increases throughput. Set the batch size to a multiple of 256 — the minimum recommended batch size is 256.

The examples in this section use the following table. Create it in your database first.

CREATE TABLE test_tb (
    pk int PRIMARY KEY,
    f1 text,
    f2 timestamptz,
    f3 double precision
);

Batch insert

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;

/*
 * Write data in batches using Prepared Statement mode.
 * Batch size: 256 rows (minimum recommended).
 */
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");
            java.util.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();
    }
}

Upsert with INSERT ON CONFLICT

To update existing rows on conflict, use the PostgreSQL INSERT ON CONFLICT syntax. The destination table must have a primary key.

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.text.SimpleDateFormat;

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");
            java.util.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 affectedRows = stmt.executeUpdate();
        System.out.println("affected rows => " + affectedRows);
    }
}

The following example uses Prepared Statement mode for write operations, which improves throughput for repeated inserts:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

private void jdbcPreparedStmtExample() throws SQLException {
    String user = System.getenv("ALIBABA_CLOUD_USER");
    String password = System.getenv("ALIBABA_CLOUD_PASSWORD");
    String url = String.format(
        "jdbc:postgresql://<ENDPOINT>:<PORT>/<DBNAME>?currentSchema=<SCHEMA_NAME>&user=%s&password=%s",
        user, password
    );
    try (Connection conn = DriverManager.getConnection(url)) {
        String sql = "insert into test values" +
                "(?, ?), (?, ?), (?, ?), (?, ?), (?, ?), " +
                "(?, ?), (?, ?), (?, ?), (?, ?), (?, ?)";
        try (PreparedStatement st = conn.prepareStatement(sql)) {
            for (int i = 0; i < 10; ++i) {
                for (int j = 0; j < 2 * 10; ++j) {
                    st.setString(j + 1, UUID.randomUUID().toString());
                }
                System.out.println("affected row => " + st.executeUpdate());
            }
        }
    }
}

Query data

Use standard SQL SELECT statements to query data from Hologres tables. The basic SELECT example in Step 4 demonstrates this pattern.

For parameterized queries, use Prepared Statement mode and pass parameters with the setter that matches the column type:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

private static void queryWithPreparedStatement(Connection conn) throws Exception {
    String sql = "select pk, f1, f2, f3 from test_tb where pk = ?";
    try (PreparedStatement stmt = conn.prepareStatement(sql)) {
        // pk is an INT column, so use setInt. Passing a String with setString
        // fails under the extended protocol with:
        // ERROR: operator does not exist: integer = character varying
        stmt.setInt(1, 1000);
        try (ResultSet rs = stmt.executeQuery()) {
            while (rs.next()) {
                System.out.println(rs.getInt("pk") + ", " + rs.getString("f1"));
            }
        }
    }
}