Limit number of results in JPQL

Question

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 */

score 72 · Accepted Answer · edited Jun 23 '15 at 14:11

72

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.

edited Jun 23 '15 at 14:11

Paŭlo Ebermann

73,284
20
146
210

answered Aug 16 '10 at 16:43

Nayan Wadekar

11,444
4
50
73

5

"It will automatically create native query in back-end to retrieve specific number of results" - only if the database and the dialect supports it. If not, JPA will query all results and filter them in-memory. The developer must be aware of that, since it may affect the performance a lot. What's more, with some (more complicated) queries, JPA doesn't even try to use the LIMIT/TOP functionality, for instance when it would lead to wrong results. – Adam Dyga Nov 28 '13 at 12:33
1

@AdamDyga Yes, it's implementation specific, some provide them explicitly. Thanks for adding information. – Nayan Wadekar Nov 29 '13 at 07:53

score 21 · Answer 2 · edited Jul 12 '20 at 12:53

21

You can set an offset too using setFirstResult()

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

edited Jul 12 '20 at 12:53

UkFLSUI

5,509
6
32
47

answered Jun 11 '12 at 10:09

Tharaka

2,883
1
21
13

score 1 · Answer 3 · answered Apr 09 '21 at 14:02

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

Limit number of results in JPQL

3 Answers3

Linked

Related