Java 我将如何在 Spring 数据存储库中编写 SELECT TOP 25 sql 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18252582/
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 would I write SELECT TOP 25 sql query in Spring data repository
提问by Metal Wing
A quick question, because I am sure this is something silly. I have the following query which I can execute in NetBeans sql command window:
一个简单的问题，因为我确信这是愚蠢的。我可以在 NetBeans sql 命令窗口中执行以下查询：
SELECT TOP 25 * FROM ARCUST_BIG WHERE arcustno<='300000' ORDER BY arcustno DESC
My goal is to put put it in my ArcustRepository class:
我的目标是把它放在我的 ArcustRepository 类中：
public interface ArcustRepository extends JpaRepository {
公共接口 ArcustRepository 扩展 JpaRepository {
Arcust findByPrimaryKey(String id);
@Query("SELECT COUNT(a) FROM Arcust a")
Long countAll();
@Query("SELECT TOP 25 a FROM Arcust a WHERE a.arcustno<='?1' ORDER BY a.arcustno DESC")
List<Arcust> findByTop(String arcustno);
}
However, that findBytop query doesn't seem to work and when I start my service with tomcat7 returns this:
但是，该 findBytop 查询似乎不起作用，当我使用 tomcat7 启动我的服务时返回：
2013-08-15 08:15:20 ERROR ContextLoader:319 - Context initialization failed
org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'arcustService': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private com.waudware.pics.repository.ArcustRepository com.waudware.pics.service.ArcustService.arcustRepository; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'arcustRepository': FactoryBean threw exception on object creation; nested exception is java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.waudware.pics.repository.ArcustRepository.findByTop(java.lang.String)!
Caused by: java.lang.IllegalArgumentException: Validation failed for query for method public abstract java.util.List com.waudware.pics.repository.ArcustRepository.findByTop(java.lang.String)!
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: 25 near line 1, column 12 [SELECT TOP 25 a FROM com.waudware.pics.domain.Arcust a WHERE a.arcustno<='?1' ORDER BY a.arcustno DESC]
采纳答案by Rakesh Soni
Pure SQL
纯SQL
Use "Limit"
使用“限制”
SELECT * FROM ARCUST_BIG
WHERE arcustno<='300000' ORDER BY arcustno DESC Limit 0, 25
JPA
日本特许经营协会
List<Arcust> findTop25ByArcustnoLessThanOrderByArcustnoDesc(String arcustno);
回答by Darren Parker
I'm not sure Rakesh's answer is correct. He seems to be writing SQL, not JPA query syntax.
I tried using LIMIT in a JPA @Query and got an exception saying "limit" is not recognized.
我不确定 Rakesh 的回答是否正确。他似乎在编写 SQL，而不是 JPA 查询语法。
我尝试在 JPA @Query 中使用 LIMIT 并得到一个异常，说“限制”不被识别。
@Query("select d from Device d where d.deviceName like CONCAT('%', :deviceName, '%') and d.deviceId not in :notList ORDER BY deviceName DESC Limit 1001")
@Query("select d from Device d where d.deviceName like CONCAT('%', :deviceName, '%') and d.deviceId not in :notList ORDER BY deviceName DESC Limit 1001")
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected token: Limit near line 1, column 162
Also, refer to this answer from Hari Shankar which says JPA doesn't support "limit": JPA doesn't support "limit"
另外，请参阅 Hari Shankar 的这个答案，其中说 JPA 不支持“限制”： JPA 不支持“限制”
回答by leoleo
As actually "limit" is not known in JPQL and also not in some database dialects (eg. MySQL knows it, Oracle does not) it can only be used in native queries and is database dependent.
实际上“限制”在 JPQL 和某些数据库方言中都不知道（例如 MySQL 知道，Oracle 不知道）它只能用于本机查询并且依赖于数据库。
In spring-data you can also use native queries:
For MySQl might work:@Query(value="SELECT * FROM ARCUST_BIG WHERE arcustno<='300000' ORDER BY arcustno DESC Limit 0, 25", nativeQuery=true)
在 spring-data 中，您还可以使用本机查询：对于 MySQl 可能有效：@Query(value="SELECT * FROM ARCUST_BIG WHERE arcustno<='300000' ORDER BY arcustno DESC Limit 0, 25", nativeQuery=true)
However for Oracle-DB you have to use something like:@Query(value="SELECT * FROM ARCUST_BIG WHERE rownum<=25 and arcustno<='300000' ORDER BY arcustno DESC", nativeQuery=true)
但是对于 Oracle-DB，您必须使用以下内容：@Query(value="SELECT * FROM ARCUST_BIG WHERE rownum<=25 and arcustno<='300000' ORDER BY arcustno DESC", nativeQuery=true)
As far as I know spring-data (from version 1.7 on) promises that Top/First will also work with @Query - but I could not get it working too, so the above "workaround" might be helpful.
据我所知，spring-data（从 1.7 版开始）承诺 Top/First 也可以与 @Query 一起使用 - 但我也无法使其正常工作，因此上述“解决方法”可能会有所帮助。
回答by Zamir
I would say you need
我会说你需要
List<Arcust> findTop25ByArcustnoLessThanOrderByArcustnoDesc(String arcustno);
That will use JPA and will probably work on all databases, will work starting SPRING JPA 1.7.0 (Evans release train)
这将使用 JPA 并且可能适用于所有数据库，将从 SPRING JPA 1.7.0（Evans 发布系列）开始工作
I implement CrudRepository and not JpaRepository
我实现了 CrudRepository 而不是 JpaRepository
回答by Kevin Sanchez
You can achieve this by setting nativeQuery to true:
您可以通过将 nativeQuery 设置为 true 来实现此目的：
@Query(nativeQuery = true,
value = "SELECT TOP 25 * FROM Arcust a WHERE a.arcustno <= :arcustno ORDER BY a.arcustno DESC")
List<Arcust> findByTop(String arcustno);
回答by davidddp
You can use in Repository your query, without TOP 25:
您可以在 Repository 中使用您的查询，无需 TOP 25：
@Query("SELECT a FROM Arcust a WHERE a.arcustno<='?1' ORDER BY a.arcustno DESC")
List<Arcust> findByTop(String arcustno, Pageable pageable);
}
And in the Service, use a PageRequest, returning a Page object:
在 Service 中，使用 PageRequest，返回一个 Page 对象：
Page<Arcust> arcusts = arcustRepository.findByTop(arcustno, PageRequest.of(0, 25));
List<Arcust> arcust = arcusts.getContent();
回答by Dapper Dan
I know this is 6 years old request , but this might help for someone still having similar issue.
我知道这是 6 年前的请求，但这可能对仍然有类似问题的人有所帮助。
In native sql we cat limit our result set using native query like
在本机 sql 中，我们使用本机查询来限制我们的结果集，例如
SELECT firstName, lastName, FROM Student ORDER BY studentNumber LIMIT 1;
But JPA does't recognize LIMITkeyword. instead it uses setMaxResults
但是 JPA 不识别LIMIT关键字。相反，它使用 setMaxResults
If you already setup your entity object, You can easily filter to Top 25 using JPA sql here is the syntax
如果您已经设置了实体对象，您可以使用 JPA sql 轻松过滤到前 25 名，这里是语法
entitymanager.createquery("select a from "your Entity Object" a where a.id =: parameter order by a.Id).setParameter("id",paramvalue).setMaxResults(25).getResultList();
In your case this should work
在你的情况下，这应该有效
entitymanager.createquery("SELECT a FROM Arcust a WHERE a.arcustno <= :'parameter' ORDER BY a.arcustno DESC).setparameter("arcustno",1).setMaxResults(25).getResultList();
