Querying in JPA 2: Typesafe and Object Oriented
Executing a Query and Obtaining a Metamodel Instance
An executable query instance is created when the EntityManager.createQuery(CriteriaQuery)
method is invoked, which returns a TypedQuery
object that specifies the actual type returned from the criteria query. The TypedQuery
interface is a sub type of the javax.persistence.Query
interface. In this snippet, the type information specified in the TypedQuery
object is Employee
and the query gets executed when the getResultList
method is invoked.
TypedQuery<Employee> typedQuery = em.createQuery(criteriaQuery);List<Employee> result = typedQuery.getResultList();
A metamodel instance is obtained by invoking the EntityManager.getMetamodel
method. A metamodel instance of type EntityType<Employee>
is then obtained by invoking Metamodel.entity(Employee.class)
, which is passed to the CriteriaQuery.from
to obtain the query root.
Metamodel metamodel = em.getMetamodel();EntityType<Employee> Employee_ = metamodel.entity(Employee.class);Root<Employee> empRoot = criteriaQuery.from(Employee_);
It is also possible to obtain metamodel information by invoking the Root.getModel
method. The Dept_
instance of type EntityType<Dept>
is obtained and the attribute name
is obtained by invoking the getSingularAttribute
method, which is compared with a string literal.
CriteriaQuery criteriaQuery = criteriaBuilder.createQuery();Root<Dept> dept = criteriaQuery.from(Dept.class);EntityType<Dept> Dept_ = dept.getModel();Predicate testCondition = criteriaBuilder.equal(dept.get(Dept_.getSingularAttribute("name", String.class)), "Ecomm");Corresponding SQL: SELECT * FROM dept WHERE name = 'Ecomm'
Expressions
An Expression object is used in a SELECT
, WHERE
or HAVING
clause of a SQL statement. The Expression interface has isNull
, isNotNull
and in
methods. The following code snippet shows the usage of Expression.in
, where the employee age is checked to be either 20 or 24.
CriteriaQuery<Employee> criteriaQuery = criteriaBuilder .createQuery(Employee.class);Root<Employee> employee = criteriaQuery.from(Employee.class);criteriaQuery.where(employee.get(Employee_.age).in(20, 24));em.createQuery(criteriaQuery).getResultList();Corresponding SQL: SELECT * FROM employee WHERE age in (20, 24)
An Expression object can be obtained using the equal
, notequal
, gt
, ge
, le
, lt
, between
, like
methods of the CriteriaBuilder interface. The following code snippets show the usage of the like
and between
methods. The CriteriaBuilder.between
method checks if the age
is in a specified range, while the CriteriaBuilder.like
method checks if the name
property matches the specified pattern.
// Usage of CriteriaBuilder.betweencriteriaQuery.where(criteriaBuilder.between(employee.get(Employee_.age), 24, 25));em.createQuery(criteriaQuery).getResultList();Corresponding SQL: SELECT * FROM employee WHERE age between 24 AND 25//Usage of CriteriaBuilder.likecriteriaQuery.where(criteriaBuilder.like(employee.get(Employee_.name), "N%"));em.createQuery(criteriaQuery).getResultList();Corresponding SQL: SELECT * FROM employee WHERE name like N%
Compound Predicates
Criteria queries also allow the developers to write compound predicates, through which queries can be tested for multiple conditions. The following query checks for two conditions: first, whether the name
property starts with M and second, whether the age
of the employee is 25. A logical AND
operation is performed to get the resultant record(s).
criteriaQuery.where(criteriaBuilder.and(criteriaBuilder.like(employee.get(Employee_.name), "M%"), criteriaBuilder.equal(employee.get(Employee_.age), 25)));em.createQuery(criteriaQuery).getResultList();Corresponding SQL: SELECT * FROM employee WHERE name LIKE 'M%' AND age = 25
Join Queries
In SQL, joins are performed to browse through multiple tables and obtain query results. Similarly, entity joins are performed by invoking the From.join
method. Joins help in navigating from one entity to another to obtain criteria query results.
The join
method on the Root returns a Join<Dept, Employee>
type (it can also be of SetJoin
, ListJoin
, MapJoin
or CollectionJoin
type), where the Dept
object represents the source from which the target Employee
object is obtained. By default, join operations use inner joins, and outer joins can be used by specifying the JoinType
argument as LEFT
or RIGHT
in the join
method.
CriteriaQuery<Dept> cqDept = criteriaBuilder.createQuery(Dept.class);Root<Dept> deptRoot = cqDept.from(Dept.class);Join<Dept, Employee> employeeJoin = deptRoot.join(Dept_.employeeCollection);cqDept.where(criteriaBuilder.equal(employeeJoin.get(Employee_.deptId).get(Dept_.id), 1));TypedQuery<Dept> resultDept = em.createQuery(cqDept);Corresponding SQL: SELECT * FROM employee e, dept d WHERE e.deptId = d.id and d.id = 1
Fetch Joins
Fetch joins are very helpful in optimizing data access when a collection valued property is involved. This is achieved by pre-fetching the associated object and reducing the overhead of lazy loading. With criteria queries, the fetch
method is used to specify the associated property that needs to be fetched as part of the query execution instead of the join
method.
The semantics of the Fetch
join are the same as that of the Join
. Because the result of the Fetch
operation does not return a Path
object, it cannot be further referenced in the query. In the example below, the employeeCollection
object is loaded when the Dept object is queried and there is no second call to the database as with lazy loading.
CriteriaQuery<Dept> d = cb.createQuery(Dept.class);Root<Dept> deptRoot = d.from(Dept.class);deptRoot.fetch("employeeCollection", JoinType.LEFT);d.select(deptRoot);List<Dept> dList = em.createQuery(d).getResultList();Corresponding SQL: SELECT * FROM dept d, employee e WHERE d.id = e.deptId
Path Expression
Objects, such as Root
instances or Join
instances or an instance obtained from another Path
object using the get
method, can qualify for a Path
object. A Path
object is essential when the query needs to navigate to the attributes of the entities. The argument that the get
method takes is the attribute specified in the entity's metamodel class. The Path
objects are used normally in select
or where
methods of the criteria query object, like this for example:
CriteriaQuery<String> criteriaQuery = criteriaBuilder.createQuery(String.class);Root<Dept> root = criteriaQuery.from(Dept.class);criteriaQuery.select(root.get(Dept_.name));
Parameterized Expressions
In JPQL, query parameters were passed at run time by using the named parameter syntax (variable preceded by a colon, e.g. :age
). In criteria queries, the query parameters can be passed at runtime by creating a typed ParameterExpression
object and setting it with a value before querying using the TypedQuery.setParameter
method. The following code snippet shows a ParameterExpression age
of type Integer
, which is set to a value of 24.
ParameterExpression<Integer> age = criteriaBuilder.parameter(Integer.class);Predicate condition = criteriaBuilder.gt(testEmp.get(Employee_.age), age);criteriaQuery.where(condition);TypedQuery<Employee> testQuery = em.createQuery(criteriaQuery);List<Employee> result = testQuery.setParameter(age, 24).getResultList();Corresponding SQL: SELECT * FROM Employee WHERE age = 24
Ordering the Results
The results of the criteria query can be ordered by invoking theCriteriaQuery.orderBy
method, which takes an Order
object as the argument. By invoking CriteriaBuilder.asc
or CriteriaBuilder.Desc
, an Order
object can be created. In the following code snippet the Employee
instances are ordered based on the ascending order of the age
.CriteriaQuery<Employee> criteriaQuery = criteriaBuilder .createQuery(Employee.class);Root<Employee> employee = criteriaQuery.from(Employee.class);criteriaQuery.orderBy(criteriaBuilder.asc(employee.get(Employee_.age)));em.createQuery(criteriaQuery).getResultList();Corresponding SQL: SELECT * FROM Employee ORDER BY age ASC
GroupBy
The groupBy
method of the CriteriaQuery instance is used to group results based on an Expression
. The queries are further filtered by calling the having
method by setting an additional expression. In the following code snippet the query is grouped by the name
property of the Employee
class, which starts with the letter N.
CriteriaQuery<Tuple> cq = criteriaBuilder.createQuery(Tuple.class);Root<Employee> employee = cq.from(Employee.class);cq.groupBy(employee.get(Employee_.name));cq.having(criteriaBuilder.like(employee.get(Employee_.name), "N%"));cq.select(criteriaBuilder.tuple(employee.get(Employee_.name),criteriaBuilder.count(employee)));TypedQuery<Tuple> q = em.createQuery(cq);List<Tuple> result = q.getResultList(); Corresponding SQL: SELECT name, COUNT(*) FROM employeeGROUP BY name HAVING name like 'N%'
Page 2 of 3
This article was originally published on September 10, 2010