This topic describes how to use Tablestore SDK for Java to query data in a table by executing SQL statements.
Notes
The SQL query feature is supported by Tablestore SDK for Java V5.13.0 and later. When you use the SQL query feature, make sure that a supported version of Tablestore SDK for Java is installed.
Before you query data by executing SQL statements, you must create a mapping table for the data table or search index. For more information, see Create a mapping table for a table.
Prerequisites
A Tablestore client is initialized. For more information, see Initialize a Tablestore Client.
Method
public SQLQueryResponse sqlQuery(SQLQueryRequest request) throws TableStoreException, ClientExceptionSample code
The following sample code provides an example on how to execute the SELECT statement to query data in the test_table table and return a maximum of 10 rows.
Before you run the code, replace the table name and field names in the code with the actual information.
public static void queryDataExample(SyncClient client) {
// Specify the SQL statement.
SQLQueryRequest request = new SQLQueryRequest("select order_id, user_id, sku_id, price, num, total_price, order_status, create_time, modified_time from test_table limit 10;");
SQLQueryResponse response = client.sqlQuery(request);
// Specify the RequestId information.
System.out.println("RequestId: " + response.getRequestId());
/*// Read throughput consumption information
System.out.println("Read throughput consumption (by table):");
for(Map.Entry<String, ConsumedCapacity> entry : response.getConsumedCapacity().entrySet()) {
System.out.println(entry.getKey() + ": " + entry.getValue().getCapacityUnit().getReadCapacityUnit() + "CU");
}*/
// Obtain the SQL query results.
SQLResultSet resultSet = response.getSQLResultSet();
// Return the schema information.
System.out.println("Schema: " + response.getSQLResultSet().getSQLTableMeta().getSchema());
// Data details
System.out.println("Data details:");
while (resultSet.hasNext()) {
SQLRow row = resultSet.next();
System.out.println(row.get("order_id") + ", "
+ row.get("user_id") + ", "
+ row.get("sku_id") + ", "
+ row.get("price") + ", "
+ row.get("num") + ", "
+ row.get("total_price") + ", "
+ row.get("order_status") + ", "
+ row.get("create_time") + ", "
+ row.get("modified_time"));
}
}