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?

Anaphase

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

Lancey

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. – Anaphase

Too bad if you want to return a Stream in ascending order. – Miscible

Answer 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

Philippa

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

Amanuensis

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; – Malapert

Answer 4

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

 List<User> findFirst10ByUsernameOrderByIdDesc(String username);

Resolvable

Looks like this one will return the first 10, and not the last 10. – Mizzenmast

With OrderByIdDesc it'll be the last records. – Resolvable

@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<User> orderByIdDescFindFirst10ByUsername(String username); or findLast10 for example – Yellows

Answer 5

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

Peplos

Answer 6

Assuming you have a repository

@Repository
public interface YourRepository extends JpaRepository<YourEntity, Long> {
  List<YourEntity> findFirst100ByOrderByIdDesc();
}

Note the Long is the id and the key for the table, the method asks for the first 100 rows, but since we sort the records in descending order we end up with the last 100 rows. The column name and type can be different, and you could add parameters to this method to be used in select criteria.

Curson

Answer 7

public interface NotificationRepository extends JpaRepository<NotificationsEntity, Long> { List findTop10ByOrderByIdDesc(); }

Cutlip