Querying in JPA 2: Typesafe and Object Oriented

• facebook
• twitter
• google+
• in
• Email

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 the CriteriaQuery.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