我将如何在 Spring 数据存储库中编写 SELECT TOP 25 sql 查询 [英] How would I write SELECT TOP 25 sql query in Spring data repository

问题描述

一个简单的问题，因为我确定这是愚蠢的.我可以在 NetBeans sql 命令窗口中执行以下查询:

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:

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

我的目标是将它放在我的 ArcustRepository 类中:

My goal is to put put it in my ArcustRepository class:

公共接口 ArcustRepository 扩展 JpaRepository {

public interface 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 启动我的服务时返回:

However, that findBytop query doesn't seem to work and when I start my service with tomcat7 returns this:

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]

推荐答案

# Pure SQL

使用限制"

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

注意:JPA 支持使用方法 createNativeQuery() 或使用注释 @NamedNativeQuery 创建 Native 查询JPA Native Query 选择和转换对象对象

Note: JPA supports creation of the Native query by using method createNativeQuery() OR by using the annotation @NamedNativeQuery JPA Native Query select and cast object object

# JPA

List<Arcust> findTop25ByArcustnoLessThanOrderByArcustnoDesc(String arcustno);

这篇关于我将如何在 Spring 数据存储库中编写 SELECT TOP 25 sql 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！