How to use LIMIT in spring within sql query?
My response is perhaps very late, but if I can help anyone else, you can use the nativeQuery like this :
@Query(value="SELECT * from person p WHERE p.company_id = :id ORDER BY p.name DESC LIMIT 3", nativeQuery = true)
LIMIT
is not part of JPQL. The mechanism available in current release version (1.6.0.RELEASE as of the time of writing) is pagination:
interface PersonRepository extends Repository<Person, Long> {
@Query("...")
List<Person> findLimited(..., Pageable pageable);
}
This can then be used as follows:
repository.findLimited(..., new PageRequest(0, 10));
This will return the first ten results of the query defined in the @Query
annotation.
The current master branch of Spring Data JPA already contains a new feature that would allow you to rewrite above query as follows:
interface PersonRepository extends Repository<Person, Long> {
List<Person> findTop3ByCompanyOrderByName(Company company);
}
As of version 1.7.0.M1 (feature already available in snapshots) the query derivation mechanism will understand Top
and First
in the subject clause To limit the number of results returned.
Update
as new PageRequest
deprecated you need to usePageRequest.of(0, 10)
instead