JDBC连接表格存储

注意事项

目前支持使用SQL查询功能的地域有华东1（杭州）、华东2（上海）、华北2（北京）、华北3（张家口）、华南1（深圳）、中国香港、德国（法兰克福）、美国（弗吉尼亚）、印度（孟买）和新加坡。

前提条件

• 如果要使用RAM用户进行操作，请确保已创建RAM用户，并为RAM用户授予所有SQL操作权限，即在自定义权限策略中配置"Action": "ots:SQL*"。具体操作，请参见配置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直连

1. 使用Class.forName()加载表格存储JDBC驱动。

   表格存储JDBC驱动名称为com.alicloud.openservices.tablestore.jdbc.OTSDriver。

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

2. 使用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);

3. 执行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的配置项。常用配置项的详细说明请参见下表。

数据类型转换

表格存储支持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
  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	✓	✓	✓	✓	✓