docs/_docs/developers-guide/sql/sql-api.adoc

// Licensed to the Apache Software Foundation (ASF) under one or more
// contributor license agreements.  See the NOTICE file distributed with
// this work for additional information regarding copyright ownership.
// The ASF licenses this file to You under the Apache License, Version 2.0
// (the "License"); you may not use this file except in compliance with
// the License.  You may obtain a copy of the License at
//
// http://www.apache.org/licenses/LICENSE-2.0
//
// Unless required by applicable law or agreed to in writing, software
// distributed under the License is distributed on an "AS IS" BASIS,
// WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
// See the License for the specific language governing permissions and
// limitations under the License.
= Java SQL API

In your Java projects, you can use the Java SQL API to execute SQL statements and getting results.

== Creating Tables

Here is an example of how you can create a new table on a cluster:

[source, java]
----
client.sql().execute(null, "CREATE TABLE IF NOT EXISTS Person (id int primary key, name varchar, age int);");
----

NOTE: ResultSet is closable, but it is safe to skip `close()` method for DDL and DML queries, as they do not keep server cursor open.

== Filling Tables

With Ignite 3, you can fill the table by adding rows one by one, or in a batch. In both cases, you create an `INSERT` statement, and then exeсute it:

[source, java]
----
long rowsAdded = Arrays.stream(client.sql().executeBatch(tx,
    "INSERT INTO Person (id, name, age) values (?, ?, ?)",
    BatchedArguments.of(1, "John", 46)
        .add(2, "Jane", 28)
        .add(3, "Mary", 51)
        .add(4, "Richard", 33)))
    .sum();
----

== Partition-Specific SELECTs

When executing a SELECT operation, you can use the system `\__part` column to only `SELECT` data in a specific partition. To find out partition information, use the SELECT request that explicitly includes the `__part` column as its part:

[source, sql]
----
SELECT city_id, id, "__part"  FROM Person;
----

Once you know the partition, you can use it in the `WHERE` clause:

[source, sql]
----
SELECT city_id, id FROM Person WHERE "__part"=23;
----


== Getting Data From Tables

To get data from a table, execute the `SELECT` statement to get a set of results. SqlRow can provide access to column values by column name or column index. You can then iterate through results to get data:

[source, java]
----
try (ResultSet<SqlRow> rs = client.sql().execute(null, "SELECT id, name, age FROM Person")) {
    while (rs.hasNext()) {
        SqlRow row = rs.next();
        System.out.println("    "
            + row.value(1) + ", "
            + row.value(2));
    }
}
----

NOTE: ResultSet may hold server-side cursor open due to lazy query execution. It must be closed manually, or by using the `try-with-resources` statement.

== SQL Scripts

The default API executes SQL statements one at a time. For large SQL statements, pass them to the `executeScript()` method. The statements will be batched together similar to using `SET STREAMING` command in Ignite 8, significantly improving performance when executing a large number of queries at once. These statements will be executed in order.

[tabs]
--
tab:Java[]
[source, java]
----
String script = "CREATE TABLE IF NOT EXISTS Person (id int primary key, name varchar, age int default 0);"
              + "INSERT INTO Person (id, name, age) VALUES ('1', 'John', '46');";
client.sql().executeScript(script);
----
--

NOTE: Execution of each statement is considered complete when the first page is ready to be returned. As a result, when working with large data sets, SELECT statement may be affected by later statements in the same script.

=== Query Cancellation

To cancel a query, create and pass the cancellation token to the execution method:

[tabs]
--
tab:Java[]
----
CancelHandle cancelHandle = CancelHandle.create();
CancellationToken cancelToken = cancelHandle.token();

ignite.sql().execute(null, cancelToken, "CREATE TABLE IF NOT EXISTS Person (id int primary key, name varchar, age int);");
----

tab:C++[]
----
std::shared_ptr<cancel_handle> handle = cancel_handle::create();
std::shared_ptr<cancellation_token> token = handle->get_token();

client.get_sql().execute(nullptr, token.get(), "CREATE TABLE IF NOT EXISTS Person (id int primary key, name varchar, age int);", {});
----
--

After the query is submitted, you can cancel all queries that use the tokens from the same `cancelHandle` object at any point by using the `cancel()` or `cancelAsync()` methods, for example:

[tabs]
--
tab:Java[]
----
CompletableFuture<Void> cancelled = cancelHandle.cancelAsync();
----
tab: .NET[]
----
var cts = new CancellationTokenSource();
await using var resultSet = await Client.Sql.ExecuteAsync(null, "CREATE TABLE IF NOT EXISTS Person (id int primary key)", cts.Token);
await cts.CancelAsync();
----
tab:C++[]
----
handle->cancel_async(ignite_result<void> cancellationResult) {
// Handle cancellationResult here
});
----
--

Another way to cancel queries is by using the SQL link:sql-reference/operational-commands#kill-query[KILL QUERY] command. The query id can be retrieved via the `SQL_QUERIES` link:administrators-guide/metrics/system-views[system view].