In my springboot 2.7.14 project I need to execute a query with a projection, specification and pagination. I already tried the library "specification-with-projection" but it doesn't seem to fit my case because the query is still retrieving all the columns and only after that is mapping my projection. So I ended up using a CriteriaQuery. The front-end pass to the backend the query parameters, pageNumber (in base 1), pageSize and the offset. How can I put all these information together for the execution of the query and the return of a Page object? This is my instruction for the execution of the query:
List<MyDto> myDtoList= entityManager.createQuery(criteriaQuery)
.setFirstResult(((pageNumber - 1) * pageSize) + offset)
.setMaxResults(pageSize)
.getResultList();
After this I do a Select Count to get the total and finally return all the information together with this instruction:
return new PageImpl<>(myDtoList, PageRequest.of(pageNumber - 1, pageSize), totalElements);
The Page object returned in this way not include the correct offset, but only the one from the operation pageNumber*pageSize. I'm pretty sure the problem is in the implementation of PageRequest.of, so I found this class OffsetBasedPageRequest in this answer: Pagination in Spring Data JPA (limit and offset) Unfortunately this specific solution seems to not work with the latest version of Springboot. Has anyone tried to do this recently and can help me with a correct implementation of an OffsetBasedPageRequest please?
