当 Specification 和 Pageable 一起使用时如何禁用计数? [英] How to disable count when Specification and Pageable are used together?

问题描述

这些方法带有 JpaSpecificationExecutor 不够用，他们都没有给我我想要的:

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)

第一个方法执行分页查询和计数查询.接下来的 2 根本不执行分页.我需要的是以下之一:

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)

通过不扩展 JpaSpecificationExecutor，我能够执行两个查询，但计数查询也是如此.在我的情况下，必须避免计数查询，因为它非常昂贵.问题是如何?

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?

推荐答案

看SimpleJpaRepository 的 findAll(Specification, Pageable) 和 readPage(TypedQuery, Pageable, Specification) 方法.看来 Spring 的实现是在执行 select 查询之前总是执行一个计数查询并检查 startIndex 是否超出范围:

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.

如果 Spring Data 可以提供一个标志或替代方法来禁用条件查询的计数，那就太好了，类似于 简单查找查询.

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:

创建一个继承 SimpleJpaRepository 的内部类.覆盖 readPage 以禁用计数查询.创建一个DAO，使用@Repository 对其进行注释并实例化这个内部类以传递正确的EntityManager.最后，在无计数"条件搜索适用的地方注入此 DAO:

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

这篇关于当 Specification 和 Pageable 一起使用时如何禁用计数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！