JPQL 中的 LIMIT 子句替代是什么? [英] What is the LIMIT clause alternative in JPQL?

问题描述

我正在使用在 JPQL 中实现的 PostgreSQL 查询.

I'm working with PostgreSQL query implementing in JPQL.

这是一个很好的原生 psql 查询示例，

This is a sample native psql query which works fine,

SELECT * FROM students ORDER BY id DESC LIMIT 1;

JPQL 中的相同查询不起作用，

The same query in JPQL doesnt work,

@Query("SELECT s FROM Students s ORDER BY s.id DESC LIMIT 1")

Students getLastStudentDetails();

似乎 LIMIT 子句在 JPQL 中不起作用.

seems like LIMIT clause doesn't work in JPQL.

根据 JPA 文档，我们可以使用 setMaxResults/setFirstResult，谁能告诉我如何在上面的查询中使用它?

According to JPA documentation we can use setMaxResults/setFirstResult, Can anyone tell me how can I use that in my above query?

推荐答案

您使用的 JPQL 不支持这样的限制结果.使用本机 JPQL 时，您应该使用 setMaxResults 来限制结果.

You are using JPQL which doesn't support limiting results like this. When using native JPQL you should use setMaxResults to limit the results.

然而，您使用的是 Spring Data JPA，这基本上使它很容易做到.请参阅此处在有关如何根据查询限制结果的参考指南中.在您的情况下， find 方法将完全符合您的要求.

However you are using Spring Data JPA which basically makes it pretty easy to do. See here in the reference guide on how to limit results based on a query. In your case the following, find method would do exactly what you want.

findFirstByOrderById();

您还可以在查询中使用 Pageable 参数而不是 LIMIT 子句.

You could also use a Pageable argument with your query instead of a LIMIT clause.

@Query("SELECT s FROM Students s ORDER BY s.id DESC")
List<Students> getLastStudentDetails(Pageable pageable);

然后在你的调用代码中做这样的事情(如此处 在参考指南中).

Then in your calling code do something like this (as explained here in the reference guide).

getLastStudentDetails(PageRequest.of(0,1));

两者都应该产生相同的结果，而无需求助于纯 SQL.

Both should yield the same result, without needing to resort to plain SQL.

这篇关于JPQL 中的 LIMIT 子句替代是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！