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?

score 18 · Answer 1 · answered Oct 24 '14 at 17:15

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

score 12 · Answer 2 · answered Feb 15 '15 at 00:23

12

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"));

answered Feb 15 '15 at 00:23

leandrobh

121
1
2

1

the "new PageRequest" was now protected and uninstantiable. but "Pageable pageable = PageRequest.of(0, 10);" did work (for future readers) This worked for me..and "byTop10" and "byFirst10" did not. full imports : import org.springframework.data.domain.PageRequest; import org.springframework.data.domain.Pageable; – granadaCoder Jun 09 '20 at 20:54

score 4 · Accepted Answer · answered Jun 05 '14 at 20:16

4

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.

answered Jun 05 '14 at 20:16

Vlad Mihalcea

123,941
58
509
849

Thanks for the confirmation that this isnt' a hack - going with this way of doing things for now and just using Collections.reverse() to reverse my results. – headlikearock Jun 06 '14 at 19:53
Too bad if you want to return a Stream in ascending order. – Dave Mar 04 '20 at 19:30

score 3 · Answer 4 · answered Jul 26 '17 at 18:30

3

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

 List<User> findFirst10ByUsernameOrderByIdDesc(String username);

answered Jul 26 '17 at 18:30

freemanpolys

1,648
19
19

Looks like this one will return the first 10, and not the last 10. – Jacques Koorts Sep 26 '17 at 12:20
1

With OrderByIdDesc it'll be the last records. – freemanpolys Sep 27 '17 at 15:30
@freemanpolys, this is not the case, the order by clause is applied after first 10 rows are fetched, it would be nice if it goes some thing like this `List orderByIdDescFindFirst10ByUsername(String username);` or `findLast10` for example – DrAhmedJava Aug 15 '19 at 21:21

bradleyfitz · Answer 5 · 2014-06-05T20:09:44.857

The only other way I can think of doing it (other than by ordering first), would be to get the total number of records and then use set max results and set first results.

I don't know the spring-data-jpa, but it should have a set firstresult on the query as well.

// this is a hibernate query... but should be simple
Integer count = (Integer) session.createQuery("select count(*) from ....").uniqueResult(); 

//Get the last ten
Pageable topTen = new PageRequest(count - 10, count);
List<User> result = repository.findByUsername("Matthews", topTen);

Spring Data JPA Java - get Last 10 records from query

5 Answers5