Connect to Hologres and develop data using JDBC - Hologres

Hologres provides connection interfaces that are fully compatible with PostgreSQL, such as Java Database Connectivity (JDBC). You can use these interfaces to connect SQL client tools to Hologres for data development. This topic describes how to use JDBC to connect to Hologres and develop data.

Precautions

Use PostgreSQL JDBC Driver 42.3.2 or later to write data to Hologres through a JDBC connection.
For performance tests of data writes, use a VPC network. The public network cannot meet performance testing goals.
Hologres does not support multiple writes in a single transaction. Therefore, set autoCommit to true. The default value of autoCommit for JDBC is true. Do not explicitly call the commit operation in your code. If the ERROR: INSERT in transaction is not supported now error occurs, set autoCommit to true, as shown below.
```
Connection conn = DriverManager.getConnection(url, user, password); 
conn.setAutoCommit(true);
```

Connect to Hologres using JDBC

Follow these steps to connect to Hologres using JDBC.

Download the configuration.
Most client tools include a built-in PostgreSQL driver. Use the built-in driver if it is available. If not, download and install a driver.
To use the PostgreSQL driver, go to the official website to download the PostgreSQL JDBC Driver. Use version 42.3.2 or later. We recommend that you use the latest version of the JDBC driver. After the download, add the following dependency to your Maven repository.
```
<dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.3.2</version>                            
        </dependency>
</dependencies>
```

Connect to Hologres.

Use the following connection string to connect to Hologres.

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

The following table describes the parameters.

Parameter	Description
ENDPOINT	The network endpoint and port of the Hologres instance. Go to the Hologres console. In the navigation pane on the left, click Instances. Click the target instance. On the Instance Details page, find the endpoint and port in the Network Information section. Important Select the endpoint and port that correspond to the network environment where your code runs. Otherwise, the connection will fail.
PORT
DBNAME	The name of the database created in Hologres.
ACCESS_ID	The username for the current account. We recommend that you use environment variables to specify your credentials to reduce the risk of leaks. For more information, see the example later in this topic.
ACCESS_KEY	The logon password for the current account. We recommend that you use environment variables to specify your credentials to reduce the risk of leaks. For more information, see the example later in this topic.

Note the following recommendations when connecting to Hologres.
- Add the ApplicationName parameter to the JDBC URL. This parameter is optional. It helps you quickly locate the application that sends requests based on the ApplicationName in the slow query checklist. The following code shows the connection string.
```
jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}
```
- Add the reWriteBatchedInserts=true configuration to the JDBC URL. This allows the system to submit jobs in batches for better performance. The following code shows the connection string.
```
jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
```
- Use Prepared Statement to read and write data for higher throughput.
- After you enable automatic loading of foreign tables in Hologres, MaxCompute project names are automatically mapped to schemas with the same names in Hologres. If you want to directly query foreign tables in such a schema, add the currentSchema parameter to the JDBC URL to map to the corresponding MaxCompute project. The following code shows an example of the connection string.
```
jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?currentSchema={SCHEMA_NAME}&user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}
```
- Use environment variables to specify the username and password to reduce the risk of credential leaks. For example, on a Linux system, you can add the following commands to the bash_profile file to configure environment variables.
```
export ALIBABA_CLOUD_USER=<ACCESS_ID>
export ALIBABA_CLOUD_PASSWORD=<ACCESS_KEY>
```

The following code provides connection examples.

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);
        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()) {
                        // Get the value from the first column of the data table.
                        String c1 = rs.getString(1);
                    }
                }
            }
        }
    }

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

Develop using JDBC

After you connect to Hologres using JDBC, you can use standard statements to develop in Hologres, including writing and reading data.

Write data

You can write data using the Statement or Prepared Statement mode in JDBC. We recommend that you use the Prepared Statement mode and set the batch size to a multiple of 256. The minimum recommended batch size is 256. In Prepared Statement mode, the server-side caches the SQL compilation results. This reduces write latency and increases throughput.

The following examples show how to write data in Prepared Statement mode.

Use the Prepared Statement mode to write data in batches. The following code provides an example.

/*Write data in batches in Prepared Statement mode.*/
/*In this example, the batch size is 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();
    }
}

When you write data in Prepared Statement mode, you can also use the PostgreSQL INSERT ON CONFLICT feature to update or overwrite existing data. The following code provides an example.

Note

The destination table must have a primary key when you use the INSERT ON CONFLICT statement.

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 the data is written, you can query it. You can also query data from existing tables as needed.

Druid connection pool configuration

Precautions
- Set keepAlive=true to reuse connections and avoid short-lived connections.
- Use Druid 1.1.12 or later to connect to Hologres.
- Druid versions 1.2.12 to 1.2.21 have an issue where the connectTimeout and socketTimeout parameters default to 10 seconds if they are not specified. If you encounter a similar issue, upgrade your Druid version.

Configure a Druid connection pool

Note

Set initialSize, minIdle, and maxActive based on your instance size and business requirements.

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
  <!-- The jdbc_url is the endpoint URL of the Hologres instance. You can obtain the URL from the instance configuration page in the console. -->
  <property name="url" value="${jdbc_url}" />
  <!-- The jdbc_user is the AccessKey ID of the user account in the Hologres instance. -->
  <property name="username" value="${jdbc_user}" />
  <!-- The jdbc_password is the AccessKey secret that corresponds to the user account in the Hologres instance. -->
  <property name="password" value="${jdbc_password}" />
  <!-- Configure the initial size, minimum number of connections, and maximum number of connections for the connection pool. -->
  <property name="initialSize" value="5" />
  <property name="minIdle" value="10" />
  <property name="maxActive" value="20" />
  <!-- Configure the timeout period for waiting for a connection. -->
  <property name="maxWait" value="60000" />
  <!-- Configure the interval for detecting and closing idle connections. Unit: milliseconds. -->
  <property name="timeBetweenEvictionRunsMillis" value="2000" />
  <!-- Configure the minimum survival time for a connection in the pool. Unit: milliseconds. -->
  <property name="minEvictableIdleTimeMillis" value="600000" />
  <property name="maxEvictableIdleTimeMillis" value="900000" />
  <property name="validationQuery" value="select 1" />
  <property name="testWhileIdle" value="true" />
  <!-- Configure whether to check the validity of a connection when it is retrieved from the pool. true: check each time. false: do not check. -->
  <property name="testOnBorrow" value="false" />
  <!-- Configure whether to check the validity of a connection when it is returned to the pool. true: check each time. false: do not check. -->
  <property name="testOnReturn" value="false" />
  <property name="keepAlive" value="true" />
  <property name="phyMaxUseCount" value="100000" />
  <!-- Configure filters for monitoring and statistics interception. -->
  <property name="filters" value="stat" />
</bean>

Performance tuning best practices

Note the following items to achieve better performance when you use JDBC.

Use a VPC network instead of the public network to avoid network overhead.
When you write data using the JDBC driver, add the reWriteBatchedInserts=true configuration to the JDBC URL. This allows the system to submit jobs in batches for better performance. Tests show that setting the batch size to a multiple of 256 provides better results. The minimum recommended batch size is 256. You can also use the Hologres Holo Client, which automatically handles batching.
```
jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
```
Use the Prepared Statement mode. In this mode, the server-side caches the SQL compilation results. This reduces write latency and increases throughput.

Configure GUC parameters using JDBC

You may need to set Grand Unified Configuration (GUC) parameters at the session level. For more information about GUC parameters, see GUC parameters. We recommend that you use the following method to set GUC parameters. The example shows how to set the session-level statement_timeout parameter to 12345 milliseconds and the session-level idle_in_transaction_session_timeout parameter to 12345 milliseconds.

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

JDBC-based load balancing

Starting from V1.3, Hologres lets you configure multiple read-only secondary instances in JDBC to support simple load balancing. For more information, see JDBC-based load balancing.