...

1. The user supplies a Criteria and a QueryOptions.
2. The client (or the API facade in embedded mode) generates the SELECT query based on the Criteria and sets the options (for now – just pageSize) in the query properties.
3. The client runs the SQL as usual, potentially via the public API.
4. The client obtains the ResultSet and turns it into an Iterator; it uses the view-defined Mappers to turn the Tuple into the target types.

Suggested Predicates List for Criteria API

...

• Comparisons: =, >, <, >=, <=, IN
• Combination: AND, OR, NOT
• Nulls: IS NULL, IS NOT NULL

Criteria query works with transactions similarly to SQL

...

• Query methods accept a transaction instance
• If transaction is null, an implicit read-only transaction is opened

...

Configuration options

• Ability to enforce an index(indexHint) to be used

• Ability to configure pageSize for query. This is useful for tuning

...

// Criteria
try (var cursor = kvView().queryCriteria(
        null,
        and(equal("intCol", 42), greaterThan("primitiveIntCol", 9000), equal("booleanCol", true)),
        CriteriaQueryOptions.builder().pageSize(10).build()
)) {
    // ...
}

// CriteriaBuilder
try (var cursor = kvView().queryCriteria(
        null,
        columnName("intCol").equal(42)
                .and(columnName("primitiveIntCol").greaterThan(9000))
                .and(columnName("booleanCol").equal(true))
)) {
     // ... 
}

//  SQL where clause as criteria.
try (var cursor = kvView().queryCriteria(null, sql("intCol = ? AND primitiveIntCol > ? AND booleanCol = true", 42, 9000))) {
     // ... 
}

...

Reactive API should be added for both SQL and Criteria, so it's not a point of this IEP.

USE INDEX-style hint looks like is not supported at this moment, but we still can add API and SQL generation for it.

NOTE: Comparing with table.get(PK) should be done with disabled partition awareness

...

Index Query in Apache Ignite 2.x: https://ignite.apache.org/docs/latest/key-value-api/using-cache-queries#executing-index-queries.

Tickets