Hibernate JPQL Pagination in IntelliJ IDEA: A Decision Guide

The problem: page numbers do not limit database rows by themselves

You need to return a slice of a large result set, such as rows 41–60 from a JPQL query. A common mistake is assuming that IDE autocompletion for setFirstResult and setMaxResults means the database will only fetch those rows. The useful takeaway: pagination is a repository-layer implementation detail. IntelliJ IDEA can highlight JPQL/HQL and complete JPA methods, but the running Hibernate session and the SQL dialect decide what SQL is sent to the database.

Decision and constraints

Choose a pagination approach when you must expose stable, bounded result pages from a JPA repository. Constraints include the Hibernate ORM version resolved by your build, the IntelliJ IDEA release used by the team, and whether the UI needs total row counts for pagination controls.

This guide assumes Hibernate ORM 5.5 or newer and IntelliJ IDEA 2023.3 or newer for the completion behavior described. Older IDE releases, such as 2020.3, may not recognize newer Hibernate 6 paging APIs. Verify completion and compilation in your own project before standardizing on an approach.

JPQL is the Java Persistence Query Language; HQL is Hibernate’s closely related query language. Both are object-oriented query strings, not SQL. A dialect is the Hibernate component that translates JPQL/HQL into database-specific SQL, including LIMIT/OFFSET or vendor equivalents.

Supported options compared

Option	Where paging is expressed	IDE assistance	Best fit
JPA Query.setFirstResult / setMaxResults	On a JPQL/HQL Query object in the repository	Syntax highlighting and completion for JPQL/HQL strings; compile-time checks when JPA annotations are used	Simple repositories with fixed or moderately dynamic queries
JPA Criteria API	Programmatically on CriteriaQuery and TypedQuery	Method completion; no JPQL string to inspect	Dynamic filters where string concatenation is error-prone
Spring Data Pageable	Repository method parameter; Spring Data applies paging and an optional count query	Completion for Pageable, Page, and Sort	Spring applications that need total counts and sorting conventions
Hibernate org.hibernate.query.Query API	Same setFirstResult/setMaxResults methods on the native Hibernate Query interface	Completion depends on IDE and Hibernate version; newer APIs may be unsupported in older releases	Code already using Hibernate-specific features beyond JPA

Trade-offs

• Direct JPA paging is explicit and easy to read, but it does not return a total count. If the UI needs page 3 of 12, you must run a separate count query.
• Criteria API avoids fragile JPQL string building, but it is more verbose and harder to log as a readable query.
• Spring Data Pageable reduces boilerplate and integrates with Spring MVC, but it can trigger an extra count query and may fetch more than expected when join fetches are present.
• Large offsets remain slow in most databases regardless of the Java API. Keyset pagination is often faster for deep pages, but that is a query design decision outside IDE assistance.

Concrete implementation and validation

The following repository method uses JPA paging directly. It is intentionally plain so the IDE’s JPQL/HQL assistance and the compiler can both see the query shape.

package com.example.repository;

import jakarta.persistence.EntityManager;
import jakarta.persistence.PersistenceContext;
import jakarta.persistence.TypedQuery;
import java.util.List;

public class EmployeeRepository {

    @PersistenceContext
    private EntityManager entityManager;

    public List<Employee> findPage(int pageNumber, int pageSize) {
        if (pageNumber < 0 || pageSize <= 0) {
            throw new IllegalArgumentException("pageNumber must be >= 0 and pageSize must be > 0");
        }

        String jpql = "SELECT e FROM Employee e ORDER BY e.id";
        TypedQuery<Employee> query = entityManager
                .createQuery(jpql, Employee.class)
                .setFirstResult(pageNumber * pageSize)
                .setMaxResults(pageSize);

        return query.getResultList();
    }
}

This example uses Jakarta Persistence imports as in Hibernate 6. For Hibernate 5.6 with javax.persistence, change the imports accordingly.

Before relying on completion behavior, confirm the Hibernate version your build resolves. Run this in the project root terminal:

# Maven
mvn dependency:tree -Dincludes=org.hibernate.orm:hibernate-core

# Gradle
./gradlew dependencies --configuration runtimeClasspath | grep hibernate-core

Required permissions are read access to the project and local dependency caches; network access is needed if dependencies are not already cached. Expected check: the output shows a single hibernate-core version, such as a 6.x or 5.6.x release. Risk: the command may download dependencies and can fail in offline environments.

Add a focused repository test that asserts page size and ordering. Run it with your build tool:

# Maven
mvn -Dtest=EmployeeRepositoryTest test

# Gradle
./gradlew test --tests "com.example.repository.EmployeeRepositoryTest"

Expected check: the test passes and the build reports no compilation errors on the pagination code. Risk: the test may touch a real database; use a test profile or an in-memory database.

To validate that paging reaches the database, enable Hibernate SQL logging in a development profile:

# src/main/resources/application.properties or a dev profile
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.orm.jdbc.bind=TRACE

Expected check: when findPage(2, 20) runs, the logged SQL for the JPQL query contains a database limit/offset construct or bound parameters for it. Risk: verbose logs; do not enable this in production.

In IntelliJ IDEA, open the repository class and confirm completion offers setFirstResult and setMaxResults on the query object. If completion is missing, treat the IDE and Hibernate version pairing as unsupported rather than changing the repository contract.

If your team uses DataGrip, copy the logged SQL into a console and inspect the result-set metadata and row count. That checks the SQL Hibernate produced; it does not verify that Java mapped the rows to the correct entities.

Limitations and a practical result check

IDE assistance is static. It does not execute the query, apply the dialect, or guarantee that the database honors the requested page. Hibernate generates the final SQL, so the only reliable check is to observe the SQL and the returned list size.

A practical check is: compile the repository, run the focused test, and confirm the logged SQL includes a limit/offset clause for the target dialect. If the row count matches pageSize and the first row matches the expected offset, the pagination path is working for that query. Remember that setFirstResult is zero-based, so page 2 with size 20 starts at row 40, not row 41.

Remaining limitations include separate count queries for total pages, potential N+1 loads if the page contains lazy associations, and slower deep offsets. Those are application design concerns, not IDE features.