QueryDsl SpringData Jpa findAll how to avoid count()

Question

I'm trying to use QueryDSL with Spring Data JPA, I want to use findAll with pagination but the count is always performed, also if return type is a List. I don't need this count because it is really slow and I could loose the benefit of pagination.

Any solutions for this problem?

This is the count(), it requires about 30 seconds on MySQL:

Mysql too slow on simple query between two tables

In any case I don't want repeat the count for each page I require, this information is required just for first call.

Answer 1

Since QueryDslPredicateExecutor does not support returning Slice as the return value of findAll(Predicate, Pageable), so the Count Query seems unavoidable. But you can define a new base repository interface and implement the findAll method in a way that it does not issue a count query for pagination. For starters, you should define an interface which will be used as the base interface for all other Repositories:

/**
 * Interface for adding one method to all repositories.
 *
 * <p>The main motivation of this interface is to provide a way
 * to paginate list of items without issuing a count query
 * beforehand. Basically we're going to get one element more
 * than requested and form a {@link Page} object out of it.</p>
 */
@NoRepositoryBean
public interface SliceableRepository<T, ID extends Serializable>
        extends JpaRepository<T, ID>,
        QueryDslPredicateExecutor<T> {

    Page<T> findAll(Predicate predicate, Pageable pageable);
}

Then, implement this interface like:

public class SliceableRepositoryImpl<T, ID extends Serializable>
        extends QueryDslJpaRepository<T, ID>
        implements SliceableRepository<T, ID> {
    private static final EntityPathResolver DEFAULT_ENTITY_PATH_RESOLVER = SimpleEntityPathResolver.INSTANCE;
    private final EntityPath<T> path;
    private final PathBuilder<T> builder;
    private final Querydsl querydsl;

    public SliceableRepositoryImpl(JpaEntityInformation<T, ID> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        path = DEFAULT_ENTITY_PATH_RESOLVER.createPath(entityInformation.getJavaType());
        this.builder = new PathBuilder<>(path.getType(), path.getMetadata());
        this.querydsl = new Querydsl(entityManager, builder);
    }

    @Override
    public Page<T> findAll(Predicate predicate, Pageable pageable) {
        int oneMore = pageable.getPageSize() + 1;
        JPQLQuery query = createQuery(predicate)
                .offset(pageable.getOffset())
                .limit(oneMore);

        Sort sort = pageable.getSort();
        query = querydsl.applySorting(sort, query);

        List<T> entities = query.list(path);

        int size = entities.size();
        if (size > pageable.getPageSize())
            entities.remove(size - 1);

        return new PageImpl<>(entities, pageable, pageable.getOffset() + size);
    }
}

Basically, this implementation would fetch one more element than requested size and use the result for constructing a Page. Then you should tell Spring Data to use this implementation as the repository base class:

@EnableJpaRepositories(repositoryBaseClass = SliceableRepositoryImpl.class)

And finally extend the SliceableRepository as your base interface:

public SomeRepository extends SliceableRepository<Some, SomeID> {}