按日期排序Desc限制Spring数据JPA [英] 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);

但是，当我尝试使用limit（no.of records）限制记录时，

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]

如何使用限制查询的顺序spring数据jpa查询？

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

推荐答案

您无法将分页支持添加到 Query 注释。当您使用Spring Data JPA时，不需要将排序和分页功能添加到 HQL / JPQL 中。使用 Pageable 作为第二个参数，如下所示：

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 包装排序和分页功能，作为春天data jpa doc 表示：

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

在查询中添加 Pageable 实例方法来动态添加分页到
您的静态定义的查询。一个页面知道
元素和可用页面的总数。它通过基础设施
触发计数查询来计算总数。由于这
可能会很昂贵，具体取决于所使用的商店，所以 Slice 可以用作
返回。 A Slice 只知道是否有下一个 Slice
可用，

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:

排序选项也通过Pageable实例处理。

Sorting options are handled through the Pageable instance too.

这篇关于按日期排序Desc限制Spring数据JPA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！