Java JPQL 中的 LIMIT 子句替代是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44565820/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
What is the LIMIT clause alternative in JPQL?
提问by Madhu
I'm working with PostgreSQL query implementing in JPQL.
我正在使用在 JPQL 中实现的 PostgreSQL 查询。
This is a sample native psql query which works fine,
这是一个很好的原生 psql 查询示例，
SELECT * FROM students ORDER BY id DESC LIMIT 1;
The same query in JPQL doesnt work,
JPQL 中的相同查询不起作用，
@Query("SELECT s FROM Students s ORDER BY s.id DESC LIMIT 1")
Students getLastStudentDetails();
seems like LIMIT clause doesn't work in JPQL.
似乎 LIMIT 子句在 JPQL 中不起作用。
According to JPA documentation we can use setMaxResults/setFirstResult, Can anyone tell me how can I use that in my above query?
根据我们可以使用的 JPA 文档setMaxResults/setFirstResult，谁能告诉我如何在上面的查询中使用它？
采纳答案by M. Deinum
You are using JPQL which doesn't support limiting results like this. When using native JPQL you should use setMaxResultsto limit the results.
您使用的 JPQL 不支持这样的限制结果。使用本机 JPQL 时，您应该使用setMaxResults来限制结果。
However you are using Spring Data JPA which basically makes it pretty easy to do. See herein 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.
但是，您使用的是 Spring Data JPA，这基本上使它很容易做到。有关如何根据查询限制结果的参考指南，请参见此处。在您的情况下， find 方法将完全符合您的要求。
findFirstByOrderById();
You could also use a Pageableargument with your query instead of a LIMITclause.
您还可以Pageable在查询中使用参数而不是LIMIT子句。
@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 herein the reference guide).
然后在你的调用代码做这样的事情（如解释这里的参考指南中）。
getLastStudentDetails(new PageRequest(0,1));
Both should yield the same result, without needing to resort to plain SQL.
两者都应该产生相同的结果，而无需求助于纯 SQL。
回答by dazito
As stated in the comments, JPQL does not support the LIMITkeyword.
如评论中所述，JPQL 不支持LIMIT关键字。
You can achieve that using the setMaxResultsbut if what you want is just a single item, then use the getSingleResult- it throws an exception if no item is found.
您可以使用 the 来实现，setMaxResults但如果您想要的只是单个项目，则使用getSingleResult- 如果找不到项目，它会引发异常。
So, your query would be something like:
因此，您的查询将类似于：
TypedQuery<Student> query = entityManager.createQuery("SELECT s FROM Students s ORDER BY s.id DESC", Student.class);
query.setMaxResults(1);
If you want to set a specific start offset, use query.setFirstResult(initPosition); too
如果要设置特定的起始偏移量，请使用query.setFirstResult(initPosition); 也
回答by Rajib Das Gupta
You can use something like this:
你可以使用这样的东西：
@Repository
public interface ICustomerMasterRepository extends CrudRepository<CustomerMaster, String>
{
@Query(value = "SELECT max(c.customer_id) FROM CustomerMaster c ")
public String getMaxId();
}
回答by Manish Joshi
Hello for fetching single row and using LIMIT in jpql we can tell the jpql if it's a native query.
您好，获取单行并在 jpql 中使用 LIMIT 我们可以告诉 jpql 它是否是本机查询。
( using - nativeQuery=true )
（使用 - nativeQuery=true ）
Below is the use
下面是使用
@Query("SELECT s FROM Students s ORDER BY s.id DESC LIMIT 1", nativeQuery=true)
Students getLastStudentDetails();
回答by tk_
Hardcode the pagination(new PageRequest(0, 1)) to achieve fetch only one record.
硬编码分页（new PageRequest(0, 1)）以实现仅获取一条记录。
@QueryHints({ @QueryHint(name = "org.hibernate.cacheable", value = "true") })
@Query("select * from a_table order by a_table_column desc")
List<String> getStringValue(Pageable pageable);
you have to pass new PageRequest(0, 1)to fetch records and from the list fetch the first record.
您必须传递new PageRequest(0, 1)以获取记录并从列表中获取第一条记录。
回答by greenhorn
JPQL does not allow to add the limitkeyword to the query generated by the HQL. You would get the following exception.
JPQL 不允许在 HQL 生成的查询中添加limit关键字。你会得到以下异常。
org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: LIMIT near line 1
org.hibernate.hql.internal.ast.QuerySyntaxException：意外标记：第 1 行附近的 LIMIT
But don't worry there is an alternative to use the limit keyword in the query generated by the HQL by using the following steps.
但是不要担心，通过使用以下步骤，可以在 HQL 生成的查询中使用 limit 关键字的替代方法。
Sort.by(sortBy).descending() // fetch the records in descending order
pageSize = 1 // fetch the first record from the descending order result set.
Sort.by(sortBy).descending() // 以降序获取记录
pageSize = 1 // 从降序结果集中获取第一条记录。
Refer the following service class
参考以下服务类
Service:
服务：
@Autowired
StudentRepository repository;
public List<Student> getLastStudentDetails(Integer pageNo, Integer pageSize, String sortBy)
{
Integer pageNo = 0;
Integer pageSize = 1;
String sortBy = "id";
Pageable paging = PageRequest.of(pageNo, pageSize, Sort.by(sortBy).descending());
Slice<Student> pagedResult = repository.findLastStudent(paging);
return pagedResult.getContent();
}
Your repository interface should implement the PagingAndSortingRepository
你的存储库接口应该实现PagingAndSortingRepository
Repository:
存储库：
public interface StudentRepository extends JpaRepository<Student,Long>, PagingAndSortingRepository<Student,Long>{
@Query("select student from Student student")
Slice<Student> findLastStudent(Pageable paging);
}
This will add the limit keyword to you query which you can see in the console. Hope this helps.
这会将 limit 关键字添加到您可以在控制台中看到的查询中。希望这可以帮助。
