Spring Data JPA - Limit results when using Specifications without an unnecessary count query being executed

-Spring-JPA-Dynamic-Query-With-Limit

If you use the findAll(Specification, Pageable) method, a count query is first executed and then the 
data query is executed if the count returns a value greater than the offset.

For what I was doing I did not need pageable, but simply wanted to limit my results.  This is easy 
to do with static named queries and methodNameMagicGoodness queries, but from my research (googling 
for a few hours) I couldn't find a way to do it with dynamic criteria queries using Specifications.

During my search I found two things that helped me to figure out how to just do it myself.

1.) A stackoverflow question.
How to disable count when Specification and Pageable are used together?
http://stackoverflow.com/questions/26738199/how-to-disable-count-when-specification-and-pageable-are-used-together
(where I will add a link to this gist)

2.) Spring documentation - Adding custom behavior to all repositories
http://docs.spring.io/spring-data/data-jpa/docs/current/reference/html/#repositories.custom-behaviour-for-all-repositories

I followed the Spring documentation pretty closely and got this all working pretty quickly without 
any real problems.

BaseRepository.java

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
	List<T> findAll(Specification<T> spec, int offset, int maxResults, Sort sort);
	List<T> findAll(Specification<T> spec, int offset, int maxResults);
}

BaseRepositoryFactoryBean.java

public class BaseRepositoryFactoryBean<R extends JpaRepository<T, I>, T, I extends Serializable> extends JpaRepositoryFactoryBean<R, T, I> {

	@SuppressWarnings("rawtypes")
	@Override
	protected RepositoryFactorySupport createRepositoryFactory(EntityManager entityManager) {
		return new BaseRepositoryFactory(entityManager);
	}

	private static class BaseRepositoryFactory<T, I extends Serializable> extends JpaRepositoryFactory {

		private final EntityManager em;

		public BaseRepositoryFactory(EntityManager em) {

			super(em);
			this.em = em;
		}

		@SuppressWarnings({ "unchecked", "rawtypes", "hiding" })
		protected <T, ID extends Serializable> SimpleJpaRepository<?, ?> getTargetRepository(RepositoryMetadata metadata, EntityManager entityManager) {
			SimpleJpaRepository<?, ?> repo = new BaseRepositoryImpl(metadata.getDomainType(), entityManager);
			return repo;
		}

		protected Class<?> getRepositoryBaseClass(RepositoryMetadata metadata) {
			return BaseRepositoryImpl.class;
		}
	}

}

BaseRepositoryImpl.java

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

	private final EntityManager entityManager;

	public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
		super(domainClass, entityManager);
		this.entityManager = entityManager;
	}

	public List<T> findAll(Specification<T> spec, int offset, int maxResults) {
		return findAll(spec, offset, maxResults, null);
	}

	public List<T> findAll(Specification<T> spec, int offset, int maxResults, Sort sort) {
		TypedQuery<T> query = getQuery(spec, sort);

		if (offset < 0) {
			throw new IllegalArgumentException("Offset must not be less than zero!");
		}
		if (maxResults < 1) {
			throw new IllegalArgumentException("Max results must not be less than one!");
		}

		query.setFirstResult(offset);
		query.setMaxResults(maxResults);
		return query.getResultList();
	}

}

MySpringBootApplication.java

@SpringBootApplication
@EnableJpaRepositories(repositoryFactoryBeanClass = BaseRepositoryFactoryBean.class)
public class MySpringBootApplication {

	public static void main(String[] args) {
		SpringApplication app = new SpringApplication(MySpringBootApplication.class);
		app.run(args);
	}

}

UserRepository.java

// This is just to show an example of a repo
public interface UserRepository extends BaseRepository<User, Long>, JpaSpecificationExecutor<User> {
	
}

I've found 2 problems in your file BaseRepositoryImpl.class.

1. You need to implement the default constructor from super class
   public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) { super(entityInformation, entityManager); this.entityManager = entityManager; }

2. Go to line 11 on this file, sort = null will throw an exception. I fixed this error by replaced null value to Sort.unsorted()

Everything is work well

Thanks for solution.
In my case enough
Work in spring boot 2.4.2

1. @EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
   and no BaseRepositoryFactoryBean.java
2. And yes, need add code how write deathghost

Finally

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import java.io.Serializable;
import java.util.List;

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

    private final EntityManager entityManager;

    public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
        super(domainClass, entityManager);
        this.entityManager = entityManager;
    }

    public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults) {
        return findAll(spec, offset, maxResults, Sort.unsorted());
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort) {
        TypedQuery<T> query = getQuery(spec, sort);

        if (offset < 0) {
            throw new IllegalArgumentException("Offset must not be less than zero!");
        }
        if (maxResults < 1) {
            throw new IllegalArgumentException("Max results must not be less than one!");
        }

        query.setFirstResult((int) offset);
        query.setMaxResults(maxResults);
        return query.getResultList();
    }

}

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;

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

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
    List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort);

    List<T> findAll(Specification<T> spec, long offset, int maxResults);
}

@Repository
public interface GeoAttributeTypeRepository extends BaseRepository<GeoAttributeType, Long> {
}

@SpringBootApplication
@EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

I suspect the problems that @deathghost listed are probably due to the initial solution being done on an older version of Spring. Since I did this almost 3 years ago, I have no idea what version of Spring I did this for originally.

Anyway, I'm glad this helped you and you were able to get it working.

Thanks for solution. I implement Slice semantic based on your solution.

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
    Slice<T> findAllSliced(@Nullable Specification<T> var1, Pageable var2);
}

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

    private final EntityManager entityManager;

    public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
        super(domainClass, entityManager);
        this.entityManager = entityManager;
    }

    public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    @Override
    public Slice<T> findAllSliced(Specification<T> spec, Pageable pageable) {
        TypedQuery<T> query = getQuery(spec, pageable.getSort());

        query.setFirstResult((int) pageable.getOffset());
        int extraSize = pageable.getPageSize() + 1;
        query.setMaxResults(extraSize);
        boolean hasNext = query.getResultList().size() == extraSize;

        List<T> result = query.getResultList();
        if(hasNext){
            result.remove(extraSize - 1);
        }
        return new SliceImpl<>(result, pageable, hasNext);
    }

}

@vora-bei FYI you are calling query.getResultList() twice

boolean hasNext = query.getResultList().size() == extraSize;
List<T> result = query.getResultList();

so you will be executing the query twice. Can I suggest you change it to

List<T> result = query.getResultList();
boolean hasNext = result.size() == extraSize;

Thanks for solution. In my case enough Work in spring boot 2.4.2

1. @EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
   and no BaseRepositoryFactoryBean.java
2. And yes, need add code how write deathghost

Finally

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.support.JpaEntityInformation;
import org.springframework.data.jpa.repository.support.SimpleJpaRepository;

import javax.persistence.EntityManager;
import javax.persistence.TypedQuery;
import java.io.Serializable;
import java.util.List;

public class BaseRepositoryImpl<T, ID extends Serializable> extends SimpleJpaRepository<T, ID> implements BaseRepository<T, ID> {

    private final EntityManager entityManager;

    public BaseRepositoryImpl(Class<T> domainClass, EntityManager entityManager) {
        super(domainClass, entityManager);
        this.entityManager = entityManager;
    }

    public BaseRepositoryImpl(JpaEntityInformation<T, ?> entityInformation, EntityManager entityManager) {
        super(entityInformation, entityManager);
        this.entityManager = entityManager;
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults) {
        return findAll(spec, offset, maxResults, Sort.unsorted());
    }

    public List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort) {
        TypedQuery<T> query = getQuery(spec, sort);

        if (offset < 0) {
            throw new IllegalArgumentException("Offset must not be less than zero!");
        }
        if (maxResults < 1) {
            throw new IllegalArgumentException("Max results must not be less than one!");
        }

        query.setFirstResult((int) offset);
        query.setMaxResults(maxResults);
        return query.getResultList();
    }

}

import org.springframework.data.domain.Sort;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.repository.NoRepositoryBean;

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

@NoRepositoryBean
public interface BaseRepository<T, ID extends Serializable> extends JpaRepository<T, ID> {
    List<T> findAll(Specification<T> spec, long offset, int maxResults, Sort sort);

    List<T> findAll(Specification<T> spec, long offset, int maxResults);
}

@Repository
public interface GeoAttributeTypeRepository extends BaseRepository<GeoAttributeType, Long> {
}

@SpringBootApplication
@EnableJpaRepositories(repositoryBaseClass = BaseRepositoryImpl.class)
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }
}

Thanks buddy

Muchas gracias 🤝

Slice is simply a specialization of Window, the same goal can be achieved using the
<S extends T, R> R findBy(Specification<T> spec, Function<FluentQuery.FetchableFluentQuery<S>, R> queryFunction);
method of JpaSpecificationExecutor

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, limit, scrollPosition));
  }

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

  default Window<T> findAllWindowed(Specification<T> spec, ScrollPosition scrollPosition) {
    return this.findAllWindowed(spec, Sort.unsorted(), scrollPosition);
  }

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

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

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

}