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 的实现似乎是在执行选择查询之前总是执行计数查询并检查 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屋！