Java 如何在 sql 查询中在 spring 中使用 LIMIT？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/24367572/
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
How to use LIMIT in spring within sql query?
提问by membersound
Somehow I cannot use the LIMITqualifier within a sql query using Spring-data-jpa:
不知何故，我无法LIMIT在 sql 查询中使用限定符Spring-data-jpa：
@Query("SELECT p from Person p WHERE p.company.id = :id ORDER BY p.name DESC LIMIT 3")
What is wrong here?
这里有什么问题？
Caused by: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: Limit near line 1, column 146
at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:91)
at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:109)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:304)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:203)
at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:158)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:126)
at org.hibernate.engine.query.spi.HQLQueryPlan.<init>(HQLQueryPlan.java:88)
at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:190)
at org.hibernate.internal.AbstractSessionImpl.getHQLQueryPlan(AbstractSessionImpl.java:301)
at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:236)
at org.hibernate.internal.SessionImpl.createQuery(SessionImpl.java:1800)
at org.hibernate.jpa.spi.AbstractEntityManagerImpl.createQuery(AbstractEntityManagerImpl.java:328)
... 48 more
采纳答案by Oliver Drotbohm
LIMITis not part of JPQL. The mechanism available in current release version (1.6.0.RELEASE as of the time of writing) is pagination:
LIMIT不是 JPQL 的一部分。当前发行版（截至撰写本文时为 1.6.0.RELEASE）中可用的机制是分页：
interface PersonRepository extends Repository<Person, Long> {
@Query("...")
List<Person> findLimited(..., Pageable pageable);
}
This can then be used as follows:
然后可以按如下方式使用：
repository.findLimited(..., new PageRequest(0, 10));
This will return the first ten results of the query defined in the @Queryannotation.
这将返回@Query注释中定义的查询的前十个结果。
The current master branch of Spring Data JPA already contains a new feature that would allow you to rewrite above query as follows:
Spring Data JPA 的当前 master 分支已经包含一个新功能，允许您按如下方式重写上述查询：
interface PersonRepository extends Repository<Person, Long> {
List<Person> findTop3ByCompanyOrderByName(Company company);
}
As of version 1.7.0.M1 (feature already availablein snapshots) the query derivation mechanism will understand Topand Firstin the subject clause To limit the number of results returned.
从 1.7.0.M1 版本开始（功能已经在快照中可用），查询派生机制将理解Top并First在主题子句中限制返回结果的数量。
Updateas new PageRequestdeprecated you need to usePageRequest.of(0, 10)instead
更新为new PageRequest已弃用，您需要PageRequest.of(0, 10)改用
回答by Augusto Araujo Peres
An alternative is to use HQL instead of the Spring-data-jpa and then use setMaxResults().
另一种方法是使用 HQL 而不是 Spring-data-jpa，然后使用setMaxResults().
For example:
例如：
SessionFactory sessionFactory = new Configuration().configure("hibernate.cfg.xml").buildSessionFactory();
Session session = sessionFactory.openSession();
session.beginTransaction();
Query query = session.createQuery("FROM Person WHERE company.id = :id ORDER BY name DESC");
query.setParameter("id", id);
query.setMaxResults(3); // this replaces the LIMIT in the query
List<Person> personList = query.getResultList();
session.getTransaction().commit();
session.close();
回答by Sofiane
My response is perhaps very late, but if I can help anyone else, you can use the nativeQuesry like this :
我的回复可能很晚了，但是如果我可以帮助其他人，您可以像这样使用 nativeQuesry：
@Query(value="SELECT p from (replace with the person table name in DB) p WHERE p.company.id = :id ORDER BY p.name DESC LIMIT 3", nativeQuery = true)
