Edit

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:

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:

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:

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:

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:

SELECT city_id, id, "__part"  FROM Person;

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

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:

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.

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.

© 2025 The Apache Software Foundation.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.