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 directly use their built-in driver. If PostgreSQL Database Driver is not integrated, download PostgreSQL JDBC Driver from the Maven repository. We recommend that you download PostgreSQL JDBC Driver V42.2.18 or later. The following sample code is for your reference:
      <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. The following sample code is for your reference:
      <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 to a timestamp.
      • The parameters that are 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 the 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 the 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.
    The following sample code is for your reference:
    public class HologresTest {
    
        private void jdbcExample() throws Exception {
            String url = "jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}";
            Connection conn = DriverManager.getConnection(url);
            Statement st = conn.createStatement();
            String sql = "SELECT * FROM table where xxx limit 100";
            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 Exception {
            String sql = "insert into test values" +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?), " +
                "(?, ?)";
            try (PreparedStatement st = conn.prepareStatement(sql)) {
                for (int i = 0; i < 10; ++i) {
                    try {
                        for (int j = 0; j < 2 * 10; ++j) {
                            st.setString(j + 1, UUID.randomUUID().toString());
                        }
                        System.out.println("affected row => " + st.executeUpdate());
                    } catch (Exception e) {
                        e.printStackTrace();
                    }
                }
            }
        }
    }
    For more information about the configuration of JDBC, visit Java Concept Of The Day.
  3. Analyze data.
    After you connect to Hologres by using JDBC, you can use standard SQL statements to analyze 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. This way, you can 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 by the user in the Hologres instance. -->
      <property name="username" value="${jdbc_user}" />
      <! -- jdbc_password: the AccessKey secret of the account used by the user in 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 indicates that the validity of the connection is checked. A value of false indicates that the validity of the connection is not checked. -->
      <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 indicates that the validity of the connection is checked. A value of false indicates that the validity of the connection is not checked. -->
      <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>