Tablestore:Use JDBC to access Tablestore

Step 1: Install the JDBC driver

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 of 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 Endpoints. 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 information about 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();