Spring Data JPA return wrong totalElement count after group by

Question

I am new to Spring Data JPA and have the something below

        Page<Object> page = tableARepository.findAll(
                (root, criteriaQuery, criteriaBuilder) -> criteriaQuery
                        .where(sortConfig.getPredicateList(root, criteriaQuery, criteriaBuilder))
                        .groupBy(root.get("colB"))
                        .getRestriction(),
                sortConfig.getPageable()
        );

Everything is correct except for page.getTotalElements() which return the wrong count and I also found out that it is returning the count without groupBy.

I need page.getTotalElements() in order to show the total rows for front end and currently the workaround was I re-query it and get the size of the result which is not very pretty thing to do (the website still in development stage).

I also did print out the hibernate sql query and the select count query did include groupBy but I am not sure what caused it to return the wrong value.

Assume, tableA data as below:

ColA  | ColB | ...
1       A
2       B
3       B
4       C
5       D

And after groupBy I should have result and count below

ColA  | ColB | ...
1       A
2       B
4       C
5       D

totalCount: 4

But somehow I got 5, anyone can help?

NOTE: SortConfig is developed by my colleague to use it for pagination purpose.

EDIT:

select
    count(tblA_.tableA_ID) as col_0_0_ 
from
    tableA tblA_ 
where
    tblA_.tableAColC like ? 
group by
    tblA_.tableAColB

can u update the question with the hibernate generated sql query — Rajendra Gupta, Nov 20 '19 at 10:49
also what do you mean you have 5 count? can u post the result set that its being returned ? maybe its simple a data issue ? — AntJavaDev, Nov 21 '19 at 01:30
@AntJavaDev I did a ```where ColC like abc``` will return 43 rows and 43 count, after that I added a ```groupBy colB``` it will return 33 rows and 43 count. — Chan Chun Weng, Nov 21 '19 at 02:39
ooh now i get your problem !?! cant guess anything else except of those 2 links [here](https://jira.spring.io/browse/DATAJPA-656) and [here](https://jira.spring.io/browse/DATAJPA-945), as a solution i would suggest to use a native query or the original JPA query(not a criteria one) and grab the total count from the result set, not from the pagination element, check this [answer](https://stackoverflow.com/questions/55613291/spring-data-jparepository-pagination-and-group-by). Also as a ref from the 2 links, which spring data version are you using? — AntJavaDev, Nov 21 '19 at 06:27
@AntJavaDev YES! that's what I am currently facing. So it was a bug and thanks for the links I will check those out. I am using spring data jpa 2.1.10.RELEASE and spring boot 2.1.8.RELEASE. — Chan Chun Weng, Nov 21 '19 at 07:14

Camilo Uribe · Answer 1 · 2021-10-19T19:43:09.930

Use @EnableJpaRepositories to define a repositoryBaseClass, I did it in a config class in a child package.

    @Configuration
    @EnableJpaRepositories(
            repositoryBaseClass = SimpleJpaRepositoryEvenForGroupBy.class,
            value = "com.company.repository"
    )
    public class JpaConfig {
    }

Write your own executeCountQuery

    /**
     * A SimpleJpaRepository where COUNT returns the right number of rows even for GROUP BY queries.
     */
    public class SimpleJpaRepositoryEvenForGroupBy<T, ID> extends SimpleJpaRepository<T, ID> {
    
        public SimpleJpaRepositoryEvenForGroupBy(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
            super(entityInformation, entityManager);
        }
    
        public SimpleJpaRepositoryEvenForGroupBy(Class<T> domainClass, EntityManager em) {
            super(domainClass, em);
        }
    
        /**
         * Exactly the same method as in SimpleJpaRepository, but this calls the executeCountQuery of this class
         */
        @Override
        protected <S extends T> Page<S> readPage(TypedQuery<S> query, final Class<S> domainClass, Pageable pageable,
                                                 @Nullable Specification<S> spec) {
    
            if (pageable.isPaged()) {
                query.setFirstResult((int) pageable.getOffset());
                query.setMaxResults(pageable.getPageSize());
            }
    
            return PageableExecutionUtils.getPage(query.getResultList(), pageable,
                    () -> executeCountQuery(getCountQuery(spec, domainClass)));
        }
    
        /**
         *  An executeCountQuery where COUNT returns the right number of rows even for GROUP BY queries.
         */
        private static long executeCountQuery(TypedQuery<Long> query) {
            Assert.notNull(query, "TypedQuery must not be null!");
    
            List<Long> resultList = query.getResultList();
            if (resultList.size() == 1) {
                return resultList.get(0);
            }
    
            return resultList.size();
        }
    }

score 1 · Answer 2 · answered Nov 26 '19 at 08:02

1

It is confirmed that it was a bug in Pageable based on @AntJavaDev commments.

For now I changed to use PageImpl approach that found here and it worked great.

answered Nov 26 '19 at 08:02

Chan Chun Weng

876
2
16
32

score 0 · Answer 3 · answered Nov 21 '19 at 02:09

0

Check your pageable object if it indexes from zero. Something like:

Pageable pageable = new PageRequest(pageId-1, size);

answered Nov 21 '19 at 02:09

tksilicon

3,276
3
24
36

Check the implementation of sortConfig. That is where the problem is. If you can paste the part of the class that has to do with pagination. – tksilicon Nov 21 '19 at 04:09

Spring Data JPA return wrong totalElement count after group by

3 Answers3