How to disable count when Specification and Pageable are used together?
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());
}
}
}
Create your custom base repositoy impl as seen in that link: https://www.baeldung.com/spring-data-jpa-method-in-all-repositories
Create a method like:
public List<T> findAllBy(Specification<T> aSpecification, Pageable aPageable) {
TypedQuery<T> query = getQuery(aSpecification, aPageable);
query.setFirstResult((int) aPageable.getOffset());
query.setMaxResults(aPageable.getPageSize());
return query.getResultList();
}