Announcement Announcement Module
Collapse
No announcement yet.
is there any way to do a count(distinct(*)) using Specifications? Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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

  • #2
    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);
    }

    Comment

    Working...
    X