JPA2.0 Criteria API: count(*) / select * from 1 restriction

jeroenvs · **Joined:** Thu Apr 22, 2010 2:58 am **Posts:** 2

I want to build a method that can both retrieve a count(*) and select * from a criteria query. Using the hibernate criteria API I could easily do this using

Code:

criteria.setProjection(Projections.rowCount())

, but I recently started using the JPA2.0 Criteria API and am uncertain how to obtain a similar result. Below is a little code fragment to display my situation:

Code:

// Set up criteria (restrictions)
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<EntityStub> criteriaQuery = builder.createQuery(EntityStub.class);
Root<EntityStub> entity_ = criteriaQuery.from(EntityStub.class);
criteriaQuery.where(builder.equal(entity_.get("message"), "second"));

// Generic retrieve list (Works)
TypedQuery<T> entityQuery = entityManager.createQuery(criteriaQuery);
List<T> entities = entityQuery.getResultList();

// Generic retrieve count (doesn't work)
CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
Root<T> entity_ = countQuery.from(criteriaQuery.getResultType());
countQuery.select(builder.count(entity_));
Predicate restriction = criteriaQuery.getRestriction();
if (restriction != null) {
  countQuery.where(restriction); // Copy restrictions, throws: 'Invalid path: 'generatedAlias1.message'
}
Long count = entityManager.createQuery(countQuery).getSingleResult();

The listing of results is quite simple and straight forward, but I'm having trouble retrieving a count of all entities that match my criteria (restrictions). How I'm currently retrieving the count is by setting the root to the criteria query's result type (class) and copying its restrictions. This obviously doesn't work as my countQuery is unaware of the alias 'generatedAlias1', is there a work-around, or preferably a better solution, for my problem?

Yours,
Jeroen

jeroenvs · **Joined:** Thu Apr 22, 2010 2:58 am **Posts:** 2

citress · **Joined:** Fri Jul 05, 2013 9:55 am **Posts:** 3

Running into same issue here.

hallmit · **Posted:** Tue Aug 27, 2013 4:26 am

Hi jeroenvs and citress, this problem was explained very clearly in this post https://forum.hibernate.org/viewtopic.php?p=2438424

andrewshilliday wrote:

This happened because you called criteria.from() twice, once when you generated the criteria that you sent as input to the method, and once again inside the method. Each time you call it, you're returning a new root object with its own alias.

As a workaround you could use the method javax.persistence.criteria.Selection.alias(String) to assign the same alias for the entity root for both queries rather than allowing Hibernate auto-generate them.

Code:

// Set up criteria (restrictions)
CriteriaBuilder builder = entityManager.getCriteriaBuilder();
CriteriaQuery<EntityStub> criteriaQuery = builder.createQuery(EntityStub.class);
Root<EntityStub> entity_ = criteriaQuery.from(EntityStub.class);
entity_.alias("entitySub"); //assign alias to entity root

criteriaQuery.where(builder.equal(entity_.get("message"), "second"));

// Generic retrieve count
CriteriaQuery<Long> countQuery = builder.createQuery(Long.class);
Root<T> entity_ = countQuery.from(criteriaQuery.getResultType());
entity_.alias("entitySub"); //use the same alias in order to match the restrictions part and the selection part
countQuery.select(builder.count(entity_));
Predicate restriction = criteriaQuery.getRestriction();
if (restriction != null) {
  countQuery.where(restriction); // Copy restrictions
}
Long count = entityManager.createQuery(countQuery).getSingleResult();