Hologres:JDBC

Etapa 1: Adicionar a dependência do driver

A maioria das ferramentas cliente SQL inclui um driver PostgreSQL integrado — utilize-o se estiver disponível. Para aplicações Java, adicione o PostgreSQL JDBC Driver ao seu projeto Maven. Baixe-o em jdbc.postgresql.org/download e use a versão 42.3.2 ou superior (recomenda-se a versão estável mais recente).

O Hologres usa o PostgreSQL JDBC Driver padrão — não há um driver específico separado do Hologres para instalar.

Adicione a seguinte dependência ao seu 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>

Etapa 2: Crie a string de conexão

O formato da string de conexão é:

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

Parâmetros obrigatórios

Parâmetros opcionais

Anexe estes parâmetros à string de conexão conforme necessário. Separe múltiplos parâmetros com &.

Sobre preferQueryMode

No protocolo estendido, os parâmetros de PreparedStatement são enviados com tipos explícitos e o driver não realiza conversão implícita de tipos. Portanto, comparar uma coluna inteira com um parâmetro varchar pode falhar com um erro como ERROR: operator does not exist: integer = character varying. Se você encontrar esse erro de incompatibilidade de tipos, adicione preferQueryMode=simple à string de conexão. O driver então envia o SQL como texto simples e permite que o servidor infira os tipos de parâmetros pelo contexto.

Exemplo de string de conexão com opções recomendadas:

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

Se uma consulta PreparedStatement falhar com um erro como ERROR: operator does not exist: integer = character varying devido a uma incompatibilidade de tipos entre o parâmetro da consulta e a coluna, adicione preferQueryMode=simple à string de conexão:

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

Etapa 3: Armazenar credenciais em variáveis de ambiente

Codificar credenciais diretamente nas strings de conexão cria um risco de segurança. Armazene-as como variáveis de ambiente. No Linux, adicione o seguinte ao seu ~/.bash_profile:

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

Etapa 4: Conectar e execute uma consulta

O exemplo abaixo lê credenciais de variáveis de ambiente, defina propriedades de timeout, conecta-se ao Hologres e executa uma consulta SELECT básica usando um Statement padrão:

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);
                    }
                }
            }
        }
    }
}

Escrever dados

É possível escrever dados usando o modo Statement ou Prepared Statement no JDBC. Prefira o modo Prepared Statement para operações de escrita. Nesse modo, o servidor armazena em cache os resultados da compilação SQL, o que reduz a latência de escrita e aumenta o throughput. Defina o tamanho do lote como um múltiplo de 256 — o tamanho mínimo recomendado é 256.

Os exemplos nesta seção usam a tabela a seguir. Crie-a no seu banco de dados primeiro.

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

Inserção em lote

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 com INSERT ON CONFLICT

Para atualize linhas existentes em caso de conflito, use a sintaxe INSERT ON CONFLICT do PostgreSQL. A tabela de destino deve ter uma chave primária.

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);
    }
}

O exemplo a seguir utiliza o modo Prepared Statement para operações de escrita, melhorando o throughput para inserções repetidas:

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());
            }
        }
    }
}

Consultar dados

Use instruções SQL SELECT padrão para consultar dados das tabelas do Hologres. O exemplo básico de SELECT na Etapa 4 demonstra esse padrão.

Para consultas parametrizadas, utilize o modo Prepared Statement e passe os parâmetros com o setter correspondente ao tipo da coluna:

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"));
            }
        }
    }
}

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource"
      init-method="init" destroy-method="close">
  <!-- Endpoint URL from the instance configuration page in the console -->
  <property name="url" value="${jdbc_url}" />
  <!-- AccessKey ID of the user account -->
  <property name="username" value="${jdbc_user}" />
  <!-- AccessKey secret of the user account -->
  <property name="password" value="${jdbc_password}" />

  <!-- Pool sizing: adjust based on instance size and workload -->
  <property name="initialSize" value="5" />
  <property name="minIdle" value="10" />
  <property name="maxActive" value="20" />

  <!-- Wait up to 60 seconds for a connection from the pool -->
  <property name="maxWait" value="60000" />
  <!-- Check for idle connections every 2 seconds -->
  <property name="timeBetweenEvictionRunsMillis" value="2000" />
  <!-- Evict connections idle for more than 10 minutes -->
  <property name="minEvictableIdleTimeMillis" value="600000" />
  <property name="maxEvictableIdleTimeMillis" value="900000" />

  <property name="validationQuery" value="select 1" />
  <property name="testWhileIdle" value="true" />
  <!-- Do not test connections on borrow/return (reduces overhead) -->
  <property name="testOnBorrow" value="false" />
  <property name="testOnReturn" value="false" />

  <property name="keepAlive" value="true" />
  <property name="phyMaxUseCount" value="100000" />
  <property name="filters" value="stat" />
</bean>

import org.postgresql.PGProperty;
import java.sql.*;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;

public class GucDemo {
    public static void main(String[] args) {
        String hostname = "hgpostcn-cn-xxxx-cn-hangzhou.hologres.aliyuncs.com";
        String port = "80";
        String dbname = "demo";
        String jdbcUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname;

        Properties properties = new Properties();
        properties.setProperty("user", "xxxxx");
        properties.setProperty("password", "xxxx");

        // Set session-level GUC parameters
        PGProperty.OPTIONS.set(properties,
            "--statement_timeout=12345 --idle_in_transaction_session_timeout=12345");

        try {
            Class.forName("org.postgresql.Driver");
            Connection connection = DriverManager.getConnection(jdbcUrl, properties);

            PreparedStatement preparedStatement =
                connection.prepareStatement("show statement_timeout");
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                ResultSetMetaData rsmd = resultSet.getMetaData();
                int columnCount = rsmd.getColumnCount();
                Map<String, Object> map = new HashMap<>();
                for (int i = 0; i < columnCount; i++) {
                    map.put(rsmd.getColumnName(i + 1).toLowerCase(), resultSet.getObject(i + 1));
                }
                System.out.println(map);
            }
        } catch (Exception exception) {
            exception.printStackTrace();
        }
    }
}

jdbc:postgresql://<ENDPOINT>:<PORT>/<DBNAME>?ApplicationName=<APPLICATION_NAME>&reWriteBatchedInserts=true