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

Slice is simply a specialization of Window, the same goal can be achieved using the method of JpaSpecificationExecutor

<S extends T, R> R findBy(Specification<T> spec, Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction);

https://gist.github.com/josergdev/06c82891a719eca4834410339885ad23

package dev.joserg.jpa;

import static org.springframework.data.domain.ScrollPosition.offset;

import java.util.function.Function;

import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.ScrollPosition;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.SliceImpl;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Window;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.repository.query.FluentQuery.FetchableFluentQuery;

public interface SliceSpecificationExecutor<T> extends JpaSpecificationExecutor<T> {

  default Window<T> findAllWindowed(Specification<T> spec, Sort sort, int limit, ScrollPosition scrollPosition) {
    return this.findBy(spec, toWindow(sort, scrollPosition, limit));
  }

  default Window<T> findAllWindowed(Specification<T> spec, Pageable pageable) {
    return this.findAllWindowed(spec, pageable.getSort(), pageable.getPageSize(), offset(pageable.getOffset()));
  }

  default Slice<T> findAllSliced(Specification<T> spec, Pageable pageable) {
    final var window = this.findAllWindowed(spec, pageable);
    return new SliceImpl<>(window.getContent(), pageable, window.hasNext());
  }

  private static <T> Function<FetchableFluentQuery<T>, Window<T>> toWindow(Sort sort, ScrollPosition scrollPosition, int limit) {
    return fetchableFluentQuery -> fetchableFluentQuery.sortBy(sort).limit(limit).scroll(scrollPosition);
  }

}

Answer 2

Update for 2024 because readPage is @Deprecated in old version

      @Override
        @NonNull
        protected <S extends T> Page<S> readPage(TypedQuery<S> query, @NonNull Class<S> domainClass, Pageable pageable,
                Specification<S> spec) {

            query.setFirstResult((int) pageable.getOffset());
            query.setMaxResults(pageable.getPageSize());
            List<S> content = query.getResultList();

            return new PageImpl<>(content, pageable, content.size());
        }
    

Answer 3

An update to Chistopher Yang's answer with Spring JPA version 2.7.7

@Repository
public class SubscriptionSearchRepo {

    @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
        @NonNull
        protected <S extends T> Page<S> readPage(TypedQuery<S> query, @NonNull Class<S> domainClass, Pageable pageable, Specification<S> spec) {
            int maxPerPage = pageable.getPageSize();
            int totalKnownRecords = (int) pageable.getOffset() + maxPerPage;

            query.setFirstResult((int) pageable.getOffset());
            query.setMaxResults(pageable.getPageSize() + 1);
            List<S> content = query.getResultList();

            // There's another page
            if (content.size() == maxPerPage + 1) {
                totalKnownRecords++;
            }
            // Else, this is the last page

            // The one additional element was just there to check if there are more records past this page,
            // so we can safely remove it.
            content.remove(content.size() - 1);

            return new PageImpl<>(content, pageable, totalKnownRecords);
        }
    }
}

Answer 4

There is a simple workaround exists for this problem. Without executing count, it is possible to fetch data using Specification. I had a similar situation before when I need to fetch the paginated results from the colossal table based on a few conditions, but till org.springframework.data:spring-data-jpa:2.3.3.RELEASE, the org.springframework.data.jpa.repository.JpaSpecificationExecutor class has not provided support for the feature to fetch Paginated Slice for a given Specification. As of September 2020, only Page<T> return type is present as below.

    public interface JpaSpecificationExecutor<T> {
            ...
            ...
          Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);
            ...
            ...
    }

There is still an open ticket in the Spring Data JPA portal present for this feature request.

Nevertheless, sharing the working solution(a simple workaround) which I implemented in my application for Slice<T> resultset construction, with Specification kind of logic similar to JpaQueryExecution.SlicedExecution#doExecute.

As org.springframework.data.jpa.domain.Specification<T> is simply a resuable javax.persistence.criteria.Predicate type,

    public interface Specification<T> {
      Predicate toPredicate(Root<T> root, CriteriaQuery query, CriteriaBuilder cb);
    }

I have directly used the array of predicates with equal conditions for simplicity in javax.persistence.criteria.CriteriaBuilder.

Code Snippet

• Repository Class:

    @Repository
    public class CommonRepository {

        @PersistenceContext
        private EntityManager entityManager;

        public <T> Slice<T> conditionalFindAll(Class<T> entityClass, Map<String, Object> conditionsToApply, Pageable pageable) {
            CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
            CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(entityClass);
            Root<T> entityRoot = criteriaQuery.from(entityClass);
            List<Predicate> predicates = new ArrayList<>();

            //applying condition using predicates(a vanilla flavor of Specification)
            conditionsToApply.entrySet().stream()
                    .filter(Objects::nonNull)
                    .forEach(entry ->
                            predicates.add(criteriaBuilder.equal(entityRoot.get(entry.getKey()),
                                    entry.getValue())));
            criteriaQuery.select(entityRoot)
                    .where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
            TypedQuery<T> query = entityManager.createQuery(criteriaQuery);

            //limit of the returning result
            int pageSize = pageable.getPageSize();
            //calculating offset from page-number and page-size
            int offset = pageable.getPageNumber() > 0 ? pageable.getPageNumber() * pageSize : 0;
        //https://github.com/spring-projects/spring-data-jpa/blob/48597dca246178c0d7e6952425004849d3fb02c0/src/main/java/org/springframework/data/jpa/repository/query/JpaQueryExecution.java#L156
        // always fetch additional one element and skip it based on the pageSize to findout whether next set of results present or not.
            query.setMaxResults(pageSize + 1);
            query.setFirstResult(offset);
            List<T> resultList = query.getResultList();
            boolean hasNext = pageable.isPaged() && resultList.size() > pageSize;
            return new SliceImpl<>(hasNext ? resultList.subList(0, pageSize) : resultList, pageable, hasNext);
        }
    }

• Repository Test Class:

    /**
    * This integration Test requires the following external dependencies to run:
    * 1. MySQL
    */
    @ActiveProfiles("test")
    @ExtendWith(SpringExtension.class)
    @SpringBootTest(classes = Application.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
    public class CommonRepositoryITest {
        @PersistenceContext
        private EntityManager entityManager;

        @Autowired
        private PhotoRepository photoRepository = null;

        @Test
        public void conditionalFindAllTest() {
            //Data Setup
            String title = "BATMAN";
            String size = "300*300";
            List<Photo> photos = new ArrayList<>();
            for (int i = 0; i < 50; i++) {
                Photo photo = Photo.builder().
                id(UUID.randomUUID().toString()).
                title(title).
                size("300*300").
                thumbnailUrl("http://localhost:8080/" + UUID.randomUUID().toString()).
                url("http://localhost:8080/" + UUID.randomUUID().toString()).
                build();
                photos.add(photo);
            }
            photoRepository.saveAll(photos);

            //data validate
            Map<String, Object> conditionsToApply = new HashMap<>();
            conditionsToApply.put("title", title);
            conditionsToApply.put("size", size);

            List<Photo> resultList = entityManager.createQuery("FROM Photo p WHERE p.title = :title AND p.size = :size", Photo.class).
            setParameter("title", title).
            setParameter("size", size).
            getResultList();

            boolean hasNext;
            int pageNumber = 0;
            int limit = 25;
            Pageable pageRequest = PageRequest.of(pageNumber, limit);
            int itemsToAssert = 0;
            do {
                Slice<Photo> photosResult = commonRepository.conditionalFindAll(Photo.class, conditionsToApply, pageRequest);
                hasNext = photosResult.hasNext();
                itemsToAssert = itemsToAssert + photosResult.getNumberOfElements();
                pageNumber++;
                pageRequest = PageRequest.of(pageNumber, limit);
            } while (hasNext);
            Assertions.assertEquals(resultList.size(), itemsToAssert);
        }
    }

Answer 5

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

Answer 6

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