如何在Spring数据存储库中编写SELECT TOP 25 sql查询

java sql spring-data

2022-09-02 11:12:36

一个简短的问题，因为我确信这是愚蠢的。我有以下查询，我可以在NetBeans sql命令窗口中执行：

SELECT TOP 25 * FROM ARCUST_BIG  WHERE arcustno<='300000' ORDER BY arcustno DESC

我的目标是把它放在我的ArcustRepository类中：

公共接口 ArcustRepository extends 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);
}

但是，该 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]

答案 1

# 纯 SQL

使用“限制”

SELECT * FROM ARCUST_BIG 
WHERE arcustno<='300000' ORDER BY arcustno DESC Limit 0, 25

注： JPA 支持使用方法 createNativeQuery（） 或使用 JPA 本机查询选择和转换对象对象@NamedNativeQuery注释来创建本机查询

# 日本邮政

List<Arcust> findTop25ByArcustnoLessThanOrderByArcustnoDesc(String arcustno);

答案 2

我会说你需要

List<Arcust> findTop25ByArcustnoLessThanOrderByArcustnoDesc(String arcustno);

这将使用JPA，并且可能会在所有数据库上运行，将从SPRING JPA 1.7.0开始工作（Evans release train）

我实现了CrudRepository而不是JpaRepository。