is there any way to do a count(distinct(*)) using Specifications?

[ageery]

My regular, non-count specification-based queries obey the query.distinct(true) method. However, when my specification contains query.distinct(true) and I pass it to the count(Specification) method, the count query is incorrect. The generated SQL looks like this (Hibernate 4.1):

select distict count(*) ...

Instead of

select count(distinct(*)) ...

Looking at the JPA2 documentation, it looks like the proper way to handle this is to use cb.select(root.countDistinct()) -- http://stackoverflow.com/questions/6...pa-critera-api

I have to say that that seems extremely counter-intuitive: the number of items in the list from executing a distinct query is different from the count of the distinct query.

Looking quickly through the source code, I didn't see anyway to do a distinctCount using Spring Data JPA. Any thoughts on how to work around this?

Thanks
Andrew

[ageery]

Since the distinct doesn't (currently) do anything for a count query, does the following make sense as a change to org.springframework.data.jpa.repository.support.Si mpleJpaRepository? The change is to line 457 which used to just be: query.select(builder.count(root)).

The issue is that the JPA spec requires count queries, as opposed to regular data queries, to specifically state whether the query should be distinct.

private TypedQuery<Long> getCountQuery(final Specification<T> spec) {

final CriteriaBuilder builder = em.getCriteriaBuilder();
final CriteriaQuery<Long> query = builder.createQuery(Long.class);

final Root<T> root = applySpecificationToCriteria(spec, query);
query.select(query.isDistinct() ? builder.countDistinct(root) : builder.count(root)); // changed this

return em.createQuery(query);
}