Hologres:JDBC

Étape 1 : Ajouter la dépendance du pilote

La plupart des outils clients SQL intègrent un pilote PostgreSQL natif ; utilisez-le si disponible. Pour les applications Java, ajoutez le pilote JDBC PostgreSQL à votre projet Maven. Téléchargez-le depuis jdbc.postgresql.org/download et utilisez la version 42.3.2 ou ultérieure (la dernière version stable est recommandée).

Hologres utilise le pilote JDBC PostgreSQL standard. Aucun pilote spécifique à Hologres n'est nécessaire.

Ajoutez la dépendance suivante à votre fichier 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>

Étape 2 : Construire la chaîne de connexion

Le format de la chaîne de connexion est le suivant :

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

Paramètres obligatoires

Paramètres facultatifs

Ajoutez-les à la chaîne de connexion selon vos besoins. Séparez plusieurs paramètres par &.

À propos de preferQueryMode

Avec le protocole étendu, les paramètres PreparedStatement sont envoyés avec des types explicites et le pilote n'effectue aucune conversion de type implicite. Ainsi, la comparaison d'une colonne entière avec un paramètre varchar peut échouer avec une erreur telle que ERROR: operator does not exist: integer = character varying. Si vous rencontrez cette erreur de non-concordance de type, ajoutez preferQueryMode=simple à la chaîne de connexion. Le pilote envoie alors le SQL sous forme de texte brut et laisse le serveur déduire les types de paramètres à partir du contexte.

Exemple de chaîne de connexion avec les options recommandées :

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

Si une requête PreparedStatement échoue avec une erreur telle que ERROR: operator does not exist: integer = character varying en raison d'une non-concordance de type entre le paramètre de requête et la colonne, ajoutez preferQueryMode=simple à la chaîne de connexion :

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

Étape 3 : Stocker les identifiants dans des variables d'environnement

Coder en dur les identifiants dans les chaînes de connexion présente un risque de sécurité. Stockez-les plutôt sous forme de variables d'environnement. Sous Linux, ajoutez les lignes suivantes à votre fichier ~/.bash_profile :

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

Étape 4 : Se connecter et exécuter une requête

L'exemple suivant lit les identifiants depuis les variables d'environnement, définit les propriétés de délai d'attente, se connecte à Hologres et exécute une requête SELECT basique à l'aide d'une instruction Statement standard :

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

Écriture des données

Vous pouvez écrire des données en utilisant le mode Statement ou Prepared Statement de JDBC. Privilégiez le mode Prepared Statement pour les opérations d'écriture. Dans ce mode, le serveur met en cache les résultats de compilation SQL, ce qui réduit la latence d'écriture et augmente le débit. Définissez la taille du lot sur un multiple de 256 — la taille de lot minimale recommandée est de 256.

Les exemples de cette section utilisent la table suivante. Créez-la d'abord dans votre base de données.

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

Insertion par lots

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

Pour mettre à jour les lignes existantes en cas de conflit, utilisez la syntaxe PostgreSQL INSERT ON CONFLICT . La table cible doit posséder une clé primaire.

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

L'exemple suivant utilise le mode Prepared Statement pour les opérations d'écriture, ce qui améliore le débit lors d'insertions répétées :

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

Interrogation des données

Utilisez des instructions SQL SELECT standard pour interroger les données des tables Hologres. L'exemple SELECT basique présenté à l'Étape 4 illustre ce modèle.

Pour les requêtes paramétrées, utilisez le mode Prepared Statement et passez les paramètres avec la méthode setter correspondant au type de colonne :

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