Order By Date Desc Limit in Spring Data JPA

Question

I am trying to limit the query results by using Limit query. With out limit the query is working as expected.

@Query("SELECT a FROM DrmAdpodTimeSlot a where a.startTime > :startTime order by a.startTime desc")
    public List<DrmAdpodTimeSlot> findByStartTime(@Param("startTime") Timestamp startTime);

But When I try to limit the records by using limit (no.of records), as follows,

@Query("SELECT a FROM DrmAdpodTimeSlot a where a.startTime > :startTime order by a.startTime desc limit 2")
    public List<DrmAdpodTimeSlot> findByStartTime(@Param("startTime") Timestamp startTime);

From the above query I am getting the following error,

Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: limit near line 1, column 110 [SELECT a FROM com.dooreme.domain.DrmAd
podTimeSlot a where a.startTime > :startTime order by a.startTime desc limit 2]

How can I use the order by limit query in spring data jpa query?

No JPQL reference will show you "LIMIT" because it is not valid syntax. Read documentation! — Neil Stockton, Jan 07 '16 at 07:29

Ali Dehghani · Accepted Answer · 2016-01-07T07:38:16.347

You can not add pagination support to the Query annotation. There is no need for adding sorting and pagination functionality into HQL/JPQL when you're using Spring Data JPA. Use Pageable as the second argument instead, like following:

@Query("SELECT a FROM DrmAdpodTimeSlot a where a.startTime > :startTime")
public List<DrmAdpodTimeSlot> findByStartTime(@Param("startTime") Timestamp startTime, Pageable pageable);

Pageable encasulates the sort and paging functionality, as spring data jpa doc says:

Add Pageable instance to the query method to dynamically add paging to your statically defined query. A Page knows about the total number of elements and pages available. It does so by the infrastructure triggering a count query to calculate the overall number. As this might be expensive depending on the store used, Slice can be used as return instead. A Slice only knows about whether there’s a next Slice available which might be just sufficient when walking thought a larger result set.

So, you can use either:

@Query("SELECT a FROM DrmAdpodTimeSlot a where a.startTime > :startTime")
public Page<DrmAdpodTimeSlot> findByStartTime(@Param("startTime") Timestamp startTime, Pageable pageable);

Or:

@Query("SELECT a FROM DrmAdpodTimeSlot a where a.startTime > :startTime")
public Slice<DrmAdpodTimeSlot> findByStartTime(@Param("startTime") Timestamp startTime, Pageable pageable);

Also:

Sorting options are handled through the Pageable instance too.

JPQL DOES support pagination ... but that is in the use of setMaxResults/setFirstResult on the JPA Query object. — Neil Stockton, Jan 07 '16 at 07:29
My bad, my answer was in the context of `@Query` annotation, i'll fix my answer — Ali Dehghani, Jan 07 '16 at 07:37
@NeilStockton - That's not pagination, that's limiting and offsets. Also, *JPQL* does not support that, the query API does. — Oliver Drotbohm, Jan 07 '16 at 07:39
Indeed, method returning `Slice` performs only one DB query per call — Ilya Serbis, Mar 23 '18 at 11:54

skethoskope · Answer 2 · 2021-04-07T18:20:56.033

None of the other answers really answered your question of how to limit to the top 2 start times descending order. I'm not sure how to do it using jpql, but using jpa query, you can do findTop2ByOrderByStartTimeDesc

Also, see this post How would I write SELECT TOP 25 sql query in Spring data repository You can use in Repository your query:

@Query("SELECT a FROM DrmAdpodTimeSlot a WHERE a.startTime>'?1' ORDER BY a.startTime DESC")
    List<DrmAdpodTimeSlot> findByStartTime(String startTime, Pageable pageable);
}

And in the Service, use a PageRequest, returning a Page object:

Page<DrmAdpodTimeSlot> top2 = arcustRepository.findByStartTime(arcustno, PageRequest.of(0, 2));
List<DrmAdpodTimeSlot> top2StartTimes = top2.getContent();

Order By Date Desc Limit in Spring Data JPA

2 Answers2

Linked