How to query data out of the box using Spring data JPA by both Sort and Pageable?

Question

I am trying Spring data JPA in my project. I want to know if there is an out-of-the-box API to query data, by both Sort and Pageable. Of course, I know I can write that method myself, I just want to know if there is an out-of-the-box one. My DAO extends JpaRepository, and I found there are the following methods I can invoke:

findAll();
findAll(Pageable pageable);
findAll(Sort sort);

But there is no such method as findAll(Sort sort, Pageable pageable), so I am curious.

score 163 · Accepted Answer · answered May 10 '12 at 06:19

163

There are two ways to achieve this:

final PageRequest page1 = new PageRequest(
  0, 20, Direction.ASC, "lastName", "salary"
);

final PageRequest page2 = new PageRequest(
  0, 20, new Sort(
    new Order(Direction.ASC, "lastName"), 
    new Order(Direction.DESC, "salary")
  )
);

dao.findAll(page1);

As you can see the second form is more flexible as it allows to define different direction for every property (lastName ASC, salary DESC).

answered May 10 '12 at 06:19

Tomasz Nurkiewicz

334,321
69
703
674

3

Passing multiple columns sorting data from Javascript through a REST controlle can be done with the following Javascript data: ['name,asc', 'location,desc'] – Stephane Oct 22 '14 at 08:44
Thanks for your answer and it helped me! Could you please change Order to Sort? It is Sort in Spring data. – curious1 Jan 19 '15 at 18:10
4

This was my solution however in spring it is: Pageable pageable = new PageRequest(0, 20, new Sort(new Sort.Order(Direction.ASC, "name"), new Sort.Order(Direction.DESC, "salary"))); – Simon May 14 '15 at 17:27
Is NUMBER datatype (integer as JPA entity type) allowed as a sort element? My DB has NUMBER data stored and those are primary identifiers for records, can I store by that column with JPA pagable support? – Ketan Feb 22 '19 at 20:04
@Ketan Do you find any solution for this? because I'm trying to get sorted a number but I can't find a solution – Robs Apr 20 '22 at 09:03

score 15 · Answer 2 · edited Sep 10 '15 at 20:00

15

Pageable has an option to specify sort as well. From the java doc

PageRequest(int page, int size, Sort.Direction direction, String... properties)

Creates a new PageRequest with sort parameters applied.

edited Sep 10 '15 at 20:00

Maksim

16,635
27
94
135

answered May 10 '12 at 05:09

gkamal

20,777
4
60
57

2

This way you're setting one sort direction for all properties. If you want to differentiate sort direction per property, you have to use Sort object as Tomasz pointed out. – Mariusz Jun 06 '13 at 07:38

score 3 · Answer 3 · answered Jul 14 '20 at 13:09

in 2020, the accepted answer is kinda out of date since the PageRequest is deprecated, so you should use code like this :

Pageable page = PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), Sort.by("id").descending());
return repository.findAll(page);

score 0 · Answer 4 · answered Feb 11 '19 at 00:23

0

Spring Pageable has a Sort included. So if your request has the values it will return a sorted pageable.

request: domain.com/endpoint?sort=[FIELDTOSORTBY]&[FIELDTOSORTBY].dir=[ASC|DESC]&page=0&size=20

That should return a sorted pageable by field provided in the provided order.

answered Feb 11 '19 at 00:23

T04435

12,507
5
54
54

I am looking for a similar way and I can sort using the field but I am not able to get the response in the ordered form using asc or desc. How can set the order in request param? – anonymous Apr 05 '23 at 07:27
For example http://localhost:8080/api/endpoint?page=0&size=10&sort=name. Here how can I specify the order i.e asc/desc? – anonymous Apr 05 '23 at 07:28

score 0 · Answer 5 · answered Feb 02 '20 at 10:01

In my case, to use Pageable and Sorting at the same time I used like below. In this case I took all elements using pagination and sorting by id by descending order:

modelRepository.findAll(PageRequest.of(page, 10, Sort.by("id").descending()))

Like above based on your requirements you can sort data with 2 columns as well.

score 0 · Answer 6 · answered Aug 22 '22 at 01:05

It's possible to provide sort parameter & sort direction right in the URL of the request. Spring Pageable automatically parses the URL parameters & creates Pageable object that is obtained by your rest controller. I believe it's one of the best solutions since it helps to avoid manual PageRequest/Pageable objects instantiation (notice the comma character before sort direction):

domain.com/endpoint?sort=[FIELD],[asc|desc]&page=3&size=100

P.S. Other example of sort direction usage described in Spring documentation not works for me (might be outdated).

score -1 · Answer 7 · answered Feb 10 '19 at 06:57

-1

 public List<Model> getAllData(Pageable pageable){
       List<Model> models= new ArrayList<>();
       modelRepository.findAllByOrderByIdDesc(pageable).forEach(models::add);
       return models;
   }

answered Feb 10 '19 at 06:57

Nazrul Kabir

51
1
5

2

Please, comment on code, even if it's self explanatory. – Andronicus Feb 10 '19 at 07:18
2

While this code may answer the question, providing additional context regarding how and/or why it solves the problem would improve the answer's long-term value. – Wai Ha Lee Feb 10 '19 at 09:55

How to query data out of the box using Spring data JPA by both Sort and Pageable?

7 Answers7

Linked