JDBC连接表格存储 - 表格存储

注意事项

目前支持使用SQL查询功能的地域有华东1（杭州）、华东2（上海）、华北2（北京）、华北3（张家口）、华北5（呼和浩特）、华北6（乌兰察布）、华南1（深圳）、西南1（成都）、中国香港、新加坡、印度尼西亚（雅加达）、印度（孟买）、德国（法兰克福）、沙特（利雅得）和美国（弗吉尼亚）。

前提条件

如果要使用RAM用户进行操作，请确保已创建RAM用户，并为RAM用户授予所有SQL操作权限，即在自定义权限策略中配置"Action": "ots:SQL*"。具体操作，请参见通过RAM Policy为RAM用户授权。
已获取AccessKey（包括AccessKey ID和AccessKey Secret）。具体操作，请参见获取AccessKey。
已创建数据表并为数据表创建映射关系。具体操作，请分别参见创建数据表和创建表的映射关系。

步骤一：安装JDBC驱动

您可以通过以下两种方式安装JDBC驱动。

下载表格存储JDBC驱动并导入到项目中。具体下载路径请参见表格存储JDBC驱动。
在Maven项目中加入依赖项
在Maven工程中使用表格存储JDBC驱动，只需在pom.xml中加入相应依赖即可。以5.13.5版本为例，在<dependencies>内加入如下内容：
```
<dependency>
  <groupId>com.aliyun.openservices</groupId>
  <artifactId>tablestore-jdbc</artifactId>
  <version>5.13.5</version>
</dependency>                
```

步骤二：使用JDBC直连

使用Class.forName()加载表格存储JDBC驱动。
表格存储JDBC驱动名称为com.alicloud.openservices.tablestore.jdbc.OTSDriver。
```
Class.forName("com.alicloud.openservices.tablestore.jdbc.OTSDriver");
```

使用JDBC连接表格存储实例。

String url = "jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance";
String user = "************************";
String password = "********************************";
Connection conn = DriverManager.getConnection(url, user, password);

参数说明请参见下表。

参数	示例	说明
url	jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance	表格存储JDBC的URL。格式为`jdbc:ots:schema://[accessKeyId:accessKeySecret@]endpoint/instanceName[?param1=value1&...&paramN=valueN]`。主要字段说明如下： schema（必选）：表格存储JDBC驱动使用的协议，一般设置为https。 accessKeyId:accessKeySecret（可选）：阿里云账号或者RAM用户的AccessKey ID和AccessKey Secret。 endpoint（必选）：实例的服务地址。更多信息，请参见服务地址。 instanceName（必选）：实例名称。其他常用配置项的说明，请参见配置项。
user	************************	阿里云账号或者RAM用户的AccessKey ID。
password	********************************	阿里云账号或者RAM用户的AccessKey Secret。

您可以通过URL或者Properties方式传递AccessKey和配置项，此处以通过公网访问华东1（杭州）地域下myinstance实例为例介绍。

通过URL传递AccessKey和配置项

DriverManager.getConnection("jdbc:ots:https://************************:********************************@myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance?enableRequestCompression=true");

通过Properties传递AccessKey和配置项

Properties info = new Properties();
info.setProperty("user", "************************");
info.setProperty("password", "********************************");
info.setProperty("enableRequestCompression", "true");
DriverManager.getConnection("jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance", info);

执行SQL语句。

您可以使用createStatement或者prepareStatement方法创建SQL语句。

说明当前支持的SQL语句请参见SQL支持功能说明。

使用createStatement创建SQL语句

// 设置SQL语句，此处以查询test_table表中id列和name列的数据为例介绍，请根据实际需要设置。
String sql = "SELECT id,name FROM test_table";

Statement stmt = conn.createStatement();
ResultSet resultSet = stmt.executeQuery(sql);
while (resultSet.next()) {
    String id = resultSet.getString("id");       
    String name = resultSet.getString("name");                 
    System.out.println(id);
    System.out.println(name);
}

resultSet.close();
stmt.close();

使用prepareStatement创建SQL语句

// 设置SQL语句，此处以查询test_table表中pk为指定值的数据为例介绍，请根据实际需要设置。
String sql = "SELECT * FROM test_table WHERE pk = ?";

PreparedStatement stmt = connection.prepareStatement(sql);
stmt.setLong(1, 1);
ResultSet resultSet = stmt.executeQuery();
ResultSetMetaData metaData = resultSet.getMetaData();
while (resultSet.next()) {
    int columnCount = metaData.getColumnCount();
    for (int i=0; i< columnCount;i++) {
        String columnName = metaData.getColumnName(i+1);
        String columnValue = resultSet.getString(columnName);
        System.out.println(columnName);
        System.out.println(columnValue);
    }
}

resultSet.close();
stmt.close();

完整示例

查询华东1（杭州）地域下myinstance实例中test_table的所有数据。

public class Demo {
    public static void main(String[] args) throws SQLException, ClassNotFoundException {

        Class.forName("com.alicloud.openservices.tablestore.jdbc.OTSDriver");

        String url = "jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance";
        String user = "************************";
        String password = "********************************";
        Connection conn = DriverManager.getConnection(url, user, password);

        String sql = "SELECT * FROM test_table";
        Statement stmt = conn.createStatement();
        ResultSet resultSet = stmt.executeQuery(sql);
        ResultSetMetaData metaData = resultSet.getMetaData();
        while (resultSet.next()) {
            int columnCount = metaData.getColumnCount();
            for (int i=0; i< columnCount;i++) {
                String columnName = metaData.getColumnName(i+1);
                String columnValue = resultSet.getString(columnName);
                System.out.println(columnName);
                System.out.println(columnValue);
            }
        }
        resultSet.close();
        stmt.close();
        conn.close();    // 请务必关闭连接，否则程序无法退出。
    }
}

配置项

表格存储JDBC驱动基于表格存储的Java SDK实现，通过JDBC您可以修改Java SDK的配置项。常用配置项的详细说明请参见下表。

配置项	示例值	说明
enableRequestCompression	false	是否压缩请求数据。取值范围如下： true：压缩请求数据。 false（默认）：不压缩请求数据。
enableResponseCompression	false	是否压缩响应数据。取值范围如下： true：压缩响应数据。 false（默认）：不压缩响应数据。
ioThreadCount	2	HttpAsyncClient的IOReactor的线程数，默认与CPU核数相同。
maxConnections	300	允许打开的最大HTTP连接数。
socketTimeoutInMillisecond	30000	Socket层传输数据的超时时间。单位为毫秒。0表示无限等待。
connectionTimeoutInMillisecond	30000	建立连接的超时时间。单位为毫秒。0表示无限等待。
retryThreadCount	1	用于执行错误重试的线程池中线程个数。
syncClientWaitFutureTimeoutInMillis	-1	异步等待的超时时间。单位为毫秒。
connectionRequestTimeoutInMillisecond	60000	发送请求的超时时间。单位为毫秒。

数据类型转换

表格存储支持Integer（整型）、Double（浮点数）、String（字符串）、Binary（二进制）和Boolean（布尔值）五种数据类型。通过Java SDK使用JDBC直连表格存储时，JDBC驱动能够对Java类型和表格存储数据类型进行自动转换。

Java类型转换为表格存储数据类型

当使用PreparedStatement方法为SQL语句中的参数赋值时，Java中Byte、Short、Int、Long、BigDecimal、Float、Double、String、CharacterStream、Bytes、Boolean类型均能传递给表格存储SQL引擎。

PreparedStatement stmt = connection.prepareStatement("SELECT * FROM t WHERE pk = ?");
stmt.setLong(1, 1);                                // 支持的类型转换。
stmt.setURL(1, new URL("https://aliyun.com/"));    // 不支持的类型转换，系统会抛出异常。

表格存储数据类型转换为Java类型

当使用ResultSet方法获取SQL返回结果时，表格存储数据类型自动转换为Java数据类型的注意事项请参见下表。

表格存储数据类型	转换原则说明
Integer	转换为整型时，如果值超出类型的数值范围，则系统会抛出异常。转换为浮点数时，转换后的值会丢失精度。转换为字符串或者二进制时，等效于toString()。转换为布尔值时，如果值为非0值，则转换后的值为真。
Double
String	转换为整型或者浮点数时，如果解析失败，则系统会抛出异常。转换为布尔值时，如果字符串为true，则转换后的值为真。
Binary	转换为整型或者浮点数时，如果解析失败，则系统会抛出异常。转换为布尔值时，如果字符串为true，则转换后的值为真。
Boolean	转换为整型或者浮点数时，如果值为真，则转换后的值为1；如果值为假，则转换后的值为0。转换为字符串或者二进制时，等效于toString()。

Statement stmt = conn.createStatement();
ResultSet resultSet = stmt.executeQuery("SELECT count(*) FROM t");
while (resultSet.next()) {
    resultSet.getLong(1);               // 支持的类型转换。
    resultSet.getCharacterStream(1);    // 不支持的类型转换，系统会抛出异常。
}

表格存储数据类型和Java类型转换的支持情况请参见下表。

说明 “✓”表示正常转换，“～”表示可能抛出异常，“×”表示无法转换。

类型转换	Integer	Double	String	Binary	Boolean
Byte	～	～	～	～	✓
Short	～	～	～	～	✓
Int	～	～	～	～	✓
Long	✓	～	～	～	✓
BigDecimal	✓	✓	～	～	✓
Float	✓	✓	～	～	✓
Double	✓	✓	～	～	✓
String	✓	✓	✓	✓	✓
CharacterStream	×	×	✓	✓	×
Bytes	✓	✓	✓	✓	✓
Boolean	✓	✓	✓	✓	✓