Spring JPA: find by multiple IDs with Pagination

How is it possible to apply the pagination to the below query:

@Repository public interface PostRepository extends JpaRepository<Post, Long> { @Query("select b from Building b where b.id in :ids" ) Page<Post> findByIds(@Param("ids") List<Long> postIdsList); ... } 

All the existing examples are based on the standard findAll method that accepts a Pageable object: public Page findAll(Pageable pageable);.

The questions are:

• what the controller method signature should be
• what the repository method parameters should be
• how and what parameters should be passed into the controller method
• should I always split the post IDs for every request
• will Spring make a single query and keep all the found posts in memory or it will hit a query every time for every next/previous page? If so, how can it figure out the IDs to use to find the next/previous posts?

The initial implementation was as follows:

@RestController class PostsController { @Autowired private PostService postService; @GetMapping("/posts", params = "ids") public List<Post> getPaginatedPosts(@RequestParam List<Long> ids) { return postService.findPaginatedPosts(ids); } } @Repository @Repository public interface PostRepository extends JpaRepository<Post, Long> { @Query("select b from Building b where b.id in :ids" ) Page<Post> findByIds(@Param("ids") List<Long> postIdsList); ... } 

I omitted the code from the PostServiceImpl qui implements the PostService and just calls the PostRepository#findByIds method.

2 Answers

Try this:

@Repository public interface PostRepository extends JpaRepository<Post, Long> { @Query( "select o from Building b where id in :ids" ) Page<Post> findByIds(@Param("ids") List<Long> postIdsList,Pageable pageRequest); ... } 

In controller ask for pageSize and pageNo, if it is empty set a default value like pageNo = 0, pageSize=10.

pass these values to to service layer service should create pageable object call findByIds(ids, pagable); and return the page to controller.

you can refer this:

Here is the solution I came to you coupled with the above comments suggestions.

1. Define a repository either extending JpaRepository or PagingAndSortingRepositoryas follows:

@Repository public interface PostRepository extends JpaRepository<Post, Long> { @Query("select p from Post p where p.id in :ids" ) Page<Post> findByIds(@Param("ids") List<Long> postIdsList); ... } 

2. Create a service class and its implementation:

public interface PostService { List<PostDTO> getPostsList(List<Long> ids, Pageable pageable); ... } @Service @Slf4j public class PostServiceImpl implements PostService { ... @Autowired private PostRepository postRepository; ... @Override public List<PostDTO> getPostsList(List<Long> ids, Pageable pageable) { List<PostDTO> resultList = new ArrayList<>(); Page<Post> paginatedPosts = postRepository.findByIds(ids, pageable); List<Post> posts = paginatedPosts.getContent(); posts.forEach(post -> resultList.add(convertToPostDTO(post))); return resultList; } 

And finally, the PostsController part:

@RestController @RequestMapping("/api") class PostsController { @Autowired private PostService postService; ... @GetMapping(value = "/posts", params = "ids") public ResponseEntity <List<PostDTO>>getPostsList(@RequestParam List<Long> ids, Pageable pageable) { List<PostDTO> postsList = postService.getPostsList(ids, pageable); return new ResponseEntity<>(postsList, HttpStatus.OK); } 

The request should contain page and size URL parameters (by default, page is 0 and size is 20):

In the above example, I had 9 records total and I put the parameters explicitly to limit the result list to 5 and display the second page only as well as to sort them by id.

If you don't provide them, the default values will be used (page = 0, size = 20).