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]
----
ignite.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.

=== Using Sequences

When creating a table, you can designate the primary key column to be filled automatically from the sequence the values for your primary key by using sql sequences:

[source, java]
----
ignite.sql().execute(null, "CREATE SEQUENCE IF NOT EXISTS defaultSequence;");

ignite.sql().execute(null, "CREATE TABLE IF NOT EXISTS Person (id bigint default NEXTVAL('defaultSequence') primary key, city_id bigint, name varchar, age int, company varchar);");
----


== 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(null,
    "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();
----

=== Using Sequences

When filling a table, you can generate the values for your primary key by using sql sequences:

[source, java]
----
ignite.sql().execute(null, "CREATE SEQUENCE IF NOT EXISTS defaultSequence;");

ignite.sql().execute(null, "CREATE TABLE IF NOT EXISTS Person (id bigint default NEXTVAL('defaultSequence') primary key, city_id bigint, name varchar, age int, company varchar);");

long rowsAdded = Arrays.stream(client.sql().executeBatch(null,
    "INSERT INTO Person (city_id, name, age, company) values (?, ?, ?, ?)",
    BatchedArguments.of(1, "John", 46, "oldCorp")
        .add(2, "Jane", 28, "newCorp")
        .add(3, "Mary", 51, "newCorp")
        .add(4, "Richard", 33, "oldCorp")))
    .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.