CODEWITHSUNDEEP
springhibernatejpaspring-dataspring-data-jpa
Prasad
Prasad

Reputation: 686

Order By Date Desc Limit in Spring Data JPA

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?

Upvotes: 4

Views: 11949

Answers (2)

skethoskope
skethoskope

Reputation: 51

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

Upvotes: 2

Ali Dehghani
Ali Dehghani

Reputation: 48183

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.

Upvotes: 5

Related Questions