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 ...][GROUPBY ... [HAVING ...]][ORDERBY ...]
The first two clauses,SELECT andFROM are required in every retrieval query (update and delete queries have a slightly different form). The other JPQL clauses,WHERE,GROUP BY,HAVING andORDER BY are optional.
The structure of JPQLDELETE andUPDATE queries is simpler:
DELETEFROM ... [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 theCountry objects in the database:
SELECTcFROMCountryASc
BecauseSELECT andFROM are mandatory, this demonstrates a minimal JPQL query.
TheFROM 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 theCountry entity class and defines iteration over all theCountry objects in the database.
TheSELECT clause defines the query results. The query above simply returns all theCountry objects from the iteration of the c range variable, which in this case is actually all theCountry objects in the database.
Organization of this Section
This section contains the following pages:
Detailed explanations on how to set criteria query clauses are provided as follows:
- Criteria SELECT (selectCriteriaQuery.select(selection) - JPA Method Specify the item that is to be returned in the query result.,distinctCriteriaQuery.distinct(distinct) - JPA Method Specify whether duplicate query results are eliminated.,multiselectCriteriaQuery.multiselect(selections) - JPA Method Specify the selection items that are to be returned in the query result.,arrayCriteriaBuilder.array(selections) - JPA Method Create an array-valued selection item.,tupleCriteriaBuilder.tuple(selections) - JPA Method Create a tuple-valued selection item.,constructCriteriaBuilder.construct(resultClass,selections) - JPA Method Create a selection item corresponding to a constructor.).
- Criteria FROM (fromAbstractQuery
.from(entityClass) - JPA Method Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.,joinFrom - Criteria WHERE (whereCriteriaQuery.where(restrictions) - JPA Method Modify the query to restrict the query result according to the conjunction of the specified restriction predicates.).
- Criteria GROUP BY / HAVING (groupByCriteriaQuery.groupBy(grouping) - JPA Method Specify the expressions that are used to form groups over the query results.,havingCriteriaQuery.having(restriction) - JPA Method Specify a restriction over the groups of the query.,countCriteriaBuilder.count(x) - JPA Method Create an aggregate expression applying the count operation.,sumCriteriaBuilder.sum(x) - JPA Method Create an aggregate expression applying the sum operation.,avgCriteriaBuilder.avg(x) - JPA Method Create an aggregate expression applying the avg operation.,minCriteriaBuilder.min(x) - JPA Method Create an aggregate expression applying the numerical min operation.,maxCriteriaBuilder.max(x) - JPA Method Create an aggregate expression applying the numerical max operation., ...).
- Criteria ORDER BY (orderByCriteriaQuery.orderBy(o) - JPA Method Specify the ordering expressions that are used to order the query results.,Orderjakarta.persistence.criteria.Order - JPA Interface An object that defines an ordering over the query results.,ascCriteriaBuilder.asc(expression) - JPA Method Create an ordering by the ascending value of the expression.,descCriteriaBuilder.desc(expression) - JPA Method Create an ordering by the descending value of the expression.).