JPA Query Structure (JPQL / Criteria)

The syntax of the Java Persistence Query Language (JPQL) is very similar to the syntax of SQL. Having an SQL-like syntax in JPA queries is an important advantage because SQL is a very powerful query language and many developers are already familiar with it.

The main difference between SQL and JPQL is that SQL works with relational database tables, records and fields, whereas JPQL works with Java classes and objects. For example, a JPQL query can retrieve and return entity objects rather than just field values from database tables, as with SQL. That makes JPQL more object oriented friendly and easier to use in Java.

JPQL Query Structure

As with SQL, a JPQL SELECT query also consists of up to 6 clauses in the following format:

SELECT ... FROM ...
[WHERE ...]
[GROUP BY ... [HAVING ...]]
[ORDER BY ...]

The first two clauses, SELECT and FROM are required in every retrieval query (update and delete queries have a slightly different form). The other JPQL clauses, WHERE, GROUP BY, HAVING and ORDER BY are optional.

The structure of JPQL DELETE and UPDATE queries is simpler:

DELETE FROM ... [WHERE ...]

UPDATE ... SET ... [WHERE ...]

Besides a few exceptions, JPQL is case insensitive. JPQL keywords, for example, can appear in queries either in upper case (e.g. SELECT) or in lower case (e.g. select). The few exceptions in which JPQL is case sensitive include mainly Java source elements such as names of entity classes and persistent fields, which are case sensitive. In addition, string literals are also case sensitive (e.g. "ORM" and "orm" are different values).

A Minimal JPQL Query

The following query retrieves all the Country objects in the database:

SELECT c FROM Country AS c

Because SELECT and FROM are mandatory, this demonstrates a minimal JPQL query.

The FROM clause declares one or more query variables (also known as identification variables). Query variables are similar to loop variables in programming languages. Each query variable represents iteration over objects in the database. A query variable that is bound to an entity class is referred to as a range variable. Range variables define iteration over all the database objects of a binding entity class and its descendant classes. In the query above, c is a range variable that is bound to the Country entity class and defines iteration over all the Country objects in the database.

The SELECT clause defines the query results. The query above simply returns all the Country objects from the iteration of the c range variable, which in this case is actually all the Country objects in the database.

Organization of this Section

This section contains the following pages:

• SELECT clause (JPQL / Criteria API)
• FROM clause (JPQL / Criteria API)
• WHERE clause (JPQL / Criteria API)
• GROUP BY and HAVING clauses
• ORDER BY clause (JPQL / Criteria API)
• DELETE Queries in JPA/JPQL
• UPDATE SET Queries in JPA/JPQL

Detailed explanations on how to set criteria query clauses are provided as follows:

• Criteria SELECT (selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page..., distinctdistinct(distinct)CriteriaQuery's methodSpecify whether duplicate query results will be eliminated.See JavaDoc Reference Page..., multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the query result.See JavaDoc Reference Page..., arrayarray(selections)CriteriaBuilder's methodCreate an array-valued selection item.See JavaDoc Reference Page..., tupletuple(selections)CriteriaBuilder's methodCreate a tuple-valued selection item.See JavaDoc Reference Page..., constructconstruct(resultClass, selections)CriteriaBuilder's methodCreate a selection item corresponding to a constructor.See JavaDoc Reference Page...).
• Criteria FROM (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..., joinjoin(attributeName, jt)From's methodCreate a join to the specified attribute using the given join type.See JavaDoc Reference Page..., fetchfetch(attributeName)FetchParent's methodCreate a fetch join to the specified attribute using an inner join.See JavaDoc Reference Page...).
• Criteria WHERE (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...).
• Criteria GROUP BY / HAVING (groupBygroupBy(grouping)CriteriaQuery's methodSpecify the expressions that are used to form groups over the query results.See JavaDoc Reference Page..., havinghaving(restriction)CriteriaQuery's methodSpecify a restriction over the groups of the query.See JavaDoc Reference Page..., countcount(x)CriteriaBuilder's methodCreate an aggregate expression applying the count operation.See JavaDoc Reference Page..., sumsum(x)CriteriaBuilder's methodCreate an aggregate expression applying the sum operation.See JavaDoc Reference Page..., avgavg(x)CriteriaBuilder's methodCreate an aggregate expression applying the avg operation.See JavaDoc Reference Page..., minmin(x)CriteriaBuilder's methodCreate an aggregate expression applying the numerical min operation.See JavaDoc Reference Page..., maxmax(x)CriteriaBuilder's methodCreate an aggregate expression applying the numerical max operation.See JavaDoc Reference Page..., ...).
• Criteria ORDER BY (orderByorderBy(o)CriteriaQuery's methodSpecify the ordering expressions that are used to order the query results.See JavaDoc Reference Page..., Orderjavax.persistence.criteria.OrderJPA interfaceAn object that defines an ordering over the query results.See JavaDoc Reference Page..., ascasc(x)CriteriaBuilder's methodCreate an ordering by the ascending value of the expression.See JavaDoc Reference Page..., descdesc(x)CriteriaBuilder's methodCreate an ordering by the descending value of the expression.See JavaDoc Reference Page...).