How would I write SELECT TOP 25 sql query in Spring data repository

Question

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

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

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

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]

Answer 1

# Pure SQL

Use "Limit"

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

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

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

I implement CrudRepository and not JpaRepository

Answer 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.

@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"

Answer 4

You can use in Repository your query, without 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:

Page<Arcust> arcusts = arcustRepository.findByTop(arcustno, PageRequest.of(0, 25));
List<Arcust> arcust = arcusts.getContent();

Answer 5

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.

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)

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)

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.

Answer 6

You can achieve this by setting nativeQuery to 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);

Answer 7

I know this is 6 years old request , but this might help for someone still having similar issue.

In native sql we cat limit our result set using native query like

SELECT firstName, lastName,  FROM Student ORDER BY studentNumber LIMIT 1;

But JPA does't recognize LIMIT keyword. instead it uses setMaxResults

If you already setup your entity object, You can easily filter to Top 25 using JPA sql here is the syntax

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