Collections in JPQL and Criteria Queries

Collections may appear in JPQL queries:

as parameters - when collections are assigned as arguments.
as path expressions - in navigation to persistent collection fields.

This page covers the following topics:

IS [NOT] EMPTY

The IS [NOT] EMPTY operator checks whether a specified collection is empty or not.

For example:

c.languages IS EMPTY is TRUE if the collection is empty and FALSE otherwise.
c.languages IS NOT EMPTY is FALSE if the collection is empty and TRUE otherwise.

SIZE

The SIZE(collection) function returns the number of elements in a specified collection.

For example:

SIZE(c.languages) is evaluated to the number of languages in that collection.

[NOT] MEMBER [OF]

The [NOT] MEMBER OF operator checks if a specified element is contained in a specified persistent collection field.

For example:

'English' MEMBER OF c.languages is TRUE if languages contains 'English' and FALSE if not.

'English' NOT MEMBER OF c.languages is TRUE if languages does not contain 'English'.

[NOT] IN

The [NOT] IN operator provides an additional method for checking if a specified element is contained in a collection.

JPA distinguishes between the MEMBER OF operator, which should be used for checking a collection field, and the IN operator, which should be used for checking other collections, such as a collection that is passed to the query as a parameter.

For example:

'English' IN :languages is TRUE if the argument for the languages parameter is a collection that contains 'English' and FALSE if not.
'English' NOT IN :languages is TRUE if the argument for the languages parameter is a collection that doesn't contain 'English'.

ObjectDB enables as an extension to standard JPQL to use both operators (IN and MEMBER OF) with any type of collection, so in ObjectDB these operators are treated as synonyms.

Criteria Query Collection Expressions

JPQL collection operators and functions (which are described above) are available also as JPA criteria query expressions. The CriteriaBuilderjavax.persistence.criteria.CriteriaBuilderJPA interfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings.See JavaDoc Reference Page... interface provides factory methods for building these expressions, as shown in the following examples:

  // Create path and parameter expressions:
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Collection<String>> languages = country.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("languages");
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<String> param = cb.parameterparameter(paramClass)CriteriaBuilder's methodCreate a parameter expression.See JavaDoc Reference Page...(String.class);

  // collection IS [NOT] EMPTY
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... e1 = cb.isEmptyisEmpty(collection)CriteriaBuilder's methodCreate a predicate that tests whether a collection is empty.See JavaDoc Reference Page...(languages);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... e2 = cb.isNotEmptyisNotEmpty(collection)CriteriaBuilder's methodCreate a predicate that tests whether a collection is
  not empty.See JavaDoc Reference Page...(languages);

  // element [NOT] MEMBER OF collection
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... m1 = cb.isMemberisMember(elem, collection)CriteriaBuilder's methodCreate a predicate that tests whether an element is
  a member of a collection.See JavaDoc Reference Page...(param, languages);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... m2 = cb.isMemberisMember(elem, collection)CriteriaBuilder's methodCreate a predicate that tests whether an element is
  a member of a collection.See JavaDoc Reference Page...("English", languages);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... m3 = cb.isNotMemberisNotMember(elem, collection)CriteriaBuilder's methodCreate a predicate that tests whether an element is
  not a member of a collection.See JavaDoc Reference Page...(param, languages);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... m4 = cb.isNotMemberisNotMember(elem, collection)CriteriaBuilder's methodCreate a predicate that tests whether an element is
  not a member of a collection.See JavaDoc Reference Page...("French", languages);

  // element [NOT] IN collection:
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... i1 = param.inin(values)Expression's methodCreate a predicate to test whether the expression is a member
 of the argument list.See JavaDoc Reference Page...(languages);
  Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or
 disjunction of restrictions.See JavaDoc Reference Page... i2 = param.inin(values)Expression's methodCreate a predicate to test whether the expression is a member
 of the collection.See JavaDoc Reference Page...("English", "French");

  // SIZE(collection)
  Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<Integer> size = cb.sizesize(collection)CriteriaBuilder's methodCreate an expression that tests the size of a collection.See JavaDoc Reference Page...(languages);

< Date and Time in JPQL ^ Query Expressions Comparison Operators >