WHERE clause (JPQL / Criteria API)

The WHERE clause adds filtering capabilities to the FROM-SELECT structure. It is essential in any JPQL query that retrieves selective objects from the database. Out of the four optional clauses of JPQL queries, the WHERE clause is definitely the most frequently used.

This page covers the following topics:

How a WHERE Clause Works

The following query retrieves only countries with a population size above a population size p:

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

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

WHERE Predicate and Indexes

Formally, the WHERE clause functions as a filter between the FROM and the SELECT clauses. Practically, if a proper index is available, filtering is done earlier during FROM iteration. In the above population query, if an index is defined on the population field ObjectDB can use that index to iterate directly on Country objects that satisfy the WHERE predicate. For entity classes with millions of objects in the database there is a huge difference in query execution time if proper indexes are defined.

WHERE Filter in Multi Variable Queries

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

For example, the following query retrieves all the countries with population size that exceeds a specified limit and also have 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 iteration over (country, language) pairs. Only pairs that satisfy the WHERE clause are passed through to the SELECT.

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

JPQL Expressions in WHERE

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

WHERE in Criteria Queries

The CriteriaQueryjavax.persistence.criteria.CriteriaQuery - JPA InterfaceThe 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 whereCriteriaQuery.where(restriction) - JPA MethodModify the query to restrict the query result according to the specified boolean expression. method takes one Expressionjavax.persistence.criteria.Expression - JPA InterfaceType for query expressions.<Boolean> argument and uses it as the WHERE clause content (overriding previously set WHERE content if any).

For example, the following JPQL query:

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

can be built by using the criteria query API as follows:

  CriteriaQueryjavax.persistence.criteria.CriteriaQuery - JPA InterfaceThe CriteriaQuery interface defines functionality that is specific
 to top-level queries.<Country> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA MethodCreate a CriteriaQuery object with the specified result
  type.(Country.class);
  Rootjavax.persistence.criteria.Root - JPA InterfaceA root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA MethodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.(Country.class);
  q.selectCriteriaQuery.select(selection) - JPA MethodSpecify the item that is to be returned in the query result.(c);
  ParameterExpressionjavax.persistence.criteria.ParameterExpression - JPA InterfaceType of criteria query parameter expressions.<Integer> p = cb.parameterCriteriaBuilder.parameter(paramClass) - JPA MethodCreate a parameter expression.(Integer.class);
  q.whereCriteriaQuery.where(restriction) - JPA MethodModify the query to restrict the query result according
 to the specified boolean expression.(cb.gtCriteriaBuilder.gt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("population"), p));

Multiple Restrictions

The second where method takes a variable number of arguments of Predicatejavax.persistence.criteria.Predicate - JPA InterfaceThe type of a simple or compound predicate: a conjunction or disjunction of restrictions. type and uses an AND conjunction as the WHERE clause content (overriding previously set WHERE content if any):

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:

  CriteriaQueryjavax.persistence.criteria.CriteriaQuery - JPA InterfaceThe CriteriaQuery interface defines functionality that is specific
 to top-level queries. q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA MethodCreate a CriteriaQuery object with the specified result
  type.(Country.class);
  Rootjavax.persistence.criteria.Root - JPA InterfaceA root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA MethodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.(Country.class);
  q.selectCriteriaQuery.select(selection) - JPA MethodSpecify the item that is to be returned in the query result.(c);
  ParameterExpressionjavax.persistence.criteria.ParameterExpression - JPA InterfaceType of criteria query parameter expressions.<Integer> p = cb.parameterCriteriaBuilder.parameter(paramClass) - JPA MethodCreate a parameter expression.(Integer.class);
  ParameterExpressionjavax.persistence.criteria.ParameterExpression - JPA InterfaceType of criteria query parameter expressions.<Integer> a = cb.parameterCriteriaBuilder.parameter(paramClass) - JPA MethodCreate a parameter expression.(Integer.class);
  q.whereCriteriaQuery.where(restrictions) - JPA MethodModify the query to restrict the query result according
 to the conjunction of the specified restriction predicates.(
      cb.gtCriteriaBuilder.gt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("population"), p),
      cb.ltCriteriaBuilder.lt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than the second.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("area"), a)
  );

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

  q.whereCriteriaQuery.where(restriction) - JPA MethodModify the query to restrict the query result according
 to the specified boolean expression.(
      cb.andCriteriaBuilder.and(x,y) - JPA MethodCreate a conjunction of the given boolean expressions.(
          cb.gtCriteriaBuilder.gt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("population"), p),
          cb.ltCriteriaBuilder.lt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than the second.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("area"), a)
      )
  );

The variable argument form of the where method always uses AND. Therefore, using OR requires building an OR expression explicitly:

  q.whereCriteriaQuery.where(restriction) - JPA MethodModify the query to restrict the query result according
 to the specified boolean expression.(
      cb.orCriteriaBuilder.or(x,y) - JPA MethodCreate a disjunction of the given boolean expressions.(
          cb.gtCriteriaBuilder.gt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 greater than the second.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("population"), p),
          cb.ltCriteriaBuilder.lt(x,y) - JPA MethodCreate a predicate for testing whether the first argument is
 less than the second.(c.getPath.get(attributeName) - JPA MethodCreate a path corresponding to the referenced attribute.("area"), a)
      )
  );

See the Logical Operators page for explanations on boolean expressions and predicates that can be used in a criteria query WHERE clause.

< JPQL FROM ^ Query Structure JPQL GROUP BY >