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;
}
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.
The Criteria API is a predefined API used to define queries for entities. It is the alternative way of defining a JPQL query. These queries are type-safe, and portable and easy to modify by changing the syntax. Similar to JPQL it follows abstract schema (easy to edit schema) and embedded objects.
One of the JPA ways for getting only particular columns is to ask for a Tuple object. then you can use T directly in your CriteriaQuery constructor: CriteriaQuery<T> cq = builder. createQuery(T.
The Criteria API is used to define queries for entities and their persistent state by creating query-defining objects. Criteria queries are written using Java programming language APIs, are typesafe, and are portable. Such queries work regardless of the underlying data store.
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 Employee
s -- 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 Object
s - 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 Tuple
s), 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,
multiselect()
method is used with a single selection term, the return type is the selected term itself.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 aSelection
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With