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;