Limit number of results in JPQL

How it is possible to limit the number of results retrieved from a database?

select e from Entity e /* I need only 10 results for instance */

Solution 1:

You can try like this giving 10 results to be fetched explicitly.

entityManager.createQuery(JPQL_QUERY)
             .setParameter(arg0, arg1)
             .setMaxResults(10)
             .getResultList();

It will automatically create native query in back-end to retrieve specific number of results, if the backend supports it, and otherwise do the limit in memory after getting all results.

Solution 2:

You can set an offset too using setFirstResult()

em.createNamedQuery("Entity.list")
  .setFirstResult(startPosition)
  .setMaxResults(length);

Solution 3:

If you are using Spring data JPA, then you can use Pageable/PageRequest to limit the record to 1 or any number you want. The first argument, is the page no, and the second argument is the number of records.

Pageable page = PageRequest.of(0, 1);
Entity e = entityRepository.findAll(page);

Make sure the entityRepostitory interface extends JpaRepository (which supports sorting and pagination).