There is a simple workaround exists for this problem. Without executing count, it is possible to fetch data using Specification. I had a similar situation before when I need to fetch the paginated results from the colossal table based on a few conditions, but till org.springframework.data:spring-data-jpa:2.3.3.RELEASE, the org.springframework.data.jpa.repository.JpaSpecificationExecutor class has not provided support for the feature to fetch Paginated Slice for a given Specification. As of September 2020, only Page<T> return type is present as below.
public interface JpaSpecificationExecutor<T> {
...
...
Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);
...
...
}
There is still an open ticket in the Spring Data JPA portal present for this feature request.
Nevertheless, sharing the working solution(a simple workaround) which I implemented in my application for Slice<T> resultset construction, with Specification kind of logic similar to JpaQueryExecution.SlicedExecution#doExecute.
As org.springframework.data.jpa.domain.Specification<T> is simply a resuable javax.persistence.criteria.Predicate type,
public interface Specification<T> {
Predicate toPredicate(Root<T> root, CriteriaQuery query, CriteriaBuilder cb);
}
I have directly used the array of predicates with equal conditions for simplicity in javax.persistence.criteria.CriteriaBuilder.
Code Snippet
@Repository
public class CommonRepository {
@PersistenceContext
private EntityManager entityManager;
public <T> Slice<T> conditionalFindAll(Class<T> entityClass, Map<String, Object> conditionsToApply, Pageable pageable) {
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<T> criteriaQuery = criteriaBuilder.createQuery(entityClass);
Root<T> entityRoot = criteriaQuery.from(entityClass);
List<Predicate> predicates = new ArrayList<>();
//applying condition using predicates(a vanilla flavor of Specification)
conditionsToApply.entrySet().stream()
.filter(Objects::nonNull)
.forEach(entry ->
predicates.add(criteriaBuilder.equal(entityRoot.get(entry.getKey()),
entry.getValue())));
criteriaQuery.select(entityRoot)
.where(criteriaBuilder.and(predicates.toArray(new Predicate[0])));
TypedQuery<T> query = entityManager.createQuery(criteriaQuery);
//limit of the returning result
int pageSize = pageable.getPageSize();
//calculating offset from page-number and page-size
int offset = pageable.getPageNumber() > 0 ? pageable.getPageNumber() * pageSize : 0;
//https://github.com/spring-projects/spring-data-jpa/blob/48597dca246178c0d7e6952425004849d3fb02c0/src/main/java/org/springframework/data/jpa/repository/query/JpaQueryExecution.java#L156
// always fetch additional one element and skip it based on the pageSize to findout whether next set of results present or not.
query.setMaxResults(pageSize + 1);
query.setFirstResult(offset);
List<T> resultList = query.getResultList();
boolean hasNext = pageable.isPaged() && resultList.size() > pageSize;
return new SliceImpl<>(hasNext ? resultList.subList(0, pageSize) : resultList, pageable, hasNext);
}
}
/**
* This integration Test requires the following external dependencies to run:
* 1. MySQL
*/
@ActiveProfiles("test")
@ExtendWith(SpringExtension.class)
@SpringBootTest(classes = Application.class, webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
public class CommonRepositoryITest {
@PersistenceContext
private EntityManager entityManager;
@Autowired
private PhotoRepository photoRepository = null;
@Test
public void conditionalFindAllTest() {
//Data Setup
String title = "BATMAN";
String size = "300*300";
List<Photo> photos = new ArrayList<>();
for (int i = 0; i < 50; i++) {
Photo photo = Photo.builder().
id(UUID.randomUUID().toString()).
title(title).
size("300*300").
thumbnailUrl("http://localhost:8080/" + UUID.randomUUID().toString()).
url("http://localhost:8080/" + UUID.randomUUID().toString()).
build();
photos.add(photo);
}
photoRepository.saveAll(photos);
//data validate
Map<String, Object> conditionsToApply = new HashMap<>();
conditionsToApply.put("title", title);
conditionsToApply.put("size", size);
List<Photo> resultList = entityManager.createQuery("FROM Photo p WHERE p.title = :title AND p.size = :size", Photo.class).
setParameter("title", title).
setParameter("size", size).
getResultList();
boolean hasNext;
int pageNumber = 0;
int limit = 25;
Pageable pageRequest = PageRequest.of(pageNumber, limit);
int itemsToAssert = 0;
do {
Slice<Photo> photosResult = commonRepository.conditionalFindAll(Photo.class, conditionsToApply, pageRequest);
hasNext = photosResult.hasNext();
itemsToAssert = itemsToAssert + photosResult.getNumberOfElements();
pageNumber++;
pageRequest = PageRequest.of(pageNumber, limit);
} while (hasNext);
Assertions.assertEquals(resultList.size(), itemsToAssert);
}
}
