When to use the select clause in the JPA criteria API?

Question

1. Without using CriteriaQuery#select() :

   public List<Address> getAddressOfManager(String designation, String name, String orderByColumn) {
   
       Boolean ascending = false;
       CriteriaBuilder cb = emanager.getCriteriaBuilder();
       CriteriaQuery<Address> cq = cb.createQuery(Address.class);
       Root<Address> root = cq.from(Address.class);
       //cq.select(root);  <-------------
       Join<Address, Employee> employeeAddress = root.join(Address_.employee);
       Join<Employee,Project> employeeProject = employeeAddress.join(Employee_.project);
       cq.where(cb.or(cb.equal(employeeProject.get(Project_.name), name),cb.equal(employeeAddress.get(Employee_.designation), designation)));
       Order order = ascending ? cb.asc(root.get(orderByColumn))
               : cb.desc(root.get(orderByColumn));
       cq.orderBy(order);
       List<Address> result = emanager.createQuery(cq).getResultList();
       return result;
   }
   

2. With CriteriaQuery#select() :

   public List<Address> getAddressOfManager(String designation, String name, String orderByColumn) {
   
       Boolean ascending = false;
       CriteriaBuilder cb = emanager.getCriteriaBuilder();
       CriteriaQuery<Address> cq = cb.createQuery(Address.class);
       Root<Address> root = cq.from(Address.class);
       cq.select(root); //<----------------
       Join<Address, Employee> employeeAddress = root.join(Address_.employee);
       Join<Employee,Project> employeeProject = employeeAddress.join(Employee_.project);
       cq.where(cb.or(cb.equal(employeeProject.get(Project_.name), name),cb.equal(employeeAddress.get(Employee_.designation), designation)));
       Order order = ascending ? cb.asc(root.get(orderByColumn))
               : cb.desc(root.get(orderByColumn));
       cq.orderBy(order);
       List<Address> result = emanager.createQuery(cq).getResultList();
       return result;
   }
   

   Now, I am confused as to when to use select() in JPA criteria query.

Answer 1

There are basically two ways to specify the projection term (or simply saying the selection term) on the CriteriaQuery<T> interface in which the query result is to be specified :

CriteriaQuery<T> select(Selection<? extends T> selection);
CriteriaQuery<T> multiselect(Selection<?>... selections);

Often the projection term used is the candidate class (Address in your examples) of the query itself. It may be implicit as in your first example.

The query from the Address entity in your first example does not explicitly specify its projection term and it is the same as explicitly selecting the candidate class itself in this case.

A candidate persistent entity itself as the only projection term is implicitly inferred.

---

When the projected result of the query is however, something other than the candidate persistent entity itself, several other constructs are available to shape the result of the query. These constructs are available in the CriteriaBuilder interface.

Methods to shape query result :

CompoundSelection<Y> construct(Class<Y> result, Selection<?>... terms);
CompoundSelection<Object[]> array(Selection<?>... terms);
CompoundSelection<Tuple> tuple(Selection<?>... terms);

_{For the sake of completeness only, I will try to demonstrate each of them in turn with a simple example for each of them.}

---

Shaping query result into instances of a class (a non persistent entity) by construct():

The construct() method creates an instance of the given class argument and invokes a constructor (of a non-persistent entity) with values from the input selection terms. These arguments to the constructor of a non-persistent entity (not even an entity, a plain Java class) must match in number, order and type (datatype) with values corresponding to the input selection terms, for example.

CriteriaQuery<EmployeeDetails> q = cb.createQuery(EmployeeDetails.class);
Root<Employee> root = q.from(Employee.class);
q.select(cb.construct(EmployeeDetails.class, root.get(Employee_.empName), root.get(Employee_.salary));

EmployeeDetails in this case, is a plain Java class -- a non-persistent entity that has a constructor which takes two parameters of type String (for empName) and BigDecimal (for salary).

The query returns a List<EmployeeDetails> -- a non-persistent entity from the selected Employees -- a persistent entity.

_{Depending upon the number of rows returned, the query may also return EmployeeDetails using the getSingleResult() method, for example.}

Multiple selection items can also be combined into a compound selection term that represents an Object[] or Tuple as shown below.

Shaping query result into an Object[] :

CriteriaQuery<Object[]> q = cb.createQuery(Object[].class);
Root<Employee> root = q.from(Employee.class);
q.select(cb.array(root.get(Employee_.empName), root.get(Employee_.salary));
List<Object[]> list = entityManager.createQuery(q).getResultList();

As can be seen, the query returns a List<Object[]>. Each element of this list contains a 0-based array of Objects - Object[].

Shaping query result into a Tuple :

CriteriaQuery<Tuple> q = cb.createTupleQuery();
Root<Employee> root = q.from(Employee.class);
Selection<String> empName = root.get(Employee_.empName).alias("empName");
q.select(cb.tuple(empName, root.get(Employee_.salary).alias("salary");
List<Tuple> list = entityManager.createQuery(q).getResultList();

String employeeName = list.get(0).get("empName");//Referring to by using its alias (empName)
String employeeSalary = list.get(0).get(1);//Referring to by using its index (salary)

//Iterate over a list of tuples through a foreach loop using alias.

for (Tuple tuple : list) {
    System.out.println(tuple.get("empName") + " : " + tuple.get("salary"));
}

The query returns a List<Tuple>. The values held by a Tuple are accessible by a 0-based integer index, using an alias name of the TupleElement or directly by the TupleElement.

_{CriteriaBuilder#createTupleQuery() is analogous to CriteriaBuilder#createQuery(Tuple.class).}

Using multiselect() to interpret terms based on result type :

Input terms are automatically interpreted by the multiselect() method based on the result type of the CriteriaQuery to automatically arrive at the shape of the result -- the return type of the CriteriaQuery in question. For example, the very first example in this answer could be rewritten using multiselect() as follows.

CriteriaQuery<EmployeeDetails> q = cb.createQuery(EmployeeDetails.class);
Root<Employee> root = q.from(Employee.class);
q.multiselect(root.get(Employee_.empName), root.get(Employee_.salary));

Since the type of the result is EmployeeDetails, the multiselect() method interprets its argument projection terms as constructor arguments of EmployeeDetails.

Notice that a construct like this, cb.construct(EmployeeDetails.class, root.get(Employee_.empName), root.get(Employee_.salary) as used in the first example, does not have to be used in the preceding example using multiselect() which automatically interprets its argument projection terms based on the return type of the query and invokes an appropriate constructor in the result class - EmployeeDetails.

If the query were to be specified to return a Tuple (or a list of Tuples), the multiselect() method with the exact same arguments would create Tuple instances instead as shown below.

CriteriaQuery<Tuple> q = cb.createTupleQuery();
Root<Employee> root = q.from(Employee.class);
q.multiselect(root.get(Employee_.empName), root.get(Employee_.salary));

Needless to mention but the same thing can also be rewritten to return an Object[] (or a list of Object[]s).

CriteriaQuery<Object[]> q = cb.createQuery(Object[].class);
Root<Employee> root = q.from(Employee.class);
q.multiselect(root.get(Employee_.empName), root.get(Employee_.salary));

In both the cases, the muliselect() method automatically interprets its argument projection terms based on the return type of the query as said earlier which is Tuple and Object[] respectively.

_{It should also be noted that EcliseLink has a bug which is still to be fixed while fetching a single Boolean selection term in this way using multiselect() as shown in this question}.

---

Depending upon different JPA providers, the behaviour of the multiselect() method gets more interesting with Object as a result type,

• If the multiselect() method is used with a single selection term, the return type is the selected term itself.
• If the multiselect() method however, contains more than one input term/selection term/projection term, the result type is Object[].

I am not quite sure about different providers but the selection terms as specified by the multiselect() method may not be an array or a collection (or a tuple-valued compound term). The only compound terms which may be allowed as multiselect() arguments are those which are created by the construct() method which essentially represents a single element in turn.

---

In addition to the above constructs, the usage of CriteriaQuery#select(Selection<? extends T> selection) is quite common while using scalar / group / aggregate / single-valued functions such as count(), max(), min() etc and sub-queries. The usage of sub-queries is excluded from this answer for brevity.

A Selection defines what is selected by a query. A Selection can be any object expression, attribute expression, function, sub-select, constructor or aggregation function. An alias can be defined for a Selection using the alias() API.

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();

// Count the total employees
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.select(criteriaBuilder.count(employee)); //<------
Query query = entityManager.createQuery(criteriaQuery);
Long result = query.getSingleResult();

// Maximum salary
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();
Root employee = criteriaQuery.from(Employee.class);
criteriaQuery.select(criteriaBuilder.max(employee.get("salary")); //<------
Query query = entityManager.createQuery(criteriaQuery);
Long result = query.getSingleResult();

http://en.wikibooks.org/wiki/Java_Persistence/Criteria#Selection