How to sort a particular page number in Spring Data JPA?-CodePudding

I have this paging problem where when I try to sort a table by field header on a particular page number, PageRequest.of(page-1, 10, sort) is sorting the entire table, not on a particular page. Thus, what record is returned in that page is different from the previous record before sorting.

Code:

@Override
public Page<User> getPageAndSort(String field, String direction, int page) {

    Sort sort = direction.equalsIgnoreCase(Sort.Direction.ASC.name())
            ? Sort.by(field).ascending()
            : Sort.by(field).descending();

    Pageable pageable = PageRequest.of(page-1, 10, sort);

    return userRepo.findAll(pageable);
}

For example. I want to sort only in page 1 by id. Returning a sorted record from page 1. The rest of the pages or entire records shouldn't be affected.

Thank you.

CodePudding user response：

I don't think there is an easy way to make this kind of sorting in the database and since you are dealing with a single page which is memory anyway since you render it to the UI, I would just sort it in memory.

Alternatively you can go with a custom SQL statement structured like this:

SELECT * FROM (
    SELECT * FROM WHATEVER
    ORDER BY -- sort clause defining the pagination
    OFFSET ... LIMIT ... -- note that this clause is database dependent.
) ORDER BY -- your sort criteria within the page goes here

You'll have to construct this SQL statement programmatically, so you can't use Spring Datas special features like annotated queries or query derivation.

CodePudding user response：

I think an if condition could solve the problem. Create Pageable instance with respect to the condition.

@Override
public Page<User> getPageAndSort(String field, String direction, int page) {

    Sort sort = direction.equalsIgnoreCase(Sort.Direction.ASC.name())
            ? Sort.by(field).ascending()
            : Sort.by(field).descending();

    Pageable pageable = (page == 1)?PageRequest.of(page-1, 10, sort)
    :PageRequest.of(page-1, 10);

    return userRepo.findAll(pageable);
}

References : https://www.baeldung.com/spring-data-jpa-pagination-sorting#:~:text=We can create a PageRequest object by passing,(0, 2); Pageable secondPageWithFiveElements = PageRequest.of (1, 5);

I think this helps.

CodePudding user response：

I'm not sure, I got your question, but if you want a certain sorted page, the db should definitely create the query plan, sort all the data and return you a certain offset (Page) of the sorted data. It's impossible to get a sorted page without sorting the whole data.