Test JPQL Pagination in IntelliJ IDEA JPA Console Before It Hits Production

The concrete problem is pagination that works in dev and breaks in staging. A JPQL query with setFirstResult and setMaxResults looks portable, but Hibernate translates it to different SQL depending on the database dialect. The same query can emit LIMIT/OFFSET on PostgreSQL and a different wrapper on Oracle or MySQL, and a missing join fetch can turn a single page into an N+1 storm only visible with real data.

The useful takeaway is to validate JPQL/HQL and its generated SQL against a real datasource inside the IDE, before the query is wired into a repository or service. IntelliJ IDEA Ultimate provides a JPA facet and a JPA Console for exactly that.

What the JPA facet gives you

The JPA facet detects persistence units from persistence.xml or from Spring Boot auto-configuration. Once a persistence unit is recognized, the IDE builds a live model of entities, mappings and named queries. That model powers code completion, inspections and navigation inside JPQL.

To use it you need a datasource configured in the Database tool window and a persistence unit that the IDE can resolve from the project classpath. The console is an Ultimate-edition feature; Community edition does not include JPA tooling.

Using the JPA Console for pagination checks

The JPA Console lets you run JPQL or HQL directly against a configured datasource. The console is bound to a persistence unit, so it bootstraps Hibernate with the entities on the project classpath and executes against the real schema.

Pagination is exercised by supplying first result and max results in the console UI. The console shows both the result set and the SQL that Hibernate generated for that execution. That makes it practical to compare dialect behavior without deploying.

Typical checks:

• Does the query use JOIN FETCH where needed to avoid N+1 selects on a page.
• Does the generated SQL use the paging strategy you expect for the target dialect.
• Do filters and ordering remain stable across pages.

Worked example: Order and Customer pagination

Consider two simple entities, Order with a many-to-one to Customer. A paginated query that loads orders with their customer might be written as:

SELECT o FROM Order o JOIN FETCH o.customer WHERE o.status = :status ORDER BY o.createdAt DESC

In the JPA Console you pick the persistence unit, open a new console tab for that unit, and paste the JPQL. Bind the :status parameter, set a first result and max results, and run.

Inspect the SQL tab. The SQL you see is produced by the Hibernate version on your project classpath combined with the dialect configured for the datasource. Switching the datasource dialect in the IDE and re-running the same JPQL shows how the paging wrapper changes. This is a diagnostic decision point: if the SQL changes in a way that adds a sort or a sub-select, you can adjust the JPQL or the mapping before it reaches a repository.

Verification you can do locally:

1. Create a minimal JPA project with two related entities and a datasource.
2. Confirm the JPA Console is available for the persistence unit in your installed edition.
3. Run a paginated JPQL query with first result and max results and inspect the generated SQL tab.
4. Repeat with a different datasource dialect and compare the emitted SQL shape.

Trade-offs and limitations

The console uses the IDE's own datasource connection and Hibernate bootstrap. Behavior that depends on container-managed transactions, Spring interceptors, filters, or second-level cache configuration may not be reproduced faithfully.

Dialect-generated pagination SQL depends on the Hibernate version on the project classpath, not just the IDE. Feature names and menu locations vary across IntelliJ IDEA releases, so verify against the version you have installed rather than assuming a specific UI path.

Do not rely on console timings for performance claims; execution time depends on local data volume and connection setup.

Because the tooling is Ultimate-only, teams on Community edition need an alternative, such as a small integration test with Testcontainers.

Actionable closing

Make the JPA Console part of query review. Before merging a repository method that pages, open the console for the target persistence unit, run the JPQL with realistic parameters and first/max results, and review the generated SQL for N+1 and dialect-specific paging. It is a quick, non-deployed way to catch the class of bugs that only appear with real data and a real dialect.