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.

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.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific to top-level queries.See JavaDoc Reference Page... interface provides two where methods for setting the WHERE clause.

Single Restriction

The first wherewhere(restriction)CriteriaQuery's methodModify the query to restrict the query result according to the specified boolean expression.See JavaDoc Reference Page... method takes one Expressionjavax.persistence.criteria.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page...<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.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Country> q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
  q.selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page...(c);
  ParameterExpressionjavax.persistence.criteria.ParameterExpressionJPA interfaceType of criteria query parameter expressions.See JavaDoc Reference Page...<Integer> p = cb.parameterparameter(paramClass)CriteriaBuilder's methodCreate a parameter expression.See JavaDoc Reference Page...(Integer.class);
  q.wherewhere(restriction)CriteriaQuery's methodModify the query to restrict the query result according
 to the specified boolean expression.See JavaDoc Reference Page...(cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("population"), p));

Multiple Restrictions

The second where method takes a variable number of arguments of Predicatejavax.persistence.criteria.PredicateJPA interfaceThe type of a simple or compound predicate: a conjunction or disjunction of restrictions.See JavaDoc Reference Page... 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.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page... q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Country.class);
  Rootjavax.persistence.criteria.RootJPA interfaceA root type in the from clause.See JavaDoc Reference Page...<Country> c = q.fromfrom(entityClass)AbstractQuery's methodCreate and add a query root corresponding to the given entity,
 forming a cartesian product with any existing roots.See JavaDoc Reference Page...(Country.class);
  q.selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page...(c);
  ParameterExpressionjavax.persistence.criteria.ParameterExpressionJPA interfaceType of criteria query parameter expressions.See JavaDoc Reference Page...<Integer> p = cb.parameterparameter(paramClass)CriteriaBuilder's methodCreate a parameter expression.See JavaDoc Reference Page...(Integer.class);
  ParameterExpressionjavax.persistence.criteria.ParameterExpressionJPA interfaceType of criteria query parameter expressions.See JavaDoc Reference Page...<Integer> a = cb.parameterparameter(paramClass)CriteriaBuilder's methodCreate a parameter expression.See JavaDoc Reference Page...(Integer.class);
  q.wherewhere(restrictions)CriteriaQuery's methodModify the query to restrict the query result according 
 to the conjunction of the specified restriction predicates.See JavaDoc Reference Page...(
      cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("population"), p),
      cb.ltlt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than the second.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("area"), a)
  );

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

  q.wherewhere(restriction)CriteriaQuery's methodModify the query to restrict the query result according
 to the specified boolean expression.See JavaDoc Reference Page...(
      cb.andand(x, y)CriteriaBuilder's methodCreate a conjunction of the given boolean expressions.See JavaDoc Reference Page...(
          cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("population"), p),
          cb.ltlt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than the second.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("area"), a)
      )
  );

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

  q.wherewhere(restriction)CriteriaQuery's methodModify the query to restrict the query result according
 to the specified boolean expression.See JavaDoc Reference Page...(
      cb.oror(x, y)CriteriaBuilder's methodCreate a disjunction of the given boolean expressions.See JavaDoc Reference Page...(
          cb.gtgt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 greater than the second.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("population"), p),
          cb.ltlt(x, y)CriteriaBuilder's methodCreate a predicate for testing whether the first argument is 
 less than the second.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("area"), a)
      )
  );

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