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.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... em:

  TypedQueryjavax.persistence.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Country> query =
      em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...("SELECT c FROM Country c", Country.class);
  List<Country> results = query.getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();

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).

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.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<String> query = em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(
      "SELECT c.name FROM Country AS c", String.class);
  List<String> results = query.getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();

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.EntityManagerJPA interfaceInterface used to interact with the persistence context.See JavaDoc Reference Page... 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.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<Object[]> query = em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(
      "SELECT c.name, c.capital.name FROM Country AS c", Object[].class);
  List<Object[]> results = query.getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();
  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.TypedQueryJPA interfaceInterface used to control the execution of typed queries.See JavaDoc Reference Page...<CountryAndCapital> query =
      em.createQuerycreateQuery(qlString, resultClass)EntityManager's methodCreate an instance of TypedQuery for executing a
 Java Persistence query language statement.See JavaDoc Reference Page...(queryStr, CountryAndCapital.class);
  List<CountryAndCapital> results = query.getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();

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.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.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("currency")).distinctdistinct(distinct)CriteriaQuery's methodSpecify whether duplicate query results will be eliminated.See JavaDoc Reference Page...(true);

The selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page... method takes one argument of type Selectionjavax.persistence.criteria.SelectionJPA interfaceThe Selection interface defines an item that is to be returned in a query result.See JavaDoc Reference Page... 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.ExpressionJPA interfaceType for query expressions.See JavaDoc Reference Page... (and its descendant interfaces).

The distinctdistinct(distinct)CriteriaQuery's methodSpecify whether duplicate query results will be eliminated.See JavaDoc Reference Page... method can be used to eliminate duplicate results as demonstrated in the above code (using method chaining).

Multi Selection

The Selectionjavax.persistence.criteria.SelectionJPA interfaceThe Selection interface defines an item that is to be returned in a query result.See JavaDoc Reference Page... interface is also a super interface of CompoundSelectionjavax.persistence.criteria.CompoundSelectionJPA interfaceThe CompoundSelection interface defines a compound selection item (tuple, array, or result of constructor).See JavaDoc Reference Page..., 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.CriteriaBuilderJPA interfaceUsed to construct criteria queries, compound selections, expressions, predicates, orderings.See JavaDoc Reference Page... interface provides three factory methods for building CompoundSelection instances - 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... and constructconstruct(resultClass, selections)CriteriaBuilder's methodCreate a selection item corresponding to a constructor.See JavaDoc Reference Page....

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.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Object[]> q = cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(Object[].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...(cb.arrayarray(selections)CriteriaBuilder's methodCreate an array-valued selection item.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name"), c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("capital").getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name")));

The arrayarray(selections)CriteriaBuilder's methodCreate an array-valued selection item.See JavaDoc Reference Page... method builds a CompoundSelectionjavax.persistence.criteria.CompoundSelectionJPA interfaceThe CompoundSelection interface defines a compound selection item (tuple, array, or result of constructor).See JavaDoc Reference Page... instance, which represents results as arrays.

The following code demonstrates the execution of the query and iteration over the results:

  List<Object[]> results = em.createQuerycreateQuery(criteriaQuery)EntityManager's methodCreate an instance of TypedQuery for executing a
 criteria query.See JavaDoc Reference Page...(q).getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();
  for (Object[] result : results) {
      System.out.println(
          "Country: " + result[0] + ", Capital: " + result[1]);
  }

CriteriaBuilder's tuple

The Tuplejavax.persistence.TupleJPA interfaceInterface for extracting the elements of a query result tuple.See JavaDoc Reference Page... interface can be used as a clean alternative to Object[]:

  CriteriaQueryjavax.persistence.criteria.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<Tuplejavax.persistence.TupleJPA interfaceInterface for extracting the elements of a query result tuple.See JavaDoc Reference Page...> q = cb.createTupleQuerycreateTupleQuery()CriteriaBuilder's methodCreate a CriteriaQuery object that returns a tuple of 
  objects as its result.See JavaDoc Reference Page...();
  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...(cb.tarray(selections)CriteriaBuilder's methodCreate an array-valued selection item.See JavaDoc Reference Page...upletuple(selections)CriteriaBuilder's methodCreate a tuple-valued selection item.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name"), c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("capital").getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name")));

The tupletuple(selections)CriteriaBuilder's methodCreate a tuple-valued selection item.See JavaDoc Reference Page... method builds a CompoundSelectionjavax.persistence.criteria.CompoundSelectionJPA interfaceThe CompoundSelection interface defines a compound selection item (tuple, array, or result of constructor).See JavaDoc Reference Page... instance, which represents Tuplejavax.persistence.TupleJPA interfaceInterface for extracting the elements of a query result tuple.See JavaDoc Reference Page... results.

The following code demonstrates the execution of the query and iteration over the results:

  List<Tuplejavax.persistence.TupleJPA interfaceInterface for extracting the elements of a query result tuple.See JavaDoc Reference Page...> results = em.createQuerycreateQuery(criteriaQuery)EntityManager's methodCreate an instance of TypedQuery for executing a
 criteria query.See JavaDoc Reference Page...(q).getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();
  for (Tuple t : results) {
      System.out.println("Country: " + t.get(0)  + ", Capital: " + t.get(1));
  }

The Tuplejavax.persistence.TupleJPA interfaceInterface for extracting the elements of a query result tuple.See JavaDoc Reference Page... 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.CriteriaQueryJPA interfaceThe CriteriaQuery interface defines functionality that is specific 
 to top-level queries.See JavaDoc Reference Page...<CountryAndCapital> q =
      cb.createQuerycreateQuery(resultClass)CriteriaBuilder's methodCreate a CriteriaQuery object with the specified result 
  type.See JavaDoc Reference Page...(CountryAndCapital.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...(cb.constructconstruct(resultClass, selections)CriteriaBuilder's methodCreate a selection item corresponding to a constructor.See JavaDoc Reference Page...(CountryAndCapital.class,
      c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name"), c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("capital").getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name")));

The constructconstruct(resultClass, selections)CriteriaBuilder's methodCreate a selection item corresponding to a constructor.See JavaDoc Reference Page... method builds a CompoundSelectionjavax.persistence.criteria.CompoundSelectionJPA interfaceThe CompoundSelection interface defines a compound selection item (tuple, array, or result of constructor).See JavaDoc Reference Page... 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.createQuerycreateQuery(criteriaQuery)EntityManager's methodCreate an instance of TypedQuery for executing a
 criteria query.See JavaDoc Reference Page...(q).getResultListgetResultList()TypedQuery's methodExecute a SELECT query and return the query results
 as a typed List.See JavaDoc Reference Page...();

As expected - the result objects are CountryAndCapital instances.

CriteriaQuery's multiselect

In the above examples, CompoundSelectionjavax.persistence.criteria.CompoundSelectionJPA interfaceThe CompoundSelection interface defines a compound selection item (tuple, array, or result of constructor).See JavaDoc Reference Page... instances were first built by a CriteriaBuilder factory method and then passed to the CriteriaQuery's selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page... method.

The CriteriaQuery interface provides a shortcut method - multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the query result.See JavaDoc Reference Page..., 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 multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the query result.See JavaDoc Reference Page...:

  q.multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the 
 query result.See JavaDoc Reference Page...(c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name"), c.getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("capital").getget(attributeName)Path's methodCreate a path corresponding to the referenced attribute.See JavaDoc Reference Page...("name"));

is equivalent to using selectselect(selection)CriteriaQuery's methodSpecify the item that is to be returned in the query result.See JavaDoc Reference Page... with one of the factory methods (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... or constructconstruct(resultClass, selections)CriteriaBuilder's methodCreate a selection item corresponding to a constructor.See JavaDoc Reference Page...) as demonstrated above.

The behavior of the multiselectmultiselect(selections)CriteriaQuery's methodSpecify the selection items that are to be returned in the query result.See JavaDoc Reference Page... method depends on the query result type (as set when CriteriaQuery is instantiated):

• For expected Object and Object[] result type - arrayarray(selections)CriteriaBuilder's methodCreate an array-valued selection item.See JavaDoc Reference Page... is used.
• For expected Tuple result - tupletuple(selections)CriteriaBuilder's methodCreate a tuple-valued selection item.See JavaDoc Reference Page... is used.
• For any other expected result type - constructconstruct(resultClass, selections)CriteriaBuilder's methodCreate a selection item corresponding to a constructor.See JavaDoc Reference Page... is used.