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> {}

Answer 2

FYI there is a spring jira issue:

https://jira.spring.io/browse/DATAJPA-289

Let's vote for this improvement

Answer 3

In case anyone lands here looking for how to achieve the same affect in Spring Data MongoDB as Ali did above for Spring Data JPA, here's my solution modeled on his:

import java.io.Serializable;
import java.util.List;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Order;
import org.springframework.data.mongodb.core.MongoOperations;
import org.springframework.data.mongodb.repository.query.MongoEntityInformation;
import org.springframework.data.mongodb.repository.support.QueryDslMongoRepository;
import org.springframework.data.mongodb.repository.support.SpringDataMongodbQuery;
import org.springframework.data.querydsl.EntityPathResolver;
import org.springframework.data.querydsl.QSort;
import org.springframework.data.querydsl.QueryDslPredicateExecutor;
import org.springframework.data.querydsl.SimpleEntityPathResolver;
import org.springframework.data.repository.core.EntityInformation;

import com.querydsl.core.types.EntityPath;
import com.querydsl.core.types.Expression;
import com.querydsl.core.types.OrderSpecifier;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.mongodb.AbstractMongodbQuery;

/**
 * Custom extension of {@link QueryDslMongoRepository} that avoids unnecessary MongoDB "count"
 * operations
 * <p>
 * {@link QueryDslPredicateExecutor#findAll(Predicate, Pageable)} returns a {@link Page} at
 * potentially great expense because determining the {@link Page}'s "totalElements" property
 * requires doing a potentially expensive MongoDB "count" operation. We'd prefer a "findAll"-like
 * method that returns a {@link Slice} (which doesn't have a "totalElements" property) but no such
 * method exists. See {@link #findAll(Predicate, Pageable)} for more details.
 *
 * @see https://github.com/spring-projects/spring-data-commons/issues/1011
 * @see https://stackoverflow.com/questions/37254385/querydsl-springdata-jpa-findall-how-to-avoid-count
 */
public class MyQueryDslMongoRepository<T, ID extends Serializable> extends QueryDslMongoRepository<T, ID>
            implements MyAbstractRepository<T, ID> {
    private final PathBuilder<T> builder;
    private final EntityInformation<T, ID> entityInformation;
    private final MongoOperations mongoOperations;

    public BTQueryDslMongoRepository(MongoEntityInformation<T, ID> entityInformation, MongoOperations mongoOperations) {
        this(entityInformation, mongoOperations, SimpleEntityPathResolver.INSTANCE);
    }

    public BTQueryDslMongoRepository(MongoEntityInformation<T, ID> entityInformation, MongoOperations mongoOperations,
            EntityPathResolver resolver) {
        super(entityInformation, mongoOperations, resolver);
        EntityPath<T> path = resolver.createPath(entityInformation.getJavaType());
        this.builder = new PathBuilder<T>(path.getType(), path.getMetadata());
        this.entityInformation = entityInformation;
        this.mongoOperations = mongoOperations;
    }

    /**
     * An override of our superclass method to return a fake but cheaper-to-compute {@link Page}
     * that's adequate for our purposes.
     */
    @Override
    public Page<T> findAll(Predicate predicate, Pageable pageable) {
        int pageSize = pageable.getPageSize();
        SpringDataMongodbQuery<T> query = new SpringDataMongodbQuery<T>(mongoOperations, entityInformation.getJavaType())
                .where(predicate)
                .offset(pageable.getOffset())
                .limit(pageSize + 1);
        applySorting(query, pageable.getSort());

        List<T> entities = query.fetch();

        int numFetched = entities.size();
        if (numFetched > pageSize) {
            entities.remove(numFetched - 1);
        }

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

    /**
     * Applies the given {@link Sort} to the given {@link MongodbQuery}.
     * <p>
     * Copied from {@link QueryDslMongoRepository}
     */
    private AbstractMongodbQuery<T, SpringDataMongodbQuery<T>> applySorting(
            AbstractMongodbQuery<T, SpringDataMongodbQuery<T>> query, Sort sort) {

        if (sort == null) {
            return query;
        }

        // TODO: find better solution than instanceof check
        if (sort instanceof QSort) {

            List<OrderSpecifier<?>> orderSpecifiers = ((QSort) sort).getOrderSpecifiers();
            query.orderBy(orderSpecifiers.toArray(new OrderSpecifier<?>[orderSpecifiers.size()]));

            return query;
        }

        for (Order order : sort) {
            query.orderBy(toOrder(order));
        }

        return query;
    }
    /**
     * Transforms a plain {@link Order} into a QueryDsl specific {@link OrderSpecifier}.
     * <p>
     * Copied from {@link QueryDslMongoRepository}
     */
    @SuppressWarnings({ "rawtypes", "unchecked" })
    private OrderSpecifier<?> toOrder(Order order) {

        Expression<Object> property = builder.get(order.getProperty());

        return new OrderSpecifier(
                order.isAscending() ? com.querydsl.core.types.Order.ASC : com.querydsl.core.types.Order.DESC, property);
    }
}

@NoRepositoryBean
public interface MyAbstractRepository<T, ID extends Serializable> extends Repository<T, ID>,
        QueryDslPredicateExecutor<T> {

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

The above works for Spring Data MongoDB 1.10.23 but I assume can be modified to be made to work for more modern versions.

Answer 4

Based on the answer of Ali Dehghani we build the following for querydsl 4.2.1, because the querydsl syntax changed in the current version 4.x

Repository Interface:

import com.querydsl.core.types.EntityPath;
import com.querydsl.core.types.Predicate;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;

public interface SliceableRepository<T> {

  Slice<T> findAllSliced(EntityPath<T> entityPath, Predicate predicate, Pageable pageable);
}

Repository Implementation:
(Must be named "<Interface-Name>Impl")

import com.querydsl.core.types.EntityPath;
import com.querydsl.core.types.Predicate;
import com.querydsl.core.types.dsl.PathBuilder;
import com.querydsl.jpa.JPQLQuery;
import com.querydsl.jpa.impl.JPAQuery;
import com.querydsl.jpa.impl.JPAQueryFactory;
import java.util.List;
import javax.persistence.EntityManager;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Slice;
import org.springframework.data.domain.SliceImpl;
import org.springframework.data.jpa.repository.support.Querydsl;


public class SliceableRepositoryImpl<T> implements SliceableRepository<T> {

  private final EntityManager entityManager;
  private final JPAQueryFactory jpaQueryFactory;

  public SliceableRepositoryImpl(EntityManager entityManager) {
    this.entityManager = entityManager;
    this.jpaQueryFactory = new JPAQueryFactory(entityManager);
  }

  @Override
  public Slice<T> findAllSliced(final EntityPath<T> entityPath, final Predicate predicate,
      final Pageable pageable) {

    final Querydsl querydsl = new Querydsl(entityManager,
        new PathBuilder<>(entityPath.getType(), entityPath.getMetadata()));

    final int oneMore = pageable.getPageSize() + 1;

    final JPAQuery<T> query = this.jpaQueryFactory.selectFrom(entityPath)
        .where(predicate)
        .offset(pageable.getOffset())
        .limit(oneMore);

    final JPQLQuery<T> querySorted = querydsl.applySorting(pageable.getSort(), query);

    final List<T> entities = querySorted.fetch();

    final int size = entities.size();
    // If there was one more result than requested from the pageable,
    // then the slice gets "hasNext"=true
    final boolean hasNext = size > pageable.getPageSize();
    if (hasNext) {
      entities.remove(size - 1);
    }
    return new SliceImpl<>(entities, pageable, hasNext);
  }
}

Use the new repository as fragment in your other repositories:

public SomeRepository extends JpaRepository<Some, Long>, SliceableRepository<Some> {
}

@EnableJpaRepositories(repositoryBaseClass = SliceableRepositoryImpl.class) is NOT needed

Then use it like:

public class MyService {
  @Autowired
  private final SomeRepository someRepository;

  public void doSomething() {
    Predicate predicate = ...
    Pageable pageable = ...
     // QSome is the generated model class from querydsl
    Slice<Some> result = someRepository.findAllSliced(QSome.some, predicate, pageable);
  }
}