[id='ickle-queries_{context}']
= Ickle queries

To use the API, first obtain a QueryFactory to the cache and then call the `.create()` method, passing in the string to use in the query.
Each `QueryFactory` instance is bound to the same `Cache` instance as the `Search`, but it is otherwise a stateless and thread-safe object that can be used for creating multiple queries in parallel.

For instance:

[source,java,tile="Using Ickle"]
----
// Remote Query, using protobuf
QueryFactory qf = org.infinispan.client.hotrod.Search.getQueryFactory(remoteCache);
Query<Transaction> q = qf.create("from sample_bank_account.Transaction where amount > 20");

// Embedded Query using Java Objects
QueryFactory qf = org.infinispan.query.Search.getQueryFactory(cache);
Query<Transaction> q = qf.create("from org.infinispan.sample.Book where price > 20");

// Execute the query
QueryResult<Book> queryResult = q.execute();
----

[NOTE]
====
A query will always target a single entity type and is evaluated over the contents of a single cache. Running a query over multiple caches or creating queries that target several entity types (joins) is not supported.
====

Executing the query and fetching the results is as simple as invoking the `execute()` method of the `Query` object. Once
executed, calling `execute()` on the same instance will re-execute the query.

== Pagination

You can limit the number of returned results by using  the `Query.maxResults(int maxResults)`. This can be used in
conjunction with `Query.startOffset(long startOffset)` to achieve pagination of the result set.

[source,java]
----
// sorted by year and match all books that have "clustering" in their title
// and return the third page of 10 results
Query<Book> query = queryFactory.create("FROM org.infinispan.sample.Book WHERE title like '%clustering%' ORDER BY year").startOffset(20).maxResults(10)
----

[NOTE]
====
If you don't explicitly set the `maxResults` for a query instance, {brandname} limits the number of results returned by the query to `100`.
You can change the default limit by setting the `query.default-max-results` cache property.
====

== Number of hits

The `QueryResult` object includes the `.hitCount()` method, which returns a hit count value that represents the total number of results from a query, regardless of any pagination parameter.
The hit count is only available for indexed queries for performance reasons.

=== Hit count accuracy

To optimize performance, the default accuracy of the hit count is set to `10000`.
You can limit the required accuracy of hit counts by setting `query.hit-count-accuracy` cache property.
Alternatively, you can set the limit on each query instance.

When the hit count exceeds the specified limit, {brandname} does not return any value for the hit count.
When using the HotRod client or embedded query API, {brandname} returns `null`, and when using the REST query API, {brandname} respons with `-1L`.
While setting the hit count accuracy to `Integer.MAX` would return accurate results for any query, it would negatively impact query performance.

For optimal performance, set the property value slightly above the expected hit count.
If you do not require precise hit counts, set it to a low value.

== Iteration

The `Query` object has the `.iterator()` method to obtain the results lazily. It returns an instance of `CloseableIterator` that must be closed after usage.

[NOTE]
====
The iteration support for Remote Queries is currently limited, as it will first fetch all entries to the client
before iterating.
====

== Named query parameters

Instead of building a new Query object for every execution it is possible to include named parameters in the query which
can be substituted with actual values before execution. This allows a query to be defined once and be efficiently
executed many times. Parameters can only be used on the right-hand side of an operator and are defined when the query is
created by supplying an object produced by the `org.infinispan.query.dsl.Expression.param(String paramName)` method to
the operator instead of the usual constant value. Once the parameters have been defined they can be set by invoking either
`Query.setParameter(parameterName, value)` or `Query.setParameters(parameterMap)` as shown in the examples below.
⁠
[source,java,tile="Using Named Parameters"]
----
QueryFactory queryFactory = Search.getQueryFactory(cache);
// Defining a query to search for various authors and publication years
Query<Book> query = queryFactory.create("SELECT title FROM org.infinispan.sample.Book WHERE author = :authorName AND publicationYear = :publicationYear").build();

// Set actual parameter values
query.setParameter("authorName", "Doe");
query.setParameter("publicationYear", 2010);

// Execute the query
List<Book> found = query.execute().list();
----

Alternatively, you can supply a map of actual parameter values to set multiple parameters at once:
⁠
[source,java,title="Setting multiple named parameters at once"]
----
Map<String, Object> parameterMap = new HashMap<>();
parameterMap.put("authorName", "Doe");
parameterMap.put("publicationYear", 2010);

query.setParameters(parameterMap);
----

[NOTE]
====
A significant portion of the query parsing, validation and execution planning effort is performed during the first
execution of a query with parameters. This effort is not repeated during subsequent executions leading to better
performance compared to a similar query using constant values instead of query parameters.
====

== Query execution

The `Query` API provides two methods for executing Ickle queries on a cache:

* `Query.execute()` runs a SELECT statement and returns a result.
* `Query.executeStatement()` runs a DELETE statement and modifies data.

[NOTE]
====
You should always invoke `executeStatement()` to modify data and invoke `execute()` to get the result of a query.
====

[role="_additional-resources"]
.Additional resources
* link:{javadocroot}/org/infinispan/query/dsl/Query.html#execute()[org.infinispan.query.dsl.Query.execute()]
* link:{javadocroot}/org/infinispan/query/dsl/Query.html#executeStatement()[org.infinispan.query.dsl.Query.executeStatement()]
