SQL API

❗️

This is a legacy Apache Ignite documentation

The new documentation is hosted here: https://ignite.apache.org/docs/latest/

In addition to JDBC drivers, Java developers can use special SQL APIs to query and modify data stored in the database:

• SqlFieldsQueries
• Example

SqlFieldsQueries

The SqlFieldsQuery class is an interface for executing SQL statements and navigating through the results. SqlFieldsQuery is executed through the IgniteCache.query(SqlFieldsQuery) method, which returns a query cursor.

You can choose to select only specific fields in order to minimize network and serialization overhead. SqlFieldsQuery accepts a standard SQL query as its constructor​ parameter and executes it as shown in the example below:

IgniteCache<Long, Person> cache = ignite.cache("personCache");

// Execute query to get names of all employees.
SqlFieldsQuery sql = new SqlFieldsQuery(
  "select concat(firstName, ' ', lastName) from Person");

// Iterate over the result set.
try (QueryCursor<List<?>> cursor = cache.query(sql)) {
  for (List<?> row : cursor)
    System.out.println("personName=" + row.get(0));
}

🚧

Queryable Fields Definition

Before specific fields can be accessed from SqlFieldsQuery, they have to be a part of the SQL schema. Use standard DDL commands or Java specific annotations and QueryEntity configuration for the fields definition.

With SqlFieldsQuery you can execute the rest of DML commands in order to modify the data:

IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery(
    "INSERT INTO Person(id, firstName, lastName) VALUES(?, ?, ?)").
    setArgs(1L, "John", "Smith"));

IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery("UPDATE Person set lastName = ? " +
         "WHERE id >= ?").setArgs("Jones", 2L));

IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery("DELETE FROM Person " +
         "WHERE id >= ?").setArgs(2L));

IgniteCache<Long, Person> cache = ignite.cache("personCache");

cache.query(new SqlFieldsQuery("MERGE INTO Person(id, firstName, lastName)" +
           " values (1, 'John', 'Smith'), (5, 'Mary', 'Jones')"));

Example

Ignite distribution includes ready-to-run SqlDmlExample as a part of its sources. This example demonstrates the usage of all the above-mentioned DML operations.