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(new PageableRequest(0,1));

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

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

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