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.

Procedure

  1. Download the JDBC driver.
    • If client tools integrate PostgreSQL Database Driver, you can use their built-in driver. If PostgreSQL Database Driver is not integrated, you must download PostgreSQL JDBC Driver from the Maven repository. We recommend that you download PostgreSQL JDBC Driver V42.2.18 or later. Sample code:
      <dependencies>
              <dependency>
                  <groupId>org.postgresql</groupId>
                  <artifactId>postgresql</artifactId>
                  <version>42.2.18.jre6</version>                            
              </dependency>
          </dependencies>
    • You can also use a driver that is specially developed for Hologres. Sample code:
      <dependency>
        <groupId>com.alibaba.hologres</groupId>
        <artifactId>postgresql-holo</artifactId>
        <version>42.2.18.4</version>
      </dependency>
      In addition to the features of PostgreSQL JDBC Driver, this driver provides the following features:
      • You can write data to a child partitioned table by using a parent partitioned table.
      • When a string in the format of yyyyMMddHHmmss is written to a column of the TIMESTAMP type, the string is automatically converted into a timestamp.
      • The parameters described in the following table are added.
        Parameter Description Default value
        dynamicPartition Specifies whether to automatically create a child partitioned table if the child partitioned table to which you want to write data does not exist. false
        inputNumberAsEpochMsForDatetimeColumn Specifies whether to treat data of the LONG type that is written to a column of the TIMESTAMP type as the number of milliseconds that have elapsed since January 1, 1970, 00:00:00 UTC. false
        inputStringAsEpochMsForDatetimeColumn Specifies whether to treat data of the STRING type that is written to a column of the TIMESTAMP type as the number of milliseconds that have elapsed since January 1, 1970, 00:00:00 UTC. false
  2. Use JDBC to connect to Hologres.
    • Recommendations
      We recommend that you add the optional ApplicationName parameter to 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.
      url = "jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}";
      The following figure shows how to identify the application that sent requests on the Historical Slow Query page. Historical Slow Query
    • Example
      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());
                      }
                  }
              }
          }
       }
  3. Develop data.
    After you connect to Hologres by using JDBC, you can use standard SQL statements to develop data. The following list provides some typical data development scenarios:
  4. Configure the Druid connection pool.
    You must use Druid 1.1.12 or later to connect to Hologres. When you connect to Hologres, we recommend that you set the keepAlive parameter to true to reuse connections and prevent short-lived connections. The following sample code provides an example on how to 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 configurations 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 to check the validity of the connection, and a value of false specifies not to 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 to check the validity of the connection, and a value of false specifies not to 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 use for monitoring statistics.  -->
      <property name="filters" value="stat" /> 
    </bean>