0

I want to load data and count(data) via Criteria API. Just loading data and then checking the result list won't work, because I'm limiting my results with a maxResults parameter, while count(data) is supposed to check the amount of data without any limitations. I have a set of Predicate filters that gets applied to both queries, though.

Mabye I have a general misunderstanding here, but I can't reuse much of the query, since right at the creation, we have to type it to the wanted query result type:

CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<QueryResultObject> criteriaQuery = criteriaBuilder.createQuery(QueryResultObject.class);

Afterwards, we create a Root and Joins for this specific already typed query:

Metamodel metamodel = entityManager.getMetamodel();
EntityType<Order> orderMeta = metamodel.entity(Order.class);
EntityType<Shop> shopMeta = metamodel.entity(Shop.class);

Root<Order> order = criteriaQuery.from(Order.class);
Join<Order, Shop> shop = order.join(orderMeta.getSingularAttribute("shop", Shop.class));
Join<Shop, Address> address = shop.join(shopMeta.getSingularAttribute("address", Address.class));

Next, I'm selecting a bunch of stuff (that corresponds to the constructor of QueryResultObject):

criteriaQuery.multiselect(order.get("id"), order.get("date"),
    address.get("country"), order.get("item").get("name"));

Now I'm constructing a list of Predicates for filtering. My goal is to re-use the same list for the count query, since it's a potentially big, complex to build list. However, in those Predicates, I'm using my Root and Joins that are already coupled to criteriaQuery, and therefore, to the target class QueryResultObject. Sometimes it works, sometimes I encounter problems, so maybe there's a better way to operate here.

List<Predicate> whereClauses = new ArrayList<>();
// ... fill it, e.g.:
Predicate filter = criteriaBuilder.equal(address.get("country"), "US");
whereClauses.add(filter);

criteriaQuery.where(whereClauses.toArray(new Predicate[0]));

Now I'm ready to execute the query:

TypedQuery<QueryResultObject> typedQuery = entityManager.createQuery(criteriaQuery);
typedQuery.setFirstResult(first));
typedQuery.setMaxResults(maxResults);

var results = typedQuery.getResultList();

Jay, it worked! When looking into the generated query, order got assigned to generatedAlias0, shop to generatedAlias2 and address to generatedAlias1. As you can see, it wasn't assigned in the order of the joins, but rather shop was skipped - maybe because nothing from shop was in the multiselect? I haven't really found much about that and I think it's the source of my problem.

Now, when creating a count query, I have to redo quite a bit:

CriteriaQuery<Long> criteriaCountQuery = criteriaBuilder.createQuery(Long.class);
Root<Order> orderCount = criteriaCountQuery.from(Order.class);
Join<Order, Shop> shopCount = orderCount.join(orderMeta.getSingularAttribute("shop", Shop.class));
Join<Shop, Address> addressCount = shopCount.join(shopMeta.getSingularAttribute("address", Address.class));

And finally, I'm trying to reuse my filters and fetch a single number:

criteriaCountQuery.select(criteriaBuilder.count(orderCount));
criteriaCountQuery.where(whereClauses.toArray(new Predicate[0]));
Long count = entityManager.createQuery(criteriaCountQuery).getSingleResult();

However, that's where I encounter a problem: I get a QueryException: could not resolve property error that says that Shop has no field country. Examining the query shows that this time, the aliases were generated in order (probably because of the different select clause), and therefore the Predicates don't quite match anymore.

I don't think that this use case is that exotic, so there's probably some easier and cleaner way to do it? Happy to hear your thoughts!

Noel93
  • 135
  • 11
  • Throw out the `maxResults`, then count the data that's returned and afterwards trim your list to the desired size? – XtremeBaumer Jun 29 '22 at 11:59
  • I don't know if this is efficient. I'm loading data for a frontend table from a potentionally big dataset and I only need the data for one table page, therefore the limit. Loading the whole thing would remove any lazy loading advantage. – Noel93 Jun 29 '22 at 12:08
  • Depending on your mapping, it seems that hibernate might ignore `setMaxResults` anyways. [Read answers here for more info](https://stackoverflow.com/questions/1239723/how-do-you-do-a-limit-query-in-jpql-or-hql). That said, I always found criteria queries to be cumbersome. I would suggest going to JPQL queries with `Pageable`s. [Look here](https://stackoverflow.com/questions/22345081/spring-data-jpa-query-and-pageable). Here JPA will generate the count query as long as it is not a native query – XtremeBaumer Jun 29 '22 at 12:23

0 Answers0