How to disable counting when specification and Pageable are used together?

Question

Methods with JpaSpecificationExecutor are inadequate, none of them give me what I want:

Page<T> findAll(Specification<T> spec, Pageable pageable) List<T> findAll(Specification<T> spec) List<T> findAll(Specification<T> spec, Sort sort) 

The first method executes a broken query and a counting request. The following 2 do not paginate at all. I need one of the following:

 Slice<T> findAll(Specification<T> spec, Pageable pageable) List<T> findAll(Specification<T> spec, Pageable pageable) 

Without extending the JpaSpecificationExecutor, I was able to execute both requests, but was also a count request. In my situation, the count request should be avoided because it is very expensive. The question is how?

Answer 1

See the SimpleJpaRepository findAll(Specification, Pageable) and readPage(TypedQuery, Pageable, Specification) methods. It seems that the Spring implementation is to always execute the count request and check if startIndex is out of range before executing the select request:

 protected Page<T> readPage(TypedQuery<T> query, Pageable pageable, Specification<T> spec) { query.setFirstResult(pageable.getOffset()); query.setMaxResults(pageable.getPageSize()); Long total = QueryUtils.executeCountQuery(getCountQuery(spec)); List<T> content = total > pageable.getOffset() ? query.getResultList() : Collections.<T> emptyList(); return new PageImpl<T>(content, pageable, total); } 

I do not think this is always the best practice. For example, in my use case, we are happy to execute the counting request earlier, and not in subsequent calls, because we know that new data is not received often enough to guarantee an update of the account, and the counter request is very expensive to complete.

It would be great if Spring Data could provide a flag or an alternative method to turn off an account for querying criteria, similar to simple search queries .

At the same time, here is my solution for working:

Create an inner class that subclasses SimpleJpaRepository. Override readPage to disable the count request. Create a DAO, annotate it with @Repository, and instantiate this inner class to pass the correct EntityManager. Finally, enter this DAO wherever the criteria for the “ignore” criteria apply:

 @Repository public class CriteriaNoCountDao { @PersistenceContext protected EntityManager em; public <T, ID extends Serializable> Page<T> findAll(Specification<T> spec, Pageable pageable, Class<T> clazz){ SimpleJpaNoCountRepository<T, ID> noCountDao = new SimpleJpaNoCountRepository<T, ID>(clazz, em); return noCountDao.findAll(spec, pageable); } /** * Custom repository type that disable count query. */ public static class SimpleJpaNoCountRepository<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> { public SimpleJpaNoCountRepository(Class<T> domainClass, EntityManager em) { super(domainClass, em); } /** * Override {@link SimpleJpaRepository#readPage(TypedQuery, Pageable, Specification)} */ protected Page<T> readPage(TypedQuery<T> query, Pageable pageable, Specification<T> spec) { query.setFirstResult(pageable.getOffset()); query.setMaxResults(pageable.getPageSize()); List<T> content = query.getResultList(); return new PageImpl<T>(content, pageable, content.size()); } } }