JDBC串連Tablestore - Tablestore

注意事項

目前支援使用SQL查詢功能的地區有華東1（杭州）、華東2（上海）、華北2（北京）、華北3（張家口）、華南1（深圳）和新加坡。

前提條件

如果要使用RAM使用者進行操作，請確保已建立RAM使用者，並為RAM使用者授予所有SQL操作許可權，即在自訂權限原則中配置"Action": "ots:SQL*"。具體操作，請參見t1985835.html#section_6hb_cr0_iv9。
已擷取AccessKey（包括AccessKey ID和AccessKey Secret）。具體操作，請參見擷取AccessKey。
已建立資料表並為資料表建立映射關係。具體操作，請分別參見建立資料表和建立表的映射關係。

步驟一：安裝JDBC驅動

您可以通過以下兩種方式安裝JDBC驅動。

下載Tablestore JDBC驅動並匯入到專案中。具體下載路徑請參見Tablestore JDBC驅動。
在Maven專案中加入依賴項
在Maven工程中使用Tablestore 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()載入Tablestore JDBC驅動。
Tablestore JDBC驅動名稱為com.alicloud.openservices.tablestore.jdbc.OTSDriver。
```
Class.forName("com.alicloud.openservices.tablestore.jdbc.OTSDriver");
```

使用JDBC串連Tablestore執行個體。

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	Tablestore JDBC的URL。格式為`jdbc:ots:schema://[accessKeyId:accessKeySecret@]endpoint/instanceName[?param1=value1&...&paramN=valueN]`。主要欄位說明如下： schema（必選）：Tablestore 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();    // 請務必關閉串連，否則程式無法退出。
    }
}

配置項

Tablestore JDBC驅動基於Tablestore的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	發送請求的逾時時間。單位為毫秒。

資料類型轉換

Tablestore支援Integer（整型）、Double（浮點數）、String（字串）、Binary（二進位）和Boolean（布爾值）五種資料類型。通過Java SDK使用JDBC直連Tablestore時，JDBC驅動能夠對Java類型和Tablestore資料類型進行自動轉換。

Java類型轉換為Tablestore資料類型
當使用PreparedStatement方法為SQL語句中的參數賦值時，Java中Byte、Short、Int、Long、BigDecimal、Float、Double、String、CharacterStream、Bytes、Boolean類型均能傳遞給Tablestore SQL引擎。
```
PreparedStatement stmt = connection.prepareStatement("SELECT * FROM t WHERE pk = ?");
stmt.setLong(1, 1);                                // 支援的類型轉換。
stmt.setURL(1, new URL("https://aliyun.com/"));    // 不支援的類型轉換，系統會拋出異常。
```

Tablestore資料類型轉換為Java類型

當使用ResultSet方法擷取SQL返回結果時，Tablestore資料類型自動轉換為Java資料類型的注意事項請參見下表。


Tablestore資料類型	轉換原則說明
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);    // 不支援的類型轉換，系統會拋出異常。
}

Tablestore資料類型和Java類型轉換的支援情況請參見下表。

说明 “✓”表示正常轉換，“～”表示可能拋出異常，“×”表示無法轉換。


類型轉換	Integer	Double	String	Binary	Boolean
Byte	～	～	～	～	✓
Short	～	～	～	～	✓
Int	～	～	～	～	✓
Long	✓	～	～	～	✓
BigDecimal	✓	✓	～	～	✓
Float	✓	✓	～	～	✓
Double	✓	✓	～	～	✓
String	✓	✓	✓	✓	✓
CharacterStream	×	×	✓	✓	×
Bytes	✓	✓	✓	✓	✓
Boolean	✓	✓	✓	✓	✓