Introduction

JPQL and HQL do not support a LIMIT keyword. Instead, you limit query results by calling setMaxResults() on the Query or TypedQuery object. For pagination, combine it with setFirstResult() to skip a specific number of rows. These methods translate into the appropriate SQL dialect (LIMIT, TOP, ROWNUM, or FETCH FIRST) depending on the underlying database.

Why LIMIT Does Not Exist in JPQL or HQL

Both JPQL and HQL are designed to be database-agnostic. SQL's LIMIT clause is not part of the ANSI SQL standard. It originated in MySQL and PostgreSQL, and other databases use different syntax for the same concept:

Because JPQL and HQL run on top of Hibernate, which supports all of these databases, they delegate pagination to the API level rather than embedding vendor-specific syntax into the query string.

Basic Usage: setMaxResults

The simplest way to limit results is setMaxResults(). This tells Hibernate the maximum number of rows to return.

HQL Example

1Session session = sessionFactory.openSession();
2Query<Employee> query = session.createQuery(
3    "FROM Employee e WHERE e.department = :dept", Employee.class);
4query.setParameter("dept", "Engineering");
5query.setMaxResults(10);
6List<Employee> employees = query.getResultList();

JPQL Example

1EntityManager em = entityManagerFactory.createEntityManager();
2TypedQuery<Employee> query = em.createQuery(
3    "SELECT e FROM Employee e WHERE e.department = :dept", Employee.class);
4query.setParameter("dept", "Engineering");
5query.setMaxResults(10);
6List<Employee> employees = query.getResultList();

Both examples produce the same effect: Hibernate generates a SQL query with the appropriate limit clause for the configured database dialect.

Pagination with setFirstResult and setMaxResults

For paginated results, combine setFirstResult() (the zero-based offset) with setMaxResults() (the page size).

1int pageNumber = 3;
2int pageSize = 20;
3
4TypedQuery<Product> query = em.createQuery(
5    "SELECT p FROM Product p ORDER BY p.createdAt DESC", Product.class);
6query.setFirstResult((pageNumber - 1) * pageSize); // skip first 40 rows
7query.setMaxResults(pageSize);                       // return 20 rows
8List<Product> page = query.getResultList();

This pattern works identically in both JPQL and HQL. The ORDER BY clause is important for pagination because without a deterministic sort order, the same row could appear on multiple pages or be skipped entirely.

Spring Data JPA: Pageable

If you are using Spring Data JPA, you rarely need to call setMaxResults() directly. The Pageable abstraction handles pagination automatically.

1public interface ProductRepository extends JpaRepository<Product, Long> {
2
3    Page<Product> findByCategory(String category, Pageable pageable);
4}

Usage in a service:

1Pageable pageable = PageRequest.of(2, 20, Sort.by("createdAt").descending());
2Page<Product> page = productRepository.findByCategory("Electronics", pageable);
3
4List<Product> products = page.getContent();
5long totalElements = page.getTotalElements();
6int totalPages = page.getTotalPages();

Under the hood, Spring Data JPA calls setFirstResult() and setMaxResults() for you.

Criteria API Alternative

For dynamic queries where JPQL strings become unwieldy, the JPA Criteria API supports the same pagination methods.

1CriteriaBuilder cb = em.getCriteriaBuilder();
2CriteriaQuery<Order> cq = cb.createQuery(Order.class);
3Root<Order> root = cq.from(Order.class);
4cq.select(root).where(cb.equal(root.get("status"), "PENDING"));
5cq.orderBy(cb.desc(root.get("createdAt")));
6
7TypedQuery<Order> query = em.createQuery(cq);
8query.setFirstResult(0);
9query.setMaxResults(25);
10List<Order> orders = query.getResultList();

The pagination calls are on the TypedQuery object, not on the CriteriaQuery. This is a common source of confusion.

Named Queries with Limits

Named queries defined in annotations can also be paginated at call time:

1@Entity
2@NamedQuery(
3    name = "Employee.findByDepartment",
4    query = "SELECT e FROM Employee e WHERE e.department = :dept ORDER BY e.lastName"
5)
6public class Employee {
7    // fields
8}

1TypedQuery<Employee> query = em.createNamedQuery(
2    "Employee.findByDepartment", Employee.class);
3query.setParameter("dept", "Sales");
4query.setFirstResult(0);
5query.setMaxResults(50);
6List<Employee> results = query.getResultList();

You do not embed a limit in the named query string itself. The limit is always applied programmatically.

Comparison of Approaches

Performance Considerations

Deep Pagination is Expensive

Using a large offset with setFirstResult() forces the database to scan and discard rows before the offset point. Requesting page 1000 with 20 rows per page means the database processes 20,000 rows and discards 19,980 of them.

For large datasets, consider keyset pagination (also called seek pagination) instead:

1TypedQuery<Product> query = em.createQuery(
2    "SELECT p FROM Product p WHERE p.createdAt < :lastSeen ORDER BY p.createdAt DESC",
3    Product.class);
4query.setParameter("lastSeen", lastSeenTimestamp);
5query.setMaxResults(20);
6List<Product> nextPage = query.getResultList();

This avoids the offset entirely by using a WHERE clause that starts from the last seen record.

Always Include ORDER BY

Without an explicit ORDER BY, the database does not guarantee row order. Paginated results without sorting can return duplicates or miss rows across pages.

Count Queries Add Overhead

Spring Data's Page return type executes a separate COUNT query to determine total elements. If you do not need the total count, use Slice instead:

Slice<Product> slice = productRepository.findByCategory("Electronics", pageable);
boolean hasNext = slice.hasNext();

Common Pitfalls

• Writing LIMIT directly in a JPQL or HQL string. This causes a parser error. Use setMaxResults() instead.
• Forgetting ORDER BY with pagination. Without a deterministic sort, rows can appear on multiple pages or be skipped.
• Applying pagination to the CriteriaQuery instead of the TypedQuery. The setMaxResults() and setFirstResult() methods belong on TypedQuery, not CriteriaQuery.
• Using deep offsets on large tables. Performance degrades linearly with offset size. Switch to keyset pagination for large datasets.
• Executing unnecessary count queries. Use Slice instead of Page in Spring Data when the total count is not needed.
• Mixing Hibernate Session API with JPA EntityManager. While both support setMaxResults(), the return types differ (list() vs getResultList()). Pick one API and stay consistent.

Summary

• JPQL and HQL do not have a LIMIT keyword. Use setMaxResults() to cap the number of returned rows.
• Combine setFirstResult() with setMaxResults() for offset-based pagination.
• Spring Data JPA wraps these calls behind Pageable and PageRequest for convenience.
• Always include an ORDER BY clause when paginating to ensure consistent results across pages.
• For large datasets, prefer keyset pagination over deep offsets to avoid performance degradation.
• The Criteria API also supports pagination through the same TypedQuery methods.