JPA sorting query result after querying with Pageable

Question

I am having an @Entity like this:

import java.time.LocalDateTime;
import javax.persistence.Entity;
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import org.springframework.data.jpa.domain.AbstractPersistable;

@Entity
@Data@NoArgsConstructor@AllArgsConstructor
public class Message extends AbstractPersistable<Long> {

    private LocalDateTime messageDate = LocalDateTime.now();
    private String message;

}

And a repository like this:

import java.util.List;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;

public interface MessageRepository extends JpaRepository<Message, Long> {
    //List<Message> findAllByOrderByMessageDateAsc(Pageable pageable);
    // With this I am trying to re-sort what I get

}

And a @Controller

@GetMapping("/messages")
    public String list(Model model) {
        Pageable limit = PageRequest.of(0, 5, Sort.by("messageDate").descending());
        model.addAttribute("messages", messageRepository.findAll(limit));
        //model.addAttribute("messages", messageRepository.findAllByOrderByMessageDateAsc(limit));
        return "messages";
    }

I get five latest messages in descending order. But how do I get them in ascending order?

If I instead of PageRequest.of(0, 5, Sort.by("messageDate").descending()) use PageRequest.of(0, 5, Sort.by("messageDate").ascending()) I get oldest five messages. I want latest five in ascending order. — Lasse, May 31 '20 at 18:22
What you need is last 5 message asc by massage date. The easiest way is do a count query then request again or you have to manually sort after fetching — Eklavya, May 31 '20 at 18:43
I have tried long count = messageRepository.count(); Pageable limit = PageRequest.of((int)count/5, 5, Sort.by("messageDate").ascending()); It does not give five records all the time. How do I sort after fetching? — Lasse, May 31 '20 at 18:58

Eklavya · Answer 1 · 2020-05-31T20:09:24.977

0

What you need is the last 5 messages in ascending order by massage date. Two ways to solve it.

Using Custom implementation of Pageable

You can't use offset properly for PageRequest. So, you need to use a custom implementation of Pageable for offset. You can use custom implementation OffsetBasedPageRequest. Then use it this way.

int totalCount = (int)serviceRepository.count();
Pageable pageable = new OffsetBasedPageRequest(totalCount - limit, limit, Sort.by("messageDate"));
messageRepository.findAll(pageable);

After fetching Sort Page data

You can get the list from Page<T> using page.getContent() then sort manually the list.

Pageable pageable = PageRequest.of(0, limit, Sort.by("messageDate").descending());
List<Message> list = messageRepository.findAll(pageable ).getContent();
List<Message> sorted =list.stream().sorted(Comparator.comparing(r -> r.getMessageDate())).collect(Collectors.toList());

Then again you have to create Page<Massage> if you want.

edited May 31 '20 at 20:09

answered May 31 '20 at 19:03

Eklavya

17,618
4
28
57

1

Thanks. That did not quite work. Error on this list.sort(Comparator.comparing(a -> a.getMessageDate())); But this worked List content = messageRepository.findAll(limit).getContent() .stream().sorted(Comparator.comparing(r -> r.getMessageDate())).collect(Collectors.toList()); – Lasse May 31 '20 at 20:01
Updated as you want – Eklavya May 31 '20 at 20:10
Maybe because list is immutable neither list.sort(Comparator.comparing(a -> a.getMessageDate())); should work – Eklavya Jul 05 '20 at 20:44

JPA sorting query result after querying with Pageable

1 Answers1