Hologres:Use JDBC to connect to Hologres

Usage notes

• To write data to Hologres, you can use only PostgreSQL JDBC Driver 42.2.25 or later.
• We recommend that you use a virtual private cloud (VPC) to test the data write performance after you connect to Hologres. The Internet cannot be used to test the data write performance.
• Hologres does not support multiple data writes in a transaction. If you need to write data to Hologres multiple times in a transaction, you must set autoCommit to true. If you use PostgreSQL JDBC Driver, the auto-commit mode is enabled by default. If the ERROR:INSERT in transaction is not supported now error occurs, you must run the following code to set autoCommit to true.

  Connection conn = DriverManager.getConnection(url, user, password); 
  conn.setAutoCommit(true);

Use JDBC to connect to Hologres

To use JDBC to connect to Hologres, perform the following steps:

1. Download the JDBC driver.
   If the client tool that you use integrates PostgreSQL JDBC Driver by default, you can use its built-in driver. If the client tool that you use does not integrate PostgreSQL JDBC Driver, you must download and install the driver.

   To download PostgreSQL JDBC Driver, visit the Maven repository. You must download PostgreSQL JDBC Driver of 42.2.25 or later. We recommend that you download the latest version. Then, you must add the driver as a dependency to your Maven repository. Sample code:

   <dependencies>
           <dependency>
               <groupId>org.postgresql</groupId>
               <artifactId>postgresql</artifactId>
               <version>42.2.25</version>                            
           </dependency>
   </dependencies>

2. Connect to Hologres.
   • Use the following connection string to connect to a Hologres instance:

     jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}

   • The following table describes the parameters in the connection string.

     Parameter	Description
     Endpoint	The endpoint of the Hologres instance. You can obtain the endpoint of the Hologres instance on the instance details page in the Hologres console.
     Port	The port number of the Hologres instance. You can obtain the port number of the Hologres instance on the instance details page in the Hologres console.
     DBNAME	The name of the Hologres database.
     AccessKey ID	The AccessKey ID of the Alibaba Cloud account used to access the Hologres instance. You can obtain the AccessKey ID from the Security Management page.
     AccessKey Secret	The AccessKey secret of the Alibaba Cloud account used to access the Hologres instance. You can obtain the AccessKey secret from the Security Management page.

   • Take note of the following recommendations when you connect to a Hologres instance:
     • We recommend that you configure the optional parameter ApplicationName in the JDBC URL. If you set the ApplicationName parameter in the requests for connecting to your Hologres database, you can identify the application that sent requests in the Application Name column on the Historical Slow Query page. Format of the connection string:

       jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}

     • We recommend that you configure reWriteBatchedInserts=true in the JDBC URL to allow multiple data entries to be written at a time. This improves the data write performance. Format of the connection string:

       jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true

     • We recommend that you read and write data by executing prepared statements to improve the throughput rate.
     • After the feature of automatically creating foreign tables for MaxCompute is enabled for Hologres, the name of the Hologres schema automatically maps to the name of the MaxCompute project. If you want to query data from the foreign table in the schema, we recommend that you configure the currentSchema parameter in the JDBC URL. This parameter helps locate the MaxCompute project that corresponds to the schema. The following example shows a complete JDBC connection string:

       jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?currentSchema={SCHEMA_NAME}&user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}

   • The following sample code provides an example on how to connect to Hologres:

     public class HologresTest {
     
         private void jdbcExample() throws SQLException {
             String url = "jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?currentSchema={SCHEMA_NAME}&user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}";
             try (Connection conn = DriverManager.getConnection(url)) {
                 try (Statement st = conn.createStatement()) {
                     String sql = "SELECT * FROM table where xxx limit 100";
                     try (ResultSet rs = st.executeQuery(sql)) {
                         while (rs.next()) {
                             // Query the values of the first column in the data table.
                             String c1 = rs.getString(1);
                         }
                     }
                 }
             }
         }
     
         private void jdbcPreparedStmtExample() throws SQLException {
             String url = "jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?currentSchema={SCHEMA_NAME}&user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}";
             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());
                     }
                 }
             }
         }
      }

Use JDBC to develop data

After you connect to Hologres, you can use standard SQL statements to develop data. For example, you can write data to or read data from Hologres.

• Write data

  You can write data to Hologres by using JDBC statements or prepared statements. In most cases, we recommend that you use prepared statements and set the number of data entries to be written at a time to 256 or a multiple of 256. This is because the client caches the SQL compilation results when prepared statements are executed. This reduces the latency of data writes and improves the throughput.

  The following sample statements provide examples on how to write data by executing prepared statements:

  • Execute the following sample statements to write data in batches:

    /* Write data in batches by executing prepared statements. */
    /* In this example, the number of data entries to be written at a time is set to 256. */
    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");
                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();
        }
    }

  • You can also add the INSERT ON CONFLICT statement to write and update the data when you execute prepared statements. The following sample statements provide an example.
    Note If you add the INSERT ON CONFLICT statement, a primary key must be defined for the destination table.

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

• Query data

  After data is written, you can query the written data. You can also query the data in an existing table based on your business requirements.

Use the Druid connection pool

• Usage notes
  • We recommend that you configure keepAlive=true to reuse connections and prevent short-lived connections.
  • Druid 1.1.12 or later is required to connect to Hologres.
• Configure the Druid connection pool
  Note Set the initialSize, minIdle, and maxActive parameters based on the size of your Hologres instance and your business requirements.

  <bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
    <!-- jdbc_url: the endpoint of the Hologres instance to which you want to connect. You can obtain the endpoint on the instance details page in the Hologres console. -->
    <property name="url" value="${jdbc_url}" />
    <!-- jdbc_user: the AccessKey ID of the account used to connect to the Hologres instance.  -->
    <property name="username" value="${jdbc_user}" />
    <!-- jdbc_password: the AccessKey secret of the account used to connect to the Hologres instance.  -->
    <property name="password" value="${jdbc_password}" />
    <!-- Set the initial size of the connection pool, and the minimum and maximum numbers of connections.  -->
    <property name="initialSize" value="5" />
    <property name="minIdle" value="10" />
    <property name="maxActive" value="20" />
    <!-- Set the timeout period for obtaining a connection from the connection pool.  -->
    <property name="maxWait" value="60000" />
    <!-- Set the intervals at which the system detects idle connections to be closed. Unit: milliseconds.  -->
    <property name="timeBetweenEvictionRunsMillis" value="2000" />
    <!-- Set the minimum validity period of a connection in the connection pool. Unit: milliseconds.  -->
    <property name="minEvictableIdleTimeMillis" value="600000" />
    <property name="maxEvictableIdleTimeMillis" value="900000" />
    <property name="validationQuery" value="select 1" />
    <property name="testWhileIdle" value="true" />
    <!-- Specify whether to check the validity of the connection each time you obtain a connection from the connection pool. A value of true specifies that the system checks the validity of the connection, and a value of false specifies that the system does not check the validity of the connection.  -->
    <property name="testOnBorrow" value="false" />
    <!-- Specify whether to check the validity of the connection each time you return a connection to the connection pool. A value of true specifies that the system checks the validity of the connection, and a value of false specifies that the system does not check the validity of the connection.  -->
    <property name="testOnReturn" value="false" />
    <property name="keepAlive" value="true" />
    <property name="phyMaxUseCount" value="100000" />
    <!-- Set the filters to be used for monitoring statistics.  -->
    <property name="filters" value="stat" />
  </bean>

Best practices for performance optimization

Configure GUC parameters for JDBC

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) {
        // Specify the endpoint of the Hologres instance.
        String hostname = "hgpostcn-cn-xxxx-cn-hangzhou.hologres.aliyuncs.com";
        // Specify the port number of the Hologres instance.
        String port = "80";
        // Specify the name of the database to which you want to connect.
        String dbname = "demo";
        String jdbcUrl = "jdbc:postgresql://" + hostname + ":" + port + "/" + dbname;
        Properties properties = new Properties();
        //Specify the username that is used to connect to the specified database.
        properties.setProperty("user", "xxxxx");
        // Specify the password that is used to connect to the specified database.
        properties.setProperty("password", "xxxx");
        // Configure the 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 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();
        }
    }
}