Spring Data JPA Java - get Last 10 records from query

Question

Is there a way to retrieve the last X number of results from a query?

For example - If want the first ten results, I see that example here works: setMaxResults for Spring-Data-JPA annotation?

 public interface UserRepository extends Repository<User, Long> {

       List<User> findByUsername(String username, Pageable pageable);
 }
 //and then I could call it like this
 Pageable topTen = new PageRequest(0, 10);
 List<User> result = repository.findByUsername("Matthews", topTen);

But how do I get the LAST ten records?

The only way I could think of doing it would be to flip the order in the query (findByUsernameDesc, assuming original results were ascending) and then iterate through the list backwards so I can process it in the order I wanted (ascending).

That seems like an ugly way to do it. Is there a way to have the query give me the last X results in the order I want?

Solution 1

The question is how efficient it would be such on option, especially against large data sets.

I would go for a descending index, which I could query using the maxResult support, as you already figured it out.

This is no way a hack. If you were to match 100M results only to get the last X ones, this method would yield the best results.

Solution 2

Spring Data JPA 1.7 has introduced 'top' and 'first' as keywords in derived queries so now we can do like:

public interface UserRepository extends Repository<User, Long> {

   List<User> findFirst10ByUsername(String username);
}

Check it out - Spring Data Release Train Evans Goes GA

Solution 3

PageRequest could be extremely useful for it. There are many options for to construct the PageRequest.

So, an option possible is:

Pageable topTen = new PageRequest(0, 10, Direction.ASC, "username"); 
List<User> result = repository.findByUsername("Matthews", topTen);

I also use without parameters (conditions about object).

@Query(value="select p from Person p")
public List<Person> findWithPageable(Pageable pageable);

And call:

repository.findWithPageable(new PageRequest(0, 10, Direction.DESC, "id"));

Solution 4

You can do this to get the 10 last records filter by the Username:

 List<User> findFirst10ByUsernameOrderByIdDesc(String username);

View more solutions