Use SQL queries with a direct JDBC connection

Prerequisites

• An AccessKey pair (RAM users require the "Action": "ots:SQL*" permission)

• A data table and its mapping table (DDL operations)

Step 1: Install the JDBC driver

The driver is available as a Maven dependency or a standalone JAR.

<dependency>
  <groupId>com.aliyun.openservices</groupId>
  <artifactId>tablestore-jdbc</artifactId>
  <version>5.17.0</version>
</dependency>

Step 2: Use a direct JDBC connection

Load the driver, connect to an instance, and then run SQL statements.

1. Load the Tablestore JDBC driver with Class.forName().

   The driver class name is com.alicloud.openservices.tablestore.jdbc.OTSDriver.

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

2. Connect to a Tablestore instance with 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 connection parameters.

   Parameter	Description
   url	The Tablestore JDBC URL. Format: jdbc:ots:schema://[accessKeyId:accessKeySecret@]endpoint/instanceName[?param1=value1&...&paramN=valueN]. The URL fields are: schema (required): The protocol. Set this field to https. accessKeyId:accessKeySecret (optional): The AccessKey ID and AccessKey Secret of your Alibaba Cloud account or RAM user. endpoint (required): The endpoint of the instance. instanceName (required): The name of the instance. For other configuration items, see Configuration.
   user	The AccessKey ID of your Alibaba Cloud account or RAM user.
   password	The AccessKey Secret of your Alibaba Cloud account or RAM user.

   Pass your AccessKey pair and settings through the URL or a Properties object. The following examples connect to the myinstance instance in the China (Hangzhou) region over the Internet.

   URL

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

   Properties

   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. Run SQL statements.

   Use createStatement or prepareStatement to run queries.

   createStatement

   String sql = "SELECT pk1, col_a FROM test_table";
   Statement stmt = conn.createStatement();
   ResultSet rs = stmt.executeQuery(sql);
   while (rs.next()) {
       System.out.println(rs.getString("pk1") + ", " + rs.getLong("col_a"));
   }
   rs.close();
   stmt.close();

   prepareStatement

   String sql = "SELECT * FROM test_table WHERE pk = ?";
   PreparedStatement stmt = conn.prepareStatement(sql);
   stmt.setLong(1, 1);
   ResultSet rs = stmt.executeQuery();
   ResultSetMetaData meta = rs.getMetaData();
   while (rs.next()) {
       for (int i = 1; i <= meta.getColumnCount(); i++) {
           System.out.println(meta.getColumnName(i) + " = " + rs.getString(i));
       }
   }
   rs.close();
   stmt.close();

Complete example

The following example queries data from test_table in a Tablestore instance.

public class Demo {
    public static void main(String[] args) throws Exception {
        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);

        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM test_table");
        ResultSetMetaData meta = rs.getMetaData();
        int colCount = meta.getColumnCount();
        while (rs.next()) {
            for (int i = 1; i <= colCount; i++) {
                System.out.print(meta.getColumnName(i) + "=" + rs.getString(i) + "\t");
            }
            System.out.println();
        }

        rs.close();
        stmt.close();
        conn.close();
    }
}

Configuration

The Tablestore JDBC driver is built on the Java SDK and supports configuration through URL parameters or Properties.

Data type conversion

Tablestore supports five data types: Integer, Double, String, Binary, and Boolean. The JDBC driver automatically converts between Java types and Tablestore data types.

PreparedStatement stmt = conn.prepareStatement("SELECT * FROM t WHERE pk = ?");
stmt.setLong(1, 1);                                // Supported
stmt.setURL(1, new URL("https://aliyun.com/"));    // Not supported — throws an exception

Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT count(*) FROM t");
while (rs.next()) {
    rs.getLong(1);               // Supported
    rs.getCharacterStream(1);    // Not supported — throws an exception
}