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