How to disable count when Specification and Pageable are used together?

Question

The methods come 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 the paginated query and the count query. The next 2 do not perform pagination at all. What I need is one of the following:

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

By not extending JpaSpecificationExecutor, I was able to get both queries executed, but so was the count query. In my situation, count query must be avoid because it is very expensive. The question is how?

Answer 1

Looking at SimpleJpaRepository's findAll(Specification, Pageable) and readPage(TypedQuery, Pageable, Specification) methods. It seems Spring's implementation is to always perform a count query and check if the startIndex is outside the range before executing the select query:

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 don't believe this is always the best practice. In my use case, for example, we're happy to execute count query once up front and not in subsequent calls because we know new data don't come frequently enough to warrant a count update and the count query is very expensive to execute.

It'd be great if Spring Data can provide a flag or an alternate method to disable count for criteria query, similar to the simple find queries.

In the mean time, here's my work-around solution:

Create an inner class that subclasses SimpleJpaRepository. Override readPage to disable count query. Create a DAO, annotate it with @Repository and instantiate this inner class to pass on the right EntityManager. Finally, inject this DAO wherever "no-count" criteria search is applicable:

@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());         }     } }