我如何在Spring数据存储库中编写SELECT TOP 25 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:

一个简单的问题,因为我确信这是愚蠢的。我有以下查询,我可以在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]

5 个解决方案

#1

Pure SQL

Use "Limit"

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

JPA

List<Arcust> findTop25ByArcustnoLessThanOrderByArcustnoDesc(String arcustno);

#2

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

#3

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.

我不确定拉克什的答案是否正确。他似乎在编写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(“从设备d中选择d,其中d.deviceName如CONCAT('%',:deviceName,'%')和d.deviceId不在: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不支持“限制”

#4

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)

在弹簧的数据你也可以使用原生查询:对于MySQL可能的工作:@Query(值= “SELECT * FROM ARCUST_BIG WHERE arcustno <= '300000' ORDER BY arcustno DESC极限0,25”,nativeQuery =真)

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 - 但我也无法使它工作,所以上面的“解决方法”可能会有所帮助。

#5

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);

#1