Jpa 2.0 performing a count on a query with joins

It’s been too long since I’ve blogged. Here is an interesting one. I recently had to modify old code. They were using jpa 2.0 in more of a 1.0 syntax. There was a very complex query with joins used for searching. There was pagination, sort of. They were not returning the total record count. So I decided to do a count on the same query as a happy medium until we can upgrade to a newer way of doing things.

I tried using the same query and predicates to do the count. I tried following several examples from stack overflows and forums. But, all the examples were with simple queries that did not do joins.

The biggest thing here is that I had to use different query objects and different predicate objects, while using the same root when creating the count predicates and the count query. It appears that query.from() creates a different root each time it’s called. So, I set this to a variable and reused it.

I’ve left out the complex code that generates the predicates to save space. I can add it if needed. It is in the method getPredicates(searchCustomerRequest, criteriaBuilder, customerRoot); which is also used by searchCustomer(searchCustomerRequest); to build the same predicate list.


Map results = new HashMap();
CriteriaBuilder criteriaBuilder = getRoEntityManager().getCriteriaBuilder();
Logger.debug(ToStringBuilder.reflectionToString(searchCustomerRequest));
CriteriaQuery countQuery = criteriaBuilder.createQuery(Long.class);

Root customerRoot = countQuery.from(Customer.class);
List lstCriteria = getPredicates(searchCustomerRequest, criteriaBuilder, customerRoot);
Predicate[] predicates = lstCriteria.toArray(new Predicate[lstCriteria.size()]);

/*Start count*/
Logger.debug("Getting Count");
countQuery.select(criteriaBuilder.countDistinct(customerRoot));
countQuery.where(criteriaBuilder.and(predicates));
Long totalRecordCount = getRoEntityManager().createQuery(countQuery).getSingleResult();

List lstCustomer = searchCustomer(searchCustomerRequest);

results.put("customerList", lstCustomer);
results.put("totalRecordCount", totalRecordCount);
return results;