Spring JPA: find by multiple IDs with Pagination

user_1119043 0
Viewed 6778

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.

spring-data-jpa spring-boot rest pagination
3 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: https://www.petrikainulainen.net/programming/spring-framework/spring-data-jpa-tutorial-part-seven-pagination/

user_130466200

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);
...
}
  1. 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):

http://localhost:8080/api/posts?ids=1050,1049,1048,1043,1042,1041,1040,1039,1038&size=5&page=1&sort=id

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).

user_11190430

To anyone coming here looking to pass a list of ids as a url-parameter like the question asker wants to do and the answer of belgoros explains:

Be aware of the url-max-length of 2048 characters.

So if your list of ids is long enough to require pagination, you probably also want to make the ids a body-parameter. This answer explains how to create body-parameters with spring: https://stackoverflow.com/a/22163492/7465516

I think this is important, because solutions that work on small data but unexpectedly fail on big data are the kind of thing that gets through testing and fails in production.

(I do not have the reputation to make this a comment, I hope this post is acceptable)

user_74655160
Related