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 EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. em: 

  TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<Country> query =
      em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of TypedQuery<X> for executing a Jakarta Persistence query language statement.("SELECT c FROM Country c", Country.class);
  List<Country> results = query.getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();

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 ExpressionsMultiple SELECT ExpressionsResult Classes (Constructor Expressions)SELECT DISTINCTSELECT 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: 

  TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<String> query = em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of TypedQuery<X> for executing a Jakarta Persistence query language statement.(
      "SELECT c.name FROM Country AS c", String.class);
  List<String> results = query.getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();

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 EntityManagerjakarta.persistence.EntityManagerInterface 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: 

  TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<Object[]> query = em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of TypedQuery<X> for executing a Jakarta Persistence query language statement.(
      "SELECT c.name, c.capital.name FROM Country AS c", Object[].class);
  List<Object[]> results = query.getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();
  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";
  TypedQueryjakarta.persistence.TypedQueryInterface used to control the execution of typed queries.<CountryAndCapital> query =
      em.createQueryjakarta.persistence.EntityManager.createQuery(String,Class)Create an instance of TypedQuery<X> for executing a Jakarta Persistence query language statement.(queryStr, CountryAndCapital.class);
  List<CountryAndCapital> results = query.getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();

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: 

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries.<Country> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a CriteriaQuery<T> object with the given result type.(Country.class);
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("currency")).distinctjakarta.persistence.criteria.CriteriaQuery.distinct(boolean)Specify whether duplicate query results are eliminated.(true);

The selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result. method takes one argument of type Selectionjakarta.persistence.criteria.SelectionThe 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 - Expressionjakarta.persistence.criteria.ExpressionType for query expressions. (and its descendant interfaces).

The distinctjakarta.persistence.criteria.CriteriaQuery.distinct(boolean)Specify whether duplicate query results are eliminated. method can be used to eliminate duplicate results as demonstrated in the above code (using method chaining).

Multi Selection

The Selectionjakarta.persistence.criteria.SelectionThe Selection interface defines an item that is to be returned in a query result. interface is also a super interface of CompoundSelectionjakarta.persistence.criteria.CompoundSelectionThe CompoundSelection interface defines a compound selection item (a tuple, array, or result of a constructor)., which represents multi selection (which is not a valid expression on its own and can be used only in the SELECT clause).

The CriteriaBuilderjakarta.persistence.criteria.CriteriaBuilderUsed to construct criteria queries, compound selections, expressions, predicates, orderings. interface provides three factory methods for building CompoundSelection instances - arrayjakarta.persistence.criteria.CriteriaBuilder.array(Selection...)Create an array-valued selection item., tuplejakarta.persistence.criteria.CriteriaBuilder.tuple(Selection...)Create a tuple-valued selection item. and constructjakarta.persistence.criteria.CriteriaBuilder.construct(Class,Selection...)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: 

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries.<Object[]> q = cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a CriteriaQuery<T> object with the given result type.(Object[].class);
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(cb.arrayjakarta.persistence.criteria.CriteriaBuilder.array(Selection...)Create an array-valued selection item.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name"), c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("capital").getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name")));

The arrayjakarta.persistence.criteria.CriteriaBuilder.array(Selection...)Create an array-valued selection item. method builds a CompoundSelectionjakarta.persistence.criteria.CompoundSelectionThe CompoundSelection interface defines a compound selection item (a tuple, array, or result of a 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.createQueryjakarta.persistence.EntityManager.createQuery(CriteriaQuery)Create an instance of TypedQuery<X> for executing a criteria query.(q).getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();
  for (Object[] result : results) {
      System.out.println(
          "Country: " + result[0] + ", Capital: " + result[1]);
  }

CriteriaBuilder's tuple

The Tuplejakarta.persistence.TupleInterface for extracting the elements of a query result tuple. interface can be used as a clean alternative to Object[]: 

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries.<Tuplejakarta.persistence.TupleInterface for extracting the elements of a query result tuple.> q = cb.createTupleQueryjakarta.persistence.criteria.CriteriaBuilder.createTupleQuery()Create a CriteriaQuery<T> object that returns a tuple of objects as its result.();
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(cb.tjakarta.persistence.criteria.CriteriaBuilder.array(Selection...)Create an array-valued selection item.uplejakarta.persistence.criteria.CriteriaBuilder.tuple(Selection...)Create a tuple-valued selection item.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name"), c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("capital").getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name")));

The tuplejakarta.persistence.criteria.CriteriaBuilder.tuple(Selection...)Create a tuple-valued selection item. method builds a CompoundSelectionjakarta.persistence.criteria.CompoundSelectionThe CompoundSelection interface defines a compound selection item (a tuple, array, or result of a constructor). instance, which represents Tuplejakarta.persistence.TupleInterface 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<Tuplejakarta.persistence.TupleInterface for extracting the elements of a query result tuple.> results = em.createQueryjakarta.persistence.EntityManager.createQuery(CriteriaQuery)Create an instance of TypedQuery<X> for executing a criteria query.(q).getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();
  for (Tuple t : results) {
      System.out.println("Country: " + t.get(0)  + ", Capital: " + t.get(1));
  }

The Tuplejakarta.persistence.TupleInterface 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: 

  CriteriaQueryjakarta.persistence.criteria.CriteriaQueryThe CriteriaQuery interface defines functionality that is specific to top-level queries.<CountryAndCapital> q =
      cb.createQueryjakarta.persistence.criteria.CriteriaBuilder.createQuery(Class)Create a CriteriaQuery<T> object with the given result type.(CountryAndCapital.class);
  Rootjakarta.persistence.criteria.RootA root type in the from clause.<Country> c = q.fromjakarta.persistence.criteria.AbstractQuery.from(Class)Create and add a query root corresponding to the given entity, forming a cartesian product with any existing roots.(Country.class);
  q.selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result.(cb.constructjakarta.persistence.criteria.CriteriaBuilder.construct(Class,Selection...)Create a selection item corresponding to a constructor.(CountryAndCapital.class,
      c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name"), c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("capital").getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name")));

The constructjakarta.persistence.criteria.CriteriaBuilder.construct(Class,Selection...)Create a selection item corresponding to a constructor. method builds a CompoundSelectionjakarta.persistence.criteria.CompoundSelectionThe CompoundSelection interface defines a compound selection item (a tuple, array, or result of a 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.createQueryjakarta.persistence.EntityManager.createQuery(CriteriaQuery)Create an instance of TypedQuery<X> for executing a criteria query.(q).getResultListjakarta.persistence.TypedQuery.getResultList()Execute a SELECT query and return the query results as a typed List<X>.();

As expected - the result objects are CountryAndCapital instances.

CriteriaQuery's multiselect

In the above examples, CompoundSelectionjakarta.persistence.criteria.CompoundSelectionThe CompoundSelection interface defines a compound selection item (a tuple, array, or result of a constructor). instances were first built by a CriteriaBuilder factory method and then passed to the CriteriaQuery's selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result. method.

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

  q.multiselectjakarta.persistence.criteria.CriteriaQuery.multiselect(Selection...)Specify the selection items that are to be returned in the query result.(c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name"), c.getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("capital").getjakarta.persistence.criteria.Path.get(String)Create a path corresponding to the referenced attribute.("name"));

is equivalent to using selectjakarta.persistence.criteria.CriteriaQuery.select(Selection)Specify the item that is to be returned in the query result. with one of the factory methods (arrayjakarta.persistence.criteria.CriteriaBuilder.array(Selection...)Create an array-valued selection item., tuplejakarta.persistence.criteria.CriteriaBuilder.tuple(Selection...)Create a tuple-valued selection item. or constructjakarta.persistence.criteria.CriteriaBuilder.construct(Class,Selection...)Create a selection item corresponding to a constructor.) as demonstrated above.

The behavior of the multiselectjakarta.persistence.criteria.CriteriaQuery.multiselect(Selection...)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):

< Query Structure^ Query StructureJPQL FROM >