Hologres provides the Java Database Connectivity (JDBC) and Open Database Connectivity (ODBC) interfaces that are fully compatible with PostgreSQL. You can connect an SQL client to Hologres by using these interfaces. This topic describes how to use JDBC to connect to Hologres for data development.

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. Therefore, if you need to write data to Hologres multiple times in a transaction, you must enable the auto-commit mode. 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 enable the auto-commit mode:
    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 a Hologres instance.
    • 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 to which you want to connect.
      AccessKey ID The AccessKey ID of your Alibaba Cloud account.

      You can obtain the AccessKey ID from the Security Management page.

      AccessKey Secret The AccessKey secret of your Alibaba Cloud account.

      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 set the optional ApplicationName parameter in the URL of JDBC. 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 URL of JDBC 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.
    • 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}?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}?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

To achieve desired performance when you use JDBC, take note of the following items:
  • We recommend that you use a VPC instead of the Internet to prevent the network overhead of the Internet.
  • We recommend that you configure reWriteBatchedInserts=true in the URL of JDBC to allow multiple data entries to be written at a time. This improves the data write performance. We recommend that you set the number of data entries to be written at a time to 256 or a multiple of 256 to achieve better performance. You can also use Holo Client, which automatically collects data in batches.
    jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
  • The client caches the SQL compilation results when prepared statements are executed. This reduces the latency of data writes and improves the throughput.