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);");

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 GridGain 9, 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));
    }
}

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 GridGain 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);

© 2024 GridGain Systems, Inc. All Rights Reserved. Privacy Policy | Legal Notices. GridGain® is a registered trademark of GridGain Systems, Inc.
Apache, Apache Ignite, the Apache feather and the Apache Ignite logo are either registered trademarks or trademarks of The Apache Software Foundation.

Last updated on Nov 21, 2024