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 Interface Interface 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 Method
 Create an instance of TypedQuery for executing a
 Java Persistence query language statement.("SELECT c FROM Country c", Country.class);
  List<Country> results = query.getResultListTypedQuery.getResultList() - JPA Method
 Execute 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 Multiple SELECT Expressions Result Classes (Constructor Expressions)SELECT DISTINCT SELECT in Criteria Queries

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 Method
 Create an instance of TypedQuery for executing a
 Java Persistence query language statement.(
      "SELECT c.name FROM Country AS c", String.class);
  List<String> results = query.getResultListTypedQuery.getResultList() - JPA Method
 Execute 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 Interface Interface 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 Method
 Create an instance of TypedQuery 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 Method
 Execute 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 Method
 Create an instance of TypedQuery for executing a
 Java Persistence query language statement.(queryStr, CountryAndCapital.class);
  List<CountryAndCapital> results = query.getResultListTypedQuery.getResultList() - JPA Method
 Execute 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
 The CriteriaQuery interface defines functionality that is specific
 to top-level queries.<Country> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA Method
  Create a CriteriaQuery 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 Method
 Specify 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 Method
 Specify whether duplicate query results will be eliminated.(true);

The selectCriteriaQuery.select(selection) - JPA Method Specify the item that is to be returned in the query result. method takes one argument of type Selectionjavax.persistence.criteria.Selection - JPA Interface The Selection interface defines an item that is to be returned in a query result. 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 - Expressionjavax.persistence.criteria.Expression - JPA Interface Type for query expressions. (and its descendant interfaces).

The distinctCriteriaQuery.distinct(distinct) - JPA Method Specify 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 Selectionjavax.persistence.criteria.Selection - JPA Interface The Selection interface defines an item that is to be returned in a query result. interface is also a super interface of CompoundSelectionjavax.persistence.criteria.CompoundSelection - JPA Interface The CompoundSelection interface defines a compound selection item (tuple, array, or result of constructor)., which represents multi selection (which is not a valid expression on its own and can be used only in the SELECT clause).

The CriteriaBuilderjavax.persistence.criteria.CriteriaBuilder - JPA Interface Used to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides three factory methods for building CompoundSelection instances - arrayCriteriaBuilder.array(selections) - JPA Method Create an array-valued selection item., tupleCriteriaBuilder.tuple(selections) - JPA Method Create a tuple-valued selection item. and constructCriteriaBuilder.construct(resultClass,selections) - JPA Method Create 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
 The CriteriaQuery interface defines functionality that is specific
 to top-level queries.<Object[]> q = cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA Method
  Create a CriteriaQuery 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 Method
 Specify the item that is to be returned in the query result.(cb.arrayCriteriaBuilder.array(selections) - JPA Method
 Create 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 arrayCriteriaBuilder.array(selections) - JPA Method Create an array-valued selection item. method builds a CompoundSelectionjavax.persistence.criteria.CompoundSelection - JPA Interface The CompoundSelection 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 Method
 Create an instance of TypedQuery for executing a
 criteria query.(q).getResultListTypedQuery.getResultList() - JPA Method
 Execute 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 Tuplejavax.persistence.Tuple - JPA Interface Interface 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
 The CriteriaQuery interface defines functionality that is specific
 to top-level queries.<Tuplejavax.persistence.Tuple - JPA Interface
 Interface for extracting the elements of a query result tuple.> q = cb.createTupleQueryCriteriaBuilder.createTupleQuery() - JPA Method
  Create a CriteriaQuery 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 Method
 Specify the item that is to be returned in the query result.(cb.tCriteriaBuilder.array(selections) - JPA Method
 Create an array-valued selection item.upleCriteriaBuilder.tuple(selections) - JPA Method
 Create 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 tupleCriteriaBuilder.tuple(selections) - JPA Method Create a tuple-valued selection item. method builds a CompoundSelectionjavax.persistence.criteria.CompoundSelection - JPA Interface The CompoundSelection interface defines a compound selection item (tuple, array, or result of constructor). instance, which represents Tuplejavax.persistence.Tuple - JPA Interface Interface 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 Interface
 Interface for extracting the elements of a query result tuple.> results = em.createQueryEntityManager.createQuery(criteriaQuery) - JPA Method
 Create an instance of TypedQuery for executing a
 criteria query.(q).getResultListTypedQuery.getResultList() - JPA Method
 Execute 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 Tuplejavax.persistence.Tuple - JPA Interface Interface 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
 The CriteriaQuery interface defines functionality that is specific
 to top-level queries.<CountryAndCapital> q =
      cb.createQueryCriteriaBuilder.createQuery(resultClass) - JPA Method
  Create a CriteriaQuery 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 Method
 Specify the item that is to be returned in the query result.(cb.constructCriteriaBuilder.construct(resultClass,selections) - JPA Method
 Create 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 constructCriteriaBuilder.construct(resultClass,selections) - JPA Method Create a selection item corresponding to a constructor. method builds a CompoundSelectionjavax.persistence.criteria.CompoundSelection - JPA Interface The CompoundSelection 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 Method
 Create an instance of TypedQuery for executing a
 criteria query.(q).getResultListTypedQuery.getResultList() - JPA Method
 Execute 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, CompoundSelectionjavax.persistence.criteria.CompoundSelection - JPA Interface The CompoundSelection 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 selectCriteriaQuery.select(selection) - JPA Method Specify the item that is to be returned in the query result. method.

The CriteriaQuery interface provides a shortcut method - multiselectCriteriaQuery.multiselect(selections) - JPA Method Specify 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 multiselectCriteriaQuery.multiselect(selections) - JPA Method Specify the selection items that are to be returned in the query result.:

  q.multiselectCriteriaQuery.multiselect(selections) - JPA Method
 Specify 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 selectCriteriaQuery.select(selection) - JPA Method Specify the item that is to be returned in the query result. with one of the factory methods (arrayCriteriaBuilder.array(selections) - JPA Method Create an array-valued selection item., tupleCriteriaBuilder.tuple(selections) - JPA Method Create a tuple-valued selection item. or constructCriteriaBuilder.construct(resultClass,selections) - JPA Method Create a selection item corresponding to a constructor.) as demonstrated above.

The behavior of the multiselectCriteriaQuery.multiselect(selections) - JPA Method Specify 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 and Object[] result type - arrayCriteriaBuilder.array(selections) - JPA Method Create an array-valued selection item. is used.
For expected Tuple result - tupleCriteriaBuilder.tuple(selections) - JPA Method Create a tuple-valued selection item. is used.
For any other expected result type - constructCriteriaBuilder.construct(resultClass,selections) - JPA Method Create a selection item corresponding to a constructor. is used.

< Query Structure ^ Query Structure JPQL FROM >