[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 has the `.hitCount()` method to return the total number of results of the query, regardless
of any pagination parameter. The hit count is only available for indexed queries for performance reasons.

== 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()]
