JPA, How to use the same criteriaQuery Object to get a result List and to make a select count

Question

I use a criteriaBuilder to create a criteriaQuery for a specific type.

CriteriaQuery<? extends File> criteriaQuery = criteriaBuilder.createQuery(Folder.class);

The query is pretty complexe and I have to add multiple joins etc.

I need to limit the result of the query when I use the createQuery method of the entityManager.

final TypedQuery<? extends File> query = this.entityManager.createQuery(criteriaQuery)
                .setFirstResult(start).setMaxResults(filePerPage);

But I also need to know how many results I can get without the limitation because I need it to calculate the number of page for a pagination system.

So I used another criteriaQueryObject to create a select count

CriteriaQuery<Long> criteriaQueryCount = criteriaBuilder.createQuery(Long.class);

It works but I have to apply the same joins and the same filters, so it's like I do the job twice.

For example :

//creation of the criteriaBuilders from specific types
CriteriaQuery<? extends File> criteriaQuery = criteriaBuilder.createQuery(Folder.class);

CriteriaQuery<Long> criteriaQueryCount = criteriaBuilder.createQuery(Long.class);

//creation of the roots
Root<? extends File> root = criteriaQuery.from(Folder.class),
                     rootCount = criteriaQuery.from(Folder.class);

//list of predicates feeded next
List<Predicate> criteriaList = new ArrayList<>(),
                criteriaCountList = new ArrayList<>();

//adding a join and the predicates
CriteriaBuilder.In<Long> inTagIds = criteriaBuilder.in(root.join("tags", JoinType.INNER).get("id"));
CriteriaBuilder.In<Long> inTagIdsCount = criteriaBuilder.in(rootCount.join("tags", JoinType.INNER).get("id"));

tags.forEach(t -> {
       inTagIds.value(t.getId());
       inTagIdsCount.value(t.getId());
});

criteriaList.add(inTagIds);
criteriaCountList.add(inTagIds);

.... I have almost 10 others filters like this one.

//generating the requests
criteriaQuery.where(criteriaBuilder.and(criteriaList.toArray(new Predicate[0])));

criteriaQuery.distinct(true);

criteriaQueryCount.select(criteriaBuilder.count(rootCount));

criteriaQueryCount.where(criteriaBuilder.and(criteriaCountList.toArray(new Predicate[0])));

final TypedQuery<? extends File> query = this.entityManager.createQuery(criteriaQuery)
                .setFirstResult(searchDto.start).setMaxResults(this.filePerPage);

Long count = this.entityManager.createQuery(criteriaQueryCount).getSingleResult();

List<? extends File> searchRs = query.getResultList();

My question is, is there a more convinient way to do that, I'd like to use the same CriteriaQuery object and/or the same root, is that possible ?

What is the problem, exactly? Simply extract a method for appending all the conditions, joins etc. to the criteriaQuery, and you should easily see how to generify it to accomodate for criteriaQueryCount. In the end, you'll only want to leave criteriaBuilder.createQuery(...) and criteriaQuery.select(...) out of that method — crizzis
@crizzis I can dod that too of course. But it's not very convenient. — Kaizokun

Amalgovinus Amalgovinus · Accepted Answer · 2020-09-10T01:23:44

The only way to do it in one method is to have it use untyped CriteriaQuery (and suppress warnings), and to return an untyped TypedQuery. You can pass in a boolean that tells it not to do sorting, etc. when you do the count query. The calling methods handle the typing and have to have warnings suppressed too.

Wish there was a more elegant way but this is it. It actually turns all the criteriaquery junk into JPQL behind the scenes, so in cases like this it's almost better to go back to giant string blocks.

JPA, How to use the same criteriaQuery Object to get a result List and to make a select count

1 Answers