Search code examples
sqlspringhibernatejpaspring-data-jpa

Spring Data JPA Java - get Last 10 records from query

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

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