After poking around Stack Overflow I found the following solution for counting problem. My requirement is to get the total number of matching rows, and return the first ten for pagination purposes.
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<T> cq = cb.createQuery(clazz);
CriteriaQuery<Long> counterCq = cb.createQuery(Long.class);
counterCq.select(cb.count(counterCq.from(clazz)));
Predicate predicate= null;
Predicate predicate1 = null;
Root<T> root = cq.from(clazz);
for (Map.Entry<String, String> e : filters.entrySet()){
predicate = cb.and(cb.like(root.<String>get(e.getKey()), e.getValue()+ "%"));
}
if(predicate != null){
cq.where(predicate);
counterCq.where(predicate);
}
int pn = ( em.createQuery(counterCq).getSingleResult()).intValue();
logger.debug("number of pages is {}", pn);
setRowCount(pn);
if(sortField !=null && !sortField.trim().equals("")){
if(sortOrder == SortOrder.DESCENDING){
cq.orderBy(cb.desc(root.get(sortField)));
} else{
cq.orderBy(cb.asc(root.get(sortField)));
}
}
Query q = em.createQuery(cq);
q.setFirstResult(first);
q.setMaxResults(first+ps);
List<T> cats= (List<T>)q.getResultList();
This snippet makes hibernate to through
java.lang.IllegalArgumentException: org.hibernate.hql.ast.QuerySyntaxException: Invalid path: 'generatedAlias1.title' [select count(generatedAlias0) from Media as generatedAlias0 where generatedAlias1.title like :param0]
It seems like cq.from(clazz)
cannot be applied for the other query.
Now my question: Is there a way to use the same predicate in both queries?
Best Answer
Your predicate list isn't assembled correctly. You have to 'and' predicates together into a single expression. I also prefer to build my predicate before performing the select for better readability.
Here's a refactor of your code to achieve the correct results: