SELECT clause (JPQL / Criteria API)

The ability to retrieve managed entities is a key advantage of JPQL. For example, the following query returns Country objects, which then become managed by the EntityManagerjakarta.persistence.EntityManagerInterface used to interact with the persistence context. instance 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 entities they have all the support that JPA provides, including transparent navigation to other database objects, transparent update detection, support for deletion, and so on.

Query results are not limited to entities. You can use almost any valid JPQL expression in SELECT clauses. Specifying the required query results more precisely can improve performance and can sometimes reduce the amount of Java code needed. Note that query results must always be specified explicitly. JPQL does not support the the SELECT * expression, which is common 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 entities. 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 called projection. Projection extracts field values from entities to form the query results.

The results of the previous query are returned 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>  .();

You can use only single-valued path expressions in the SELECT clause. Collection and map fields cannot be included directly. However, you can add their content 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 the construction of managed entities has some overhead, queries that return non-entities, like the two previous queries, are often more efficient. Such queries are useful mainly for displaying information efficiently. They are less useful in operations that use the results to update and delete, which require managed entities.

Managed entities 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: 

SELECT c.capital FROM Country AS c WHERE c.name = :name

Result expressions that represent anything other than entities (for example, values of system types and user-defined embeddable objects) return 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 during an active transaction 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 for each result row. Each array contains two elements: the first is the country name (c.name), and the second is the capital city name (c.capital.name).

The following code demonstrates how to run this query and process 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 with 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 one, except that the result list contains CountryAndCapital instances instead of 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 how to run 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 can be a JPA-managed class (for example, an entity class) or a lightweight "transfer" class that is used only for collecting and processing query results.

If an entity class is used as a result class, the result entities are created in the NEW state, which means that they are not managed. Such entities lack the JPA functionality of managed entities (for example, transparent navigation and transparent update detection), but they are more lightweight, are built faster, and 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 names start with 'I') use the 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 latter filters duplicate results. Filtering duplicate results might affect performance, depending on the size of the query result list and other factors.

SELECT in criteria queries

The criteria query API provides several ways to set 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 any previously set SELECT content. Many valid criteria API expressions can be used as a selection because criteria API expressions are represented by Expression , which is a subinterface of Selection.

The distinctjakarta.persistence.criteria.CriteriaQuery.distinct(boolean)Specify whether duplicate query results are eliminated. method can be used to eliminate duplicate results, as shown in the previous code example (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 superinterface of CompoundSelectionjakarta.persistence.criteria.CompoundSelectionThe CompoundSelection interface defines a compound selection item (a tuple, array, or result of a constructor)., which represents a multiselection. A multiselection 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.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.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.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 previous 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.multiselect

In the previous 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 shown previously.

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 >