Write data with SQL

Use Tablestore SDK for Java to execute INSERT statements and write one or more rows to a data table for which local transactions are disabled.

Prerequisites

Install the Tablestore SDK for Java and initialize a client.
Use Tablestore SDK for Java 5.17.11 or later.
Create a mapping table for the data table to which you want to write data, and make sure that local transactions are disabled for the data table.
If you use a RAM user, grant the SQL_DML permission to the RAM user.

Description

Call the sqlQuery method to execute an INSERT statement and write data to a data table. If the primary key of a row already exists, the request fails.

SQLQueryResponse sqlQuery(SQLQueryRequest request)

The following example writes one row to the example_table data table.

String query = "INSERT INTO example_table "
        + "(tenant_id, pk, name, score) "
        + "VALUES (1, 'row1', 'example', 10)";
SQLQueryRequest request = new SQLQueryRequest(query);

SQLQueryResponse response = client.sqlQuery(request);
System.out.println("Affected rows: " + response.getAffectedRows());

Parameters

SQLQueryRequest contains the following parameter.

Name	Type	Description
query (required)	String	The `INSERT` statement that specifies the mapping table, columns, and values to write.

Response

SQLQueryResponse contains the following fields related to the write result.

Field	Type	Description
`affectedRows`	long	Call `getAffectedRows()` to obtain the number of rows that are successfully written.
`consumedCapacityByTable`	Map<String, ConsumedCapacity>	Call `getConsumedCapacity()` to obtain the read and write CUs consumed by each data table. The server consumes read CUs to validate the rows and write CUs to write the rows.
`type`	SQLStatementType	Call `getSQLStatementType()` to obtain the SQL statement type. `SQL_INSERT` is returned for an `INSERT` statement.

Scenario examples

Write multiple rows

An INSERT statement can write up to 200 rows. All rows in the statement must have the same partition key and are written as an atomic operation: either all rows are written or none of the rows are written.

String query = "INSERT INTO example_table "
        + "(tenant_id, pk, name, score) VALUES "
        + "(1, 'row1', 'example-a', 10), "
        + "(1, 'row2', 'example-b', 20), "
        + "(1, 'row3', 'example-c', 30)";
SQLQueryRequest request = new SQLQueryRequest(query);

SQLQueryResponse response = client.sqlQuery(request);
System.out.println("Affected rows: " + response.getAffectedRows());