SELECT clause (JPQL / Criteria API)
The ability to retrieve managed entity objects is a major advantage of JPQL. For example, the following query returns Country
objects that become managed by the EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. em
:
TypedQueryjavax.persistence.TypedQuery- JPA Interface Interface used to control the execution of typed queries.<Country> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance ofTypedQuery
for executing a Java Persistence query language statement.("SELECT c FROM Country c", Country.class); List<Country> results = query.getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List.();
Because the results are managed entity objects they have all the support that JPA provides for managed entity objects, including transparent navigation to other database objects, transparent update detection, support for delete, etc.
Query results are not limited to entity objects. JPA 2 adds the ability to use almost any valid JPQL expression in SELECT clauses. Specifying the required query results more precisely can improve performance and in some cases can also reduce the amount of Java code needed. Notice that query results must always be specified explicitly - JPQL does not support the "SELECT *" expression (which is commonly used in SQL).
This page covers the following topics:
Projection of Path Expressions
JPQL queries can also return results that are not entity objects. For example, the following query returns country names as String
instances, rather than Country
objects:
SELECT c.name FROM Country AS c
Using path expressions, such as c.name
, in query results is referred to as projection. The field values are extracted from (or projected out of) entity objects to form the query results.
The results of the above query are received as a list of String
values:
TypedQueryjavax.persistence.TypedQuery- JPA Interface Interface used to control the execution of typed queries.<String> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance ofTypedQuery
for executing a Java Persistence query language statement.( "SELECT c.name FROM Country AS c", String.class); List<String> results = query.getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List.();
Only singular value path expressions can be used in the SELECT clause. Collection and map fields cannot be included in the results directly, but their content can be added to the SELECT clause by using a bound JOIN variable in the FROM clause.
Nested path expressions are also supported. For example, the following query retrieves the name of the capital city of a specified country:
SELECT c.capital.name FROM Country AS c WHERE c.name = :name
Because construction of managed entity objects has some overhead, queries that return non entity objects, as the two queries above, are usually more efficient. Such queries are useful mainly for displaying information efficiently. They are less productive with operations that update or delete entity objects, in which managed entity objects are needed.
Managed entity objects can, however, be returned from a query that uses projection when a result path expression resolves to an entity. For example, the following query returns a managed City
entity object:
SELECT c.capital FROM Country AS c WHERE c.name = :name
Result expressions that represent anything but entity objects (e.g. values of system types and user defined embeddable objects) return as results value copies that are not associated with the containing entities. Therefore, embedded objects that are retrieved directly by a result path expression are not associated with an EntityManagerjavax.persistence.EntityManager - JPA InterfaceInterface used to interact with the persistence context. and changes to them when a transaction is active are not propagated to the database.
Multiple SELECT Expressions
The SELECT clause may also define composite results:
SELECT c.name, c.capital.name FROM Country AS c
The result list of this query contains Object[]
elements, one per result. The length of each result Object[]
element is 2. The first array cell contains the country name (c.name
) and the second array cell contains the capital city name (c.capital.name
).
The following code demonstrates running this query and processing the results:
TypedQueryjavax.persistence.TypedQuery- JPA Interface Interface used to control the execution of typed queries.<Object[]> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance ofTypedQuery
for executing a Java Persistence query language statement.( "SELECT c.name, c.capital.name FROM Country AS c", Object[].class); List<Object[]> results = query.getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List.(); for (Object[] result : results) { System.out.println( "Country: " + result[0] + ", Capital: " + result[1]); }
As an alternative to representing compound results by Object
arrays, JPA supports using custom result classes and result constructor expressions.
Result Classes (Constructor Expressions)
JPA supports wrapping JPQL query results with instances of custom result classes. This is mainly useful for queries with multiple SELECT expressions, where custom result objects can provide an object oriented alternative to representing results as Object[]
elements.
The fully qualified name of the result class is specified in a NEW expression, as follows:
SELECT NEW example.CountryAndCapital(c.name, c.capital.name) FROM Country AS c
This query is identical to the previous query above except that now the result list contains CountryAndCapital
instances rather than Object[]
elements.
The result class must have a compatible constructor that matches the SELECT result expressions, as follows:
package example; public class CountryAndCapital { public String countryName; public String capitalName; public CountryAndCapital(String countryName, String capitalName) { this.countryName = countryName; this.capitalName = capitalName; } }
The following code demonstrates running this query:
String queryStr = "SELECT NEW example.CountryAndCapital(c.name, c.capital.name) " + "FROM Country AS c"; TypedQueryjavax.persistence.TypedQuery- JPA Interface Interface used to control the execution of typed queries.<CountryAndCapital> query = em.createQueryEntityManager.createQuery(qlString,resultClass) - JPA MethodCreate an instance ofTypedQuery
for executing a Java Persistence query language statement.(queryStr, CountryAndCapital.class); List<CountryAndCapital> results = query.getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results as a typed List.();
Any class with a compatible constructor can be used as a result class. It could be a JPA managed class (e.g. an entity class) but it could also be a lightweight 'transfer' class that is only used for collecting and processing query results.
If an entity class is used as a result class, the result entity objects are created in the NEW state, which means that they are not managed. Such entity objects are missing the JPA functionality of managed entity objects (e.g. transparent navigation and transparent update detection), but they are more lightweight, they are built faster and they consume less memory.
SELECT DISTINCT
Queries that use projection may return duplicate results. For example, the following query may return the same currency more than once:
SELECT c.currency FROM Country AS c WHERE c.name LIKE 'I%'
Both Italy and Ireland (whose name starts with 'I') use Euro as their currency. Therefore, the query result list contains "Euro"
more than once.
Duplicate results can be eliminated easily in JPQL by using the DISTINCT keyword:
SELECT DISTINCT c.currency FROM Country AS c WHERE c.name LIKE 'I%'
The only difference between SELECT and SELECT DISTINCT is that the later filters duplicate results. Filtering duplicate results might have some effect on performance, depending on the size of the query result list and other factors.
SELECT in Criteria Queries
The criteria query API provides several ways for setting the SELECT clause.
Single Selection
Setting a single expression SELECT clause is straightforward.
For example, the following JPQL query:
SELECT DISTINCT c.currency FROM Country c
can be built as a criteria query as follows:
CriteriaQueryjavax.persistence.criteria.CriteriaQuery- JPA Interface TheCriteriaQuery
interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA MethodCreate aCriteriaQuery
object with the specified result type.(Country.class); Rootjavax.persistence.criteria.Root- JPA Interface A root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA Method Create 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.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("currency")).distinctCriteriaQuery.distinct(distinct) - JPA MethodSpecify whether duplicate query results will be eliminated.(true);
The select
CriteriaQuery.select(selection) - JPA MethodSpecify the item that is to be returned in the query result. method takes one argument of type Selectionjavax.persistence.criteria.Selection
and sets it as the SELECT clause content (overriding previously set SELECT content if any). Every valid criteria API expression can be used as selection, because all the criteria API expressions are represented by a sub interface of Selection
interface defines an item that is to be
returned in a query result.Selection -
Expression
javax.persistence.criteria.Expression
The distinctCriteriaQuery.distinct(distinct) - JPA MethodSpecify whether duplicate query results will be eliminated. method can be used to eliminate duplicate results as demonstrated in the above code (using method chaining).
Multi Selection
The Selection
javax.persistence.criteria.SelectionSelection
interface defines an item that is to be returned in a query result. interface is also a super interface of CompoundSelectionjavax.persistence.criteria.CompoundSelection
, which represents multi selection (which is not a valid expression on its own and can be used only in the SELECT clause). CompoundSelection
interface defines a compound selection item
(tuple, array, or result of constructor).
The CriteriaBuilder
javax.persistence.criteria.CriteriaBuilder - JPA InterfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides three factory methods for building CompoundSelection
instances - array
CriteriaBuilder.array(selections) - JPA MethodCreate an array-valued selection item., tuple
CriteriaBuilder.tuple(selections) - JPA MethodCreate a tuple-valued selection item. and construct
CriteriaBuilder.construct(resultClass,selections) - JPA MethodCreate a selection item corresponding to a constructor..
CriteriaBuilder's array
The following JPQL query:
SELECT c.name, c.capital.name FROM Country c
can be defined using the criteria API as follows:
CriteriaQueryjavax.persistence.criteria.CriteriaQuery- JPA Interface TheCriteriaQuery
interface defines functionality that is specific to top-level queries.<Object[]> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA MethodCreate aCriteriaQuery
object with the specified result type.(Object[].class); Rootjavax.persistence.criteria.Root- JPA Interface A root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA Method Create 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.(cb.arrayCriteriaBuilder.array(selections) - JPA MethodCreate an array-valued selection item.(c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name"), c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("capital").getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name")));
The array
CriteriaBuilder.array(selections) - JPA MethodCreate an array-valued selection item. method builds a CompoundSelection
javax.persistence.criteria.CompoundSelectionCompoundSelection
interface defines a compound selection item (tuple, array, or result of constructor). instance, which represents results as arrays.
The following code demonstrates the execution of the query and iteration over the results:
List<Object[]> results = em.createQueryEntityManager.createQuery(criteriaQuery) - JPA MethodCreate an instance of TypedQuery
for executing a
criteria query.(q).getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results
as a typed List.();
for (Object[] result : results) {
System.out.println(
"Country: " + result[0] + ", Capital: " + result[1]);
}
CriteriaBuilder's tuple
The Tuple
javax.persistence.Tuple - JPA InterfaceInterface for extracting the elements of a query result tuple. interface can be used as a clean alternative to Object[]
:
CriteriaQueryjavax.persistence.criteria.CriteriaQuery- JPA Interface TheCriteriaQuery
interface defines functionality that is specific to top-level queries.<Tuplejavax.persistence.Tuple - JPA InterfaceInterface for extracting the elements of a query result tuple.> q = cb.createTupleQueryCriteriaBuilder.createTupleQuery() - JPA MethodCreate aCriteriaQuery
object that returns a tuple of objects as its result.(); Rootjavax.persistence.criteria.Root- JPA Interface A root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA Method Create 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.(cb.tCriteriaBuilder.array(selections) - JPA MethodCreate an array-valued selection item.upleCriteriaBuilder.tuple(selections) - JPA MethodCreate a tuple-valued selection item.(c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name"), c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("capital").getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name")));
The tuple
CriteriaBuilder.tuple(selections) - JPA MethodCreate a tuple-valued selection item. method builds a CompoundSelection
javax.persistence.criteria.CompoundSelectionCompoundSelection
interface defines a compound selection item (tuple, array, or result of constructor). instance, which represents Tuple
javax.persistence.Tuple - JPA InterfaceInterface for extracting the elements of a query result tuple. results.
The following code demonstrates the execution of the query and iteration over the results:
List<Tuplejavax.persistence.Tuple - JPA InterfaceInterface for extracting the elements of a query result tuple.> results = em.createQueryEntityManager.createQuery(criteriaQuery) - JPA MethodCreate an instance of TypedQuery
for executing a
criteria query.(q).getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results
as a typed List.();
for (Tuple t : results) {
System.out.println("Country: " + t.get(0) + ", Capital: " + t.get(1));
}
The Tuple
javax.persistence.Tuple - JPA InterfaceInterface for extracting the elements of a query result tuple. interface defines several other methods for accessing the result data.
CriteriaBuilder's construct
JPQL user defined result objects are also supported by the JPA criteria query API:
CriteriaQueryjavax.persistence.criteria.CriteriaQuery- JPA Interface TheCriteriaQuery
interface defines functionality that is specific to top-level queries.<CountryAndCapital> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA MethodCreate aCriteriaQuery
object with the specified result type.(CountryAndCapital.class); Rootjavax.persistence.criteria.Root- JPA Interface A root type in the from clause.<Country> c = q.fromAbstractQuery.from(entityClass) - JPA Method Create 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.(cb.constructCriteriaBuilder.construct(resultClass,selections) - JPA MethodCreate a selection item corresponding to a constructor.(CountryAndCapital.class, c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name"), c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("capital").getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name")));
The construct
CriteriaBuilder.construct(resultClass,selections) - JPA MethodCreate a selection item corresponding to a constructor. method builds a CompoundSelection
javax.persistence.criteria.CompoundSelectionCompoundSelection
interface defines a compound selection item (tuple, array, or result of constructor). instance, which represents results as instances of a user defined class (CountryAndCapital
in the above example).
The following code demonstrates the execution of the query:
List<CountryAndCapital> results = em.createQueryEntityManager.createQuery(criteriaQuery) - JPA MethodCreate an instance of TypedQuery
for executing a
criteria query.(q).getResultListTypedQuery.getResultList() - JPA MethodExecute a SELECT query and return the query results
as a typed List.();
As expected - the result objects are CountryAndCapital
instances.
CriteriaQuery's multiselect
In the above examples, CompoundSelection
javax.persistence.criteria.CompoundSelectionCompoundSelection
interface defines a compound selection item (tuple, array, or result of constructor). instances were first built by a CriteriaBuilder
factory method and then passed to the CriteriaQuery
's select
CriteriaQuery.select(selection) - JPA MethodSpecify the item that is to be returned in the query result. method.
The CriteriaQuery
interface provides a shortcut method - multiselect
CriteriaQuery.multiselect(selections) - JPA MethodSpecify the selection items that are to be returned in the query result., which takes a variable number of arguments representing multiple selections, and builds a CompoundSelection
instance based on the expected query results.
For example, the following invocation of multiselect
CriteriaQuery.multiselect(selections) - JPA MethodSpecify the selection items that are to be returned in the query result.:
q.multiselectCriteriaQuery.multiselect(selections) - JPA MethodSpecify the selection items that are to be returned in the query result.(c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name"), c.getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("capital").getPath.get(attributeName) - JPA Method Create a path corresponding to the referenced attribute.("name"));
is equivalent to using select
CriteriaQuery.select(selection) - JPA MethodSpecify the item that is to be returned in the query result. with one of the factory methods (array
CriteriaBuilder.array(selections) - JPA MethodCreate an array-valued selection item., tuple
CriteriaBuilder.tuple(selections) - JPA MethodCreate a tuple-valued selection item. or construct
CriteriaBuilder.construct(resultClass,selections) - JPA MethodCreate a selection item corresponding to a constructor.) as demonstrated above.
The behavior of the multiselect
CriteriaQuery.multiselect(selections) - JPA MethodSpecify the selection items that are to be returned in the query result. method depends on the query result type (as set when CriteriaQuery
is instantiated):
- For expected
Object
andObject[]
result type -array
CriteriaBuilder.array(selections) - JPA MethodCreate an array-valued selection item. is used. - For expected
Tuple
result -tuple
CriteriaBuilder.tuple(selections) - JPA MethodCreate a tuple-valued selection item. is used. - For any other expected result type -
construct
CriteriaBuilder.construct(resultClass,selections) - JPA MethodCreate a selection item corresponding to a constructor. is used.