The Ickle query language is subset of the JPQL query language, with some extensions for full-text.
The parser syntax has some notable rules:
-
Whitespace is not significant.
-
Wildcards are not supported in field names.
-
A field name or path must always be specified, as there is no default field.
-
&&
and||
are accepted instead ofAND
orOR
in both full-text and JPA predicates. -
!
may be used instead ofNOT
. -
A missing boolean operator is interpreted as
OR
. -
String terms must be enclosed with either single or double quotes.
-
Fuzziness and boosting are not accepted in arbitrary order; fuzziness always comes first.
-
!=
is accepted instead of<>
. -
Boosting cannot be applied to
>
,>=
,<
,<=
operators. Ranges may be used to achieve the same result.
Ickle support many filtering operators that can be used for both indexed and non-indexed fields.
Operator | Description | Example |
---|---|---|
|
Checks that the left operand is equal to one of the elements from the Collection of values given as argument. |
|
|
Checks that the left argument (which is expected to be a String) matches a wildcard pattern that follows the JPA rules. |
|
|
Checks that the left argument is an exact match of the given value. |
|
|
Checks that the left argument is different from the given value. |
|
|
Checks that the left argument is greater than the given value. |
|
|
Checks that the left argument is greater than or equal to the given value. |
|
|
Checks that the left argument is less than the given value. |
|
|
Checks that the left argument is less than or equal to the given value. |
|
|
Checks that the left argument is between the given range limits. |
|
Combining multiple attribute conditions with logical conjunction (and
) and disjunction (or
) operators in order to
create more complex conditions is demonstrated in the following example. The well known operator precedence rule for
boolean operators applies here, so the order of the operators is irrelevant. Here and
operator still has higher priority than or
even though or
was invoked first.
# match all books that have "Data Grid" in their title
# or have an author named "Manik" and their description contains "clustering"
FROM org.infinispan.sample.Book WHERE title LIKE '%Data Grid%' OR author.name = 'Manik' AND description like '%clustering%'
Boolean negation has highest precedence among logical operators and applies only to the next simple attribute condition.
# match all books that do not have "Data Grid" in their title and are authored by "Manik"
FROM org.infinispan.sample.Book WHERE title != 'Data Grid' AND author.name = 'Manik'
Changing the precedence of logical operators is achieved with parenthesis:
# match all books that have an author named "Manik" and their title contains
# "Data Grid" or their description contains "clustering"
FROM org.infinispan.sample.Book WHERE author.name = 'Manik' AND ( title like '%Data Grid%' OR description like '% clustering%')
In some use cases returning the whole domain object is overkill if only a small subset of the attributes are actually
used by the application, especially if the domain entity has embedded entities. The query language allows you to specify
a subset of attributes (or attribute paths) to return - the projection. If projections are used then the QueryResult.list()
will not return the whole domain entity but will return a List
of Object[]
, each slot in the array corresponding to
a projected attribute.
# match all books that have "Data Grid" in their title or description
# and return only their title and publication year
SELECT title, publicationYear FROM org.infinispan.sample.Book WHERE title like '%Data Grid%' OR description like '%Data Grid%'
It is possible to project the cache entry version, using the version
projection function.
# return the title, publication year and the cache entry version
SELECT b.title, b.publicationYear, version(b) FROM org.infinispan.sample.Book b WHERE b.title like '%Data Grid%'
It is possible to project the cache entry value together with other projections.
It can be used for instance to project the cache entry value together with the cache entry version
in the same Object[]
returned hit.
# return the cache entry value and the cache entry version
SELECT b, version(b) FROM org.infinispan.sample.Book b WHERE b.title like '%Data Grid%'
Ordering the results based on one or more attributes or attribute paths is done with the ORDER BY
clause. If multiple sorting criteria
are specified, then the order will dictate their precedence.
# match all books that have "Data Grid" in their title or description
# and return them sorted by the publication year and title
FROM org.infinispan.sample.Book WHERE title like '%Data Grid%' ORDER BY publicationYear DESC, title ASC
{brandname} has the ability to group query results according to a set of grouping fields and construct aggregations of the results from each group by applying an aggregation function to the set of values that fall into each group. Grouping and aggregation can only be applied to projection queries (queries with one or more field in the SELECT clause).
The supported aggregations are: avg
, sum
, count
, max
, and min
.
The set of grouping fields is specified with the GROUP BY
clause and the order used for defining grouping fields is
not relevant. All fields selected in the projection must either be grouping fields
or else they must be aggregated using one of the grouping functions described below. A projection field can be
aggregated and used for grouping at the same time. A query that selects only grouping fields but no aggregation fields
is legal.
Example: Grouping Books by author and counting them.
SELECT author, COUNT(title) FROM org.infinispan.sample.Book WHERE title LIKE '%engine%' GROUP BY author
Note
|
A projection query in which all selected fields have an aggregation function applied and no fields are used for grouping is allowed. In this case the aggregations will be computed globally as if there was a single global group. |
You can apply the following aggregation functions to a field:
Aggregation function | Description |
---|---|
|
Computes the average of a set of numbers. Accepted values are primitive numbers and instances of |
|
Counts the number of non-null rows and returns a |
|
Returns the greatest value found. Accepted values must be instances of |
|
Returns the smallest value found. Accepted values must be instances of |
|
Computes the sum of a set of Numbers. If there are no non-null values the result is |
Field Type | Return Type |
---|---|
Integral (other than BigInteger) |
Long |
Float or Double |
Double |
BigInteger |
BigInteger |
BigDecimal |
BigDecimal |
Aggregation queries can include filtering conditions, like usual queries. Filtering can be performed in two stages: before
and after the grouping operation. All filter conditions defined before invoking the groupBy()
method will be applied
before the grouping operation is performed, directly to the cache entries (not to the final projection). These filter
conditions can reference any fields of the queried entity type, and are meant to restrict the data set that is going to
be the input for the grouping stage. All filter conditions defined after invoking the groupBy()
method will be applied to
the projection that results from the projection and grouping operation. These filter conditions can either reference any
of the groupBy()
fields or aggregated fields. Referencing aggregated fields that are not specified in the select clause
is allowed; however, referencing non-aggregated and non-grouping fields is forbidden. Filtering in this phase will
reduce the amount of groups based on their properties. Sorting can also be specified similar to usual queries. The
ordering operation is performed after the grouping operation and can reference any of the groupBy()
fields or aggregated
fields.
You can delete entities from {brandname} caches with the following syntax:
DELETE FROM <entityName> [WHERE condition]
-
Reference only single entities with
<entityName>
. DELETE queries cannot use joins. -
WHERE conditions are optional.
DELETE queries cannot use any of the following:
-
Projections with SELECT statements
-
Grouping and aggregation
-
ORDER BY clauses
Tip
|
Invoke the |