Spring Data 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);

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

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

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 封装了排序和分页功能，如 spring 数据 jpa 文档 说:

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

在查询方法中添加Pageable实例，动态添加分页您静态定义的查询.Page 知道总共有多少元素和页面可用.它通过基础设施这样做触发计数查询来计算总数.作为这个根据所使用的商店可能会很昂贵，Slice 可以用作而是返回.一个 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.

所以，你可以使用:

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

还有:

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

Sorting options are handled through the Pageable instance too.

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