WHERE clause (JPQL / Criteria API)

The WHERE clause adds filtering capabilities to the SELECT-FROM statement. It is essential for retrieving a specific subset of objects from the database.

How a WHERE clause works

The following query retrieves only countries with a population greater than the value of the parameter p:

SELECT c FROM Country c WHERE c.population > :p

The FROM clause of this query defines an iteration over all Country objects in the database, using c as the range variable. Before passing these Country objects to the SELECT clause to be collected as query results, the WHERE clause acts as a filter. The boolean expression in the WHERE clause, also known as the predicate, determines which objects to accept. Only Country objects for which the predicate evaluates to TRUE are passed to the SELECT clause and collected as query results.

WHERE predicate and indexes

Formally, the WHERE clause functions as a filter between the FROM and SELECT clauses. In practice, if a suitable index is available, the database performs the filtering during the FROM iteration. In the previous population query, if an index is defined on the population field, ObjectDB can use that index to iterate directly over the Country objects that satisfy the WHERE predicate. For entity classes with millions of objects in the database, defining appropriate indexes can significantly improve query execution time.

WHERE filter in multi-variable queries

In a multi-variable query, the FROM clause defines an iteration over tuples. In this case, the WHERE clause filters these tuples before passing them to the SELECT clause.

For example, the following query retrieves all countries that have a population greater than a specified limit and an official language from a specified set of languages:

SELECT c, l FROM Country c JOIN c.languages l
WHERE c.population > :p AND l in :languages

The FROM clause of this query defines an iteration over (country, language) pairs. Only pairs that satisfy the WHERE clause are passed to the SELECT clause.

In multi-variable queries, the number of tuples for iteration can be very large even if the database is small, which makes indexes even more essential.

JPQL expressions in WHERE

The previous queries demonstrate only a small part of the WHERE clause's capabilities.
Its real power comes from the rich JPQL expression syntax,
which includes many operators (arithmetic, relational, and logical) and functions (numeric, string, and collection). The WHERE predicate is always a boolean JPQL expression. JPQL expressions are also used in other JPQL query clauses, but they are most prominent in the WHERE clause.

WHERE in criteria queries

The CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries. interface provides two where methods for setting the WHERE clause.

Single restriction

The first wherejakarta.persistence.criteria.CriteriaQuery.where(Expression)Modify the query to restrict the query result according to the specified boolean expression. method takes one Expressionjakarta.persistence.criteria.ExpressionType for query expressions.<Boolean> argument and uses it as the WHERE clause predicate, overriding any previously set predicate.

For example, the following JPQL query:

SELECT c FROM Country c WHERE c.population > :p

can be built using the criteria query API as follows:

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe  CriteriaQuery  interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a   CriteriaQuery<T>   object with the given result type.(Country.class);
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(c);
  ParameterExpressionjakarta.persistence.criteria.ParameterExpressionType of criteria query parameter expressions.<Integer> p = cb.parameterjakarta.persistence.criteria.CriteriaBuilder.parameter(Class)Create a parameter expression.(Integer.class);
  q.wherejakarta.persistence.criteria.CriteriaQuery.where(Expression)Modify the query to restrict the query result according to the specified boolean expression.(cb.gtjakarta.persistence.criteria.CriteriaBuilder.gt(Expression,Expression)Create a predicate for testing whether the first argument is greater than the second.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("population"), p));

Multiple restrictions

The second where method takes a variable number of Predicatejakarta.persistence.criteria.PredicateThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. arguments and combines them using a logical AND, overriding any previously set predicate:

For example, the following JPQL query:

SELECT c FROM Country WHERE c.population > :p AND c.area < :a

can be built as a criteria query as follows:

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe  CriteriaQuery  interface defines functionality that is specific to top-level queries. q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a   CriteriaQuery<T>   object with the given result type.(Country.class);
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(c);
  ParameterExpressionjakarta.persistence.criteria.ParameterExpressionType of criteria query parameter expressions.<Integer> p = cb.parameterjakarta.persistence.criteria.CriteriaBuilder.parameter(Class)Create a parameter expression.(Integer.class);
  ParameterExpressionjakarta.persistence.criteria.ParameterExpressionType of criteria query parameter expressions.<Integer> a = cb.parameterjakarta.persistence.criteria.CriteriaBuilder.parameter(Class)Create a parameter expression.(Integer.class);
  q.wherejakarta.persistence.criteria.CriteriaQuery.where(Predicate...)Modify the query to restrict the query result according to the conjunction of the specified restriction predicates.(
      cb.gtjakarta.persistence.criteria.CriteriaBuilder.gt(Expression,Expression)Create a predicate for testing whether the first argument is greater than the second.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("population"), p),
      cb.ltjakarta.persistence.criteria.CriteriaBuilder.lt(Expression,Expression)Create a predicate for testing whether the first argument is less than the second.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("area"), a)
  );

The where setting above is equivalent to explicitly building an AND conjunction, as follows:

  q.wherejakarta.persistence.criteria.CriteriaQuery.where(Expression)Modify the query to restrict the query result according to the specified boolean expression.(
      cb.andjakarta.persistence.criteria.CriteriaBuilder.and(Expression,Expression)Create a conjunction of the given boolean expressions.(
          cb.gtjakarta.persistence.criteria.CriteriaBuilder.gt(Expression,Expression)Create a predicate for testing whether the first argument is greater than the second.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("population"), p),
          cb.ltjakarta.persistence.criteria.CriteriaBuilder.lt(Expression,Expression)Create a predicate for testing whether the first argument is less than the second.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("area"), a)
      )
  );

The variable-argument form of the where method always uses AND. Therefore, to use OR, you must build an OR expression explicitly:

  q.wherejakarta.persistence.criteria.CriteriaQuery.where(Expression)Modify the query to restrict the query result according to the specified boolean expression.(
      cb.orjakarta.persistence.criteria.CriteriaBuilder.or(Expression,Expression)Create a disjunction of the given boolean expressions.(
          cb.gtjakarta.persistence.criteria.CriteriaBuilder.gt(Expression,Expression)Create a predicate for testing whether the first argument is greater than the second.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("population"), p),
          cb.ltjakarta.persistence.criteria.CriteriaBuilder.lt(Expression,Expression)Create a predicate for testing whether the first argument is less than the second.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("area"), a)
      )
  );

See the Logical Operators page for more information about the boolean expressions and predicates that you can use in a criteria query's WHERE clause.