Querying in JPA 2: Typesafe and Object Oriented, Page 2
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_);
Dept_instance of type
EntityType<Dept>is obtained and the attribute
nameis obtained by invoking the
getSingularAttributemethod, 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'
An Expression object is used in a
HAVING clause of a SQL statement. The Expression interface has
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
like methods of the CriteriaBuilder interface. The following code snippets show the usage of the
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%
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
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.
join method on the Root returns a
Join<Dept, Employee> type (it can also be of
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
RIGHT in the
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 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
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
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
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));
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 the
CriteriaQuery.orderBy method, which takes an
Order object as the argument. By invoking
Order object can be created. In the following code snippet the
Employee instances are ordered based on the ascending order of the
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 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