spring - Spring Data JPA 中的订单按日期描述限制

Question

我尝试使用限制查询来限制查询结果。如果没有限制，查询将按预期工作。

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

但是当我尝试使用限制(记录数)来限制记录时，如下所示，

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

从上面的查询中我收到以下错误，

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 data jpa 查询中使用 order by limit 查询？

Answer 1

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

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

Pageable 封装了排序和分页功能，如 spring data jpa doc说:

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.

因此，您可以使用:

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

或者:

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

另外:

Sorting options are handled through the Pageable instance too.