This topic describes how to use JDBC to access Tablestore.

Precautions

The SQL query feature is available in the China (Hangzhou), China (Shanghai), China (Beijing), China (Zhangjiakou), China (Shenzhen), and Singapore regions.

Prerequisites

  • If you want to use a RAM user to perform operations, make sure that a RAM user is created and all SQL operation permissions are granted to the RAM user. You can configure "Action": "ots:SQL*" in the custom policy to grant all SQL operation permissions to the RAM user. For more information, see Grant permissions to a RAM user.
  • An AccessKey pair that consists of an AccessKey ID and an AccessKey secret is obtained. For more information, see Obtain an AccessKey pair.
  • A data table is created, and a mapping table is created for the data table. For more information, see Create a data table and Create tables and mapping tables.

Step 1: Install the JDBC driver

You can install the JDBC driver by using one of the following methods:

  • Download the JDBC driver for Tablestore and import the JDBC driver to the project. For more information about the download path, see JDBC driver for Tablestore.
  • Add dependencies to a Maven project.
    To use the JDBC driver for Tablestore in Maven, you need only to add the corresponding dependencies to the pom.xml file. In this example, JDBC driver 5.13.5 is used. Add the following content to <dependencies>:
    <dependency>
      <groupId>com.aliyun.openservices</groupId>
      <artifactId>tablestore-jdbc</artifactId>
      <version>5.13.5</version>
    </dependency>                

Step 2: Access Tablestore by using JDBC

  1. Load the JDBC driver for Tablestore by using Class.forName().

    The name of the JDBC driver for Tablestore is com.alicloud.openservices.tablestore.jdbc.OTSDriver.

    Class.forName("com.alicloud.openservices.tablestore.jdbc.OTSDriver");
  2. Access a Tablestore instance by using JDBC.
    String url = "jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance";
    String user = "************************";
    String password = "********************************";
    Connection conn = DriverManager.getConnection(url, user, password);
    The following table describes the parameters that you must configure to access a Tablestore instance by using JDBC.
    Parameter Example Description
    url jdbc:ots:https://myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance The URL of the JDBC driver for Tablestore in the format jdbc:ots:schema://[accessKeyId:accessKeySecret@]endpoint/instanceName[?param1=value1&...&paramN=valueN]. The URL contains the following fields:
    • schema: This field is required and specifies the protocol that is used by the JDBC driver for Tablestore. In most cases, this field is set to https.
    • accessKeyId:accessKeySecret: This field is optional and specifies the AccessKey ID and AccessKey secret of your Alibaba Cloud account or a RAM user.
    • endpoint: This field is required and specifies the endpoint of the instance. For more information, see Endpoint.
    • instanceName: This field is required and specifies the name of the instance.

    For more information about other configuration items, see Configuration items.

    user ************************ The AccessKey ID of your Alibaba Cloud account or a RAM user.
    password ******************************** The AccessKey secret of your Alibaba Cloud account or a RAM user.
    You can pass the AccessKey pair and configuration items by using a URL or the Properties parameter. The following example shows how to access the myinstance instance in the China (Hangzhou) region over the Internet.
    • Pass the AccessKey pair and configuration items by using a URL
      DriverManager.getConnection("jdbc:ots:https://************************:********************************@myinstance.cn-hangzhou.ots.aliyuncs.com/myinstance?enableRequestCompression=true");
    • Pass the AccessKey pair and configuration items by using the Properties parameter
      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. Execute SQL statements.
    You can use the createStatement or prepareStatement method to create SQL statements.
    Note For more information about the supported SQL statements, see SQL features.
    • Use the createStatement method to create SQL statements
      // Create the SQL statement based on your business requirements. The following sample code shows how to query the data in the id and name columns in the test_table table: 
      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();
    • Use the prepareStatement method to create SQL statements
      // Create the SQL statement based on your business requirements. The following sample code shows how to query the data with the specified primary key in the test_table table: 
      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();

Complete sample code

The following sample code shows how to query all data in the test_table table of the myinstance instance in the China (Hangzhou) region:

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();    // Close the connection. Otherwise, the program cannot exit. 
    }
}        
            

Configuration items

The JDBC driver for Tablestore is implemented based on Tablestore SDK for Java. You can use JDBC to modify the configuration items of Tablestore SDK for Java. The following table describes the common configuration items.

Configuration item Example Description
enableRequestCompression false Specifies whether to compress the request data. Default value: false. Valid values:
  • true: compresses the request data.
  • false: does not compress the request data.
enableResponseCompression false Specifies whether to compress the response data. Default value: false. Valid values:
  • true: compresses the response data.
  • false: does not compress the response data.
ioThreadCount 2 The number of IOReactor threads of the HttpAsyncClient. The default value is the same as the number of vCPUs.
maxConnections 300 The maximum number of allowed HTTP connections.
socketTimeoutInMillisecond 30000 The timeout period for data transmission at the Socket layer. Unit: milliseconds. The value of 0 indicates an indefinite wait.
connectionTimeoutInMillisecond 30000 The timeout period for connection setup. Unit: milliseconds. The value of 0 indicates an indefinite wait.
retryThreadCount 1 The number of threads that are used to execute retries in the thread pool.
syncClientWaitFutureTimeoutInMillis -1 The timeout period for the asynchronous wait. Unit: milliseconds.
connectionRequestTimeoutInMillisecond 60000 The timeout period for sending the request. Unit: milliseconds.

Data type conversion

Tablestore supports five data types: Integer, Double, String, Binary, and Boolean. When you use Tablestore SDK for Java and JDBC to access Tablestore, the JDBC driver can automatically convert data types between Java and Tablestore.

  • Convert Java data types to Tablestore data types

    If you use the PreparedStatement method to specify the values of parameters in SQL statements, the Byte, Short, Int, Long, BigDecimal, Float, Double, String, CharacterStream, Bytes, and Boolean data types in Java can be passed to the SQL engine of Tablestore.

    PreparedStatement stmt = connection.prepareStatement("SELECT * FROM t WHERE pk = ?");
    stmt.setLong(1, 1);                                // The data type can be converted. 
    stmt.setURL(1, new URL("https://aliyun.com/"));    // The data type cannot be converted, and the system throws an exception. 
  • Convert Tablestore data types to Java data types
    If you use the ResultSet method to obtain SQL query results, take note of the conversion rules in the following table. The following table describes the rules for the automatic conversion of Tablestore data types to Java data types.
    Data type in Tablestore Conversion rule
    Integer
    • When the system converts the data type to an integer type, the system throws an exception if the original value is out of the value range of the integer type.
    • When the system converts the data type to a floating-point type, the precision of the converted value is lower than the precision of the original value.
    • When the system converts the data type to the string or binary type, the converted value is the same as the result of processing the original value by using toString().
    • When the system converts the data type to the Boolean type and the original value is a non-zero value, the converted value is true.
    Double
    String
    • When the system converts the data type to an integer type or a floating-point type, the system throws an exception if parsing fails.
    • When the system converts the data type to the Boolean type and the original string is true, the converted value is true.
    Binary
    Boolean
    • When the system converts the data type to an integer type or a floating-point type and the original value is true, the converted value is 1. If the original value is false, the converted value is 0.
    • When the system converts the data type to the string or binary type, the converted value is the same as the result of processing the original value by using toString().
    Statement stmt = conn.createStatement();
    ResultSet resultSet = stmt.executeQuery("SELECT count(*) FROM t");
    while (resultSet.next()) {
        resultSet.getLong(1);               // The data type can be converted. 
        resultSet.getCharacterStream(1);    // The data type cannot be converted, and the system throws an exception. 
    }
    For more information about the data type conversion between Tablestore and Java, see the following table.
    Note In the following table, ticks (✓) indicate normal conversion, tildes (~) indicate that an exception may be thrown during conversion, and crosses (×) indicate that the conversion cannot be performed.
    Data type Integer Double String Binary Boolean
    Byte ~ ~ ~ ~
    Short ~ ~ ~ ~
    Int ~ ~ ~ ~
    Long ~ ~ ~
    BigDecimal ~ ~
    Float ~ ~
    Double ~ ~
    String
    CharacterStream × × ×
    Bytes
    Boolean