如何通过JDBC连接Hologres进行数据开发_实时数仓 Hologres-阿里云帮助中心

注意事项

通过JDBC写入数据至Hologres需要使用42.2.25及以上的Postgres JDBC Driver。
使用JDBC连接Hologres后，如果您需要测试写入数据的性能，建议使用VPC网络。公共网络由于自身原因，无法达到性能测试的目标。
Hologres当前不支持在一个事务中多次写入，因此需要设置autoCommit为true（JDBC的autoCommit默认为true），不要显式的在代码里面进行Commit操作。如果出现ERROR: INSERT in transaction is not supported now 报错，则需要设置autoCommit为true，如下所示。
```
Connection conn = DriverManager.getConnection(url, user, password); 
conn.setAutoCommit(true);
```

通过JDBC连接Hologres

通过JDBC连接Hologres的步骤如下。

下载配置。
客户端工具默认集成了PostgreSQL数据库的驱动，直接使用工具自带的驱动即可。如果未集成驱动，需要下载并安装。
使用PostgreSQL驱动，请至官网下载PostgreSQL JDBC Driver，需要使用42.2.25以上版本的JDBC驱动，建议您使用最新版本的JDBC。下载成功后需要至Maven仓库配置示例如下。
```
<dependencies>
        <dependency>
            <groupId>org.postgresql</groupId>
            <artifactId>postgresql</artifactId>
            <version>42.2.25</version>                            
        </dependency>
</dependencies>
```

连接Hologres。

使用以下连接串连接Hologres。

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

参数说明如下。


参数	描述
Endpoint	Hologres实例的网络地址。进入Hologres管理控制台的实例详情页获取网络地址。
Port	Hologres实例的端口。进入Hologres管理控制台的实例详情页获取端口。
DBNAME	Hologres创建的数据库名称。
AccessKey ID	当前阿里云账号的AccessKey ID。您可以单击AccessKey 管理，获取AccessKey ID。
AccessKey Secret	当前阿里云账号的AccessKey Secret。您可以单击AccessKey 管理，获取AccessKey Secret。

连接Hologres建议如下。
- 建议JDBC的URL加上ApplicationName参数，此参数为可选参数。使用该方式连接数据库时有助于您在慢Query清单中根据ApplicationName快速定位您的发起请求的应用，连接串如下。
```
jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?user={ACCESS_ID}&password={ACCESS_KEY}&ApplicationName={APPLICATION_NAME}
```
- 建议在JDBC URL中添加reWriteBatchedInserts=true配置，系统会以批量写入的方式提交作业，性能更好，连接串如下。
```
jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
```
- 建议使用Prepared Statement方式来进行数据读取和写入，以实现更高的吞吐。

连接示例如下。

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()) {
                        //获取数据表中第一列数据值
                        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());
                }
            }
        }
    }
 }

使用JDBC开发

JDBC连接Hologres成功后，您可以使用标准的开发语句来开发Hologres，包括写入和读取数据。

写入数据

您可以通过JDBC的Statement或Prepared Statement模式写入数据。通常情况下，推荐您使用Prepared Statment模式，并且设置批量写入数据的条数为256的倍数（建议最低设置为256条）。因为使用Prepared Statment模式时，服务端会缓存SQL编译的结果，从而降低写入数据的延时，并提高吞吐量。

使用Prepared Statement模式写入数据的示例如下。

使用Prepared Statement模式批量写入数据，命令语句如下。

/*通过Prepared Statement模式批量写入数据*/
/*该示例中，设置的批量写入大小batchSize为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();
    }
}

使用Prepared Statement模式写入数据的同时，添加Postgres的INSERT ON CONFLICT功能，实现写入时更新覆盖原有数据。命令语句如下。

说明使用INSERT ON CONFLICT语句时目标表必须定义主键。

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

数据查询
数据写入完成之后，可以对数据进行查询。您也可以根据业务需求查询已有表的数据。

Druid连接池配置

注意事项
- 建议配置keepAlive=true，可以复用连接和避免短链接。
- 您需使用Druid 1.1.12以上的版本连接Hologres。

配置Druid连接池

说明其中initialSize、minIdle和maxActive请根据实例大小和实际业务进行设置。

<bean id="dataSource" class="com.alibaba.druid.pool.DruidDataSource" init-method="init" destroy-method="close">
  <!-- jdbc_url是Hologres实例的连接地址URL，可以在控制台的实例配置页面获取连接URL。-->
  <property name="url" value="${jdbc_url}" />
  <!-- jdbc_user是Hologres实例中的用户账户的Access ID。 -->
  <property name="username" value="${jdbc_user}" />
  <!-- jdbc_password是Hologres实例中用户账号对应的Access Secret。 -->
  <property name="password" value="${jdbc_password}" />
  <!-- 配置初始化连接池大小、最小连接数、最大连接数。 -->
  <property name="initialSize" value="5" />
  <property name="minIdle" value="10" />
  <property name="maxActive" value="20" />
  <!-- 配置获取连接等待超时的时间。 -->
  <property name="maxWait" value="60000" />
  <!-- 配置间隔多久进行一次检测，检测需要关闭的空闲连接，单位毫秒。 -->
  <property name="timeBetweenEvictionRunsMillis" value="2000" />
  <!-- 配置一个连接在连接池中的最小生存时间，单位毫秒。 -->
  <property name="minEvictableIdleTimeMillis" value="600000" />
  <property name="maxEvictableIdleTimeMillis" value="900000" />
  <property name="validationQuery" value="select 1" />
  <property name="testWhileIdle" value="true" />
  <!-- 配置从连接池获取连接时，是否检查连接有效性，true每次都检查；false不检查。 -->
  <property name="testOnBorrow" value="false" />
  <!-- 配置向连接池归还连接时，是否检查连接有效性，true每次都检查；false不检查。 -->
  <property name="testOnReturn" value="false" />
  <property name="keepAlive" value="true" />
  <property name="phyMaxUseCount" value="100000" />
  <!-- 配置监控统计拦截的filters。 -->
  <property name="filters" value="stat" />
</bean>

性能调优实践

使用JDBC时想要达到比较好的性能，有以下需要注意的事项。

尽量使用VPC网络，避免使用公网，已避免公网带来的网络开销。
通过JDBC驱动写入数据时，JDBC URL中添加reWriteBatchedInserts=true配置，系统会以批量写入的方式提交作业，性能更好。实践证明攒批配置256的倍数（建议最低设置为256条）效果会更好。同时您也可以使用Hologres的Holo Client会自动攒批。
```
jdbc:postgresql://{ENDPOINT}:{PORT}/{DBNAME}?ApplicationName={APPLICATION_NAME}&reWriteBatchedInserts=true
```
使用Prepared Statment模式，此模式下服务端会缓存SQL编译的结果，从而降低写入数据的延时，并提高吞吐量。