12

I wanted to fetch the last 5 records of an entity. But unable to fetch it via Spring Data JPA.

Initially I was trying to get the data via LIMIT query but LIMIT isn't supported in JPA.

Later I tried with Pageable interface.

Pageable pageCount = new PageRequest(0, 10, Direction.ASC,"id");
List<TxnEntity> txnEntities = txnDAO
            .findByAccountEntity(accountEntity,pageCount);

This gives me first page with 10 Objects.

But my requirement is to get last 10 or 5 Objects. So how can I get it via Pageable interface in the Spring framework?

Evan Knowles
  • 7,426
  • 2
  • 37
  • 71
virsha
  • 1,140
  • 4
  • 19
  • 40

4 Answers4

5

From the reference documentation:

To find out how many pages you get for a query entirely you have to trigger an additional count query. By default this query will be derived from the query you actually trigger.

You can determine the number of pages by issuing a count query in advance:

Long count = txnDAO.countByAccountEntity(accountEntity);
Pageable pageCount = new PageRequest((int)count/10, 10, Direction.ASC,"id");
Ori Dar
  • 18,687
  • 5
  • 58
  • 72
  • 1
    The above solution works only when count is exact multiple of 10. eg . i. if count is 30 then I will get pageRequest as (Number: 3, size: 10, Direction.ASC,"id"); which gives me 10 objects as expected is last 10. but ii. if count is 27 then I will get pageRequest as (Number: 2, size: 10, Direction.ASC,"id"); which gives me only 7 elements but expected is last 10. – virsha May 20 '15 at 07:07
4

The output I was expecting was last n records.

So I have used the Spring Data JPA 1.7 inbuilt query 

int n = 10;

Pageable pageCount = new PageRequest(0, n);

List<TxnEntity> txnEntities = txnDAO
        .findByAccountEntityOrderByIdDesc(accountEntity,pageCount);

Now this will return last n records.

The DAO will return all records in descending order, and by using Pageable we can how many records we need from bottom.

It was simple.

For more JPA query inbuilt queries : http://docs.spring.io/spring-data/jpa/docs/current/reference/html/

virsha
  • 1,140
  • 4
  • 19
  • 40
2

You could reverse your sort and then get the first 5.

objectuser
  • 671
  • 6
  • 15
0

Just for the reference:

As per http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#repositories.limit-query-result

you can also create a method findTop5ByAccountEntityOrderByIdDesc(accountEntity) in your repository. I would prefer this from the solution with the additional count query.

If you want, you can also add the Pageable parameter, but it doesn't make much sense.

However, there is several potential issues there: id is not a natural way to find the "last" entries. It will always give you the same result, but depending on the generation strategy and how ids are potentially cached, it is not guaranteed, that the last "saved" entity has the highest ID. This might work in 90% of the cases, but you should be cautious there.

Is this, what you intented to do?

heppa
  • 31
  • 5