Hologres:JDBC

Paso 1: Agregar la dependencia del controlador

La mayoría de las herramientas cliente SQL incluyen un controlador PostgreSQL integrado; utilícelo si está disponible. Para aplicaciones Java, agregue PostgreSQL JDBC Driver a su proyecto Maven. Descárguelo desde jdbc.postgresql.org/download y use la versión 42.3.2 o posterior (se recomienda la última versión estable).

Hologres utiliza el PostgreSQL JDBC Driver estándar, por lo que no es necesario instalar un controlador específico para Hologres.

Agregue la siguiente dependencia a su archivo 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>

Paso 2: Construir la cadena de conexión

El formato de la cadena de conexión es el siguiente:

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

Parámetros obligatorios

Parámetros opcionales

Agregue estos parámetros a la cadena de conexión según sea necesario. Separe varios parámetros con &.

Acerca de preferQueryMode

Bajo el protocolo extendido, los parámetros de PreparedStatement se envían con tipos explícitos y el controlador no realiza conversiones implícitas de tipo. Por ello, comparar una columna de enteros con un parámetro varchar puede fallar con un error como ERROR: operator does not exist: integer = character varying. Si encuentra este error de incompatibilidad de tipos, agregue preferQueryMode=simple a la cadena de conexión. De este modo, el controlador envía la sentencia SQL como texto plano y permite que el servidor infiera los tipos de parámetros según el contexto.

Ejemplo de cadena de conexión con las opciones recomendadas:

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

Si una consulta con PreparedStatement falla con un error como ERROR: operator does not exist: integer = character varying debido a una incompatibilidad de tipos entre el parámetro de la consulta y la columna, agregue preferQueryMode=simple a la cadena de conexión:

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

Paso 3: Almacenar credenciales en variables de entorno

Incluir credenciales directamente en las cadenas de conexión representa un riesgo de seguridad. En su lugar, guárdelas como variables de entorno. En Linux, agregue lo siguiente a su archivo ~/.bash_profile:

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

Paso 4: Conectarse y ejecutar una consulta

El siguiente ejemplo lee las credenciales desde variables de entorno, establece las propiedades de tiempo de espera, se conecta a Hologres y ejecuta una consulta SELECT básica mediante una Statement estándar:

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

Escritura de datos

Puede escribir datos mediante el modo Statement o Prepared Statement en JDBC. Utilice el modo Prepared Statement para las operaciones de escritura. En este modo, el servidor almacena en caché los resultados de la compilación SQL, lo que reduce la latencia de escritura y aumenta el rendimiento. Establezca el tamaño del lote como un múltiplo de 256; el tamaño mínimo recomendado es 256.

Los ejemplos de esta sección utilizan la siguiente tabla. Créela primero en su base de datos.

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

Inserción por lotes

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

Para actualizar filas existentes en caso de conflicto, utilice la sintaxis INSERT ON CONFLICT de PostgreSQL. La tabla de destino debe tener una clave primaria.

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

El siguiente ejemplo utiliza el modo Prepared Statement para operaciones de escritura, lo que mejora el rendimiento en inserciones 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());
            }
        }
    }
}

Consulta de datos

Utilice sentencias SQL SELECT estándar para consultar datos de las tablas de Hologres. El ejemplo básico de SELECT en el Paso 4 demuestra este patrón.

Para consultas parametrizadas, utilice el modo Prepared Statement y pase los parámetros con el método setter que coincida con el tipo de la columna:

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