How to find top N elements in Spring Data Jpa?

Question

In Spring Data Jpa to get first 10 rows I can do this findTop10By...(). In my case the number or rows is not defined and comes as a parameter.

Is there something like findTopNBy...(int countOfRowsToGet)?

Answer 1

Here is another way without native query. I added Pageable as a parameter to the method in the interface.

findAllBySomeField(..., Pageable pageable)

I call it like this:

findAllBySomeField(..., PageRequest.of(0, limit)) //  get first N rows
findAllBySomeField(..., Pageable.unpaged()) //  get all rows

Answer 2

Did it by using pagination, as described in the first answer. Just adding a more explicit example.

This example will give you the first 50 records ordered by id.

Repository:

@Repository
public interface MyRepository extends JpaRepository<MyEntity, String> {
    Page<MyEntity> findAll(Pageable pageable);  
}

Service:

@Service
public class MyDataService {

    @Autowired
    MyRepository myRepository;

    private static final int LIMIT = 50;
    
    public Optional<List<MyEntity>> getAllLimited() {
        Page<MyEntity> page = myRepository.findAll(PageRequest.of(0, LIMIT, Sort.by(Sort.Order.asc("id"))));
        return Optional.of(page.getContent());
    }
}

Found the original idea here: https://itqna.net/questions/16074/spring-data-jpa-does-not-recognize-sql-limit-command (which will also link to another SO question btw)

Answer 3

I don't know of a way to do exactly what you want, but if you are open to using @Query in your JPA repository class, then a prepared statement is one alternative:

@Query("SELECT * FROM Entity e ORDER BY e.id LIMIT :limit", nativeQuery=true)
Entity getEntitiesByLimit(@Param("limit") int limit);