Run and Paginate JPQL Queries Without Deploying: IntelliJ IDEA's JPA Console

The problem: verifying JPQL without a deploy cycle

Writing a JPQL or HQL query in a repository method and then starting the whole application just to see whether it returns the right rows is slow. IntelliJ IDEA Ultimate ships a JPA Console that executes queries directly against your configured persistence unit, so you can validate entity names, join paths, and pagination behavior in seconds. The trade-off: the console runs against a live datasource, so it must point at a development database.

What you need before opening the console

The JPA Console is an Ultimate-edition feature; it does not exist in Community Edition. Beyond that, three things must be in place:

1. JPA/Hibernate on the classpath. The console uses the Hibernate version from your project dependencies, not one bundled with the IDE. Syntax your Hibernate version rejects will fail here exactly as it would at runtime — which is the point.
2. A recognized persistence unit. Open the Persistence tool window and confirm your unit (from persistence.xml, a Spring configuration, or hibernate.cfg.xml) is listed with your entities under it. If entities are missing, the mapping isn't being picked up.
3. An assigned datasource. In the Persistence tool window, right-click the persistence unit and use the assign-datasource action to link it to a datasource defined in the Database tool window. Without this, the IDE cannot validate mappings against the real schema and the console has nothing to execute against. Use a development database.

Exact menu labels and tool-window names vary between IDE versions, so verify against your installed build rather than a memorized path.

Running your first query

In the Persistence tool window, right-click the persistence unit and choose the console option. A scratch editor opens with completion for entity names and properties derived from your mappings. Start with something trivial:

select o from Order o where o.status = 'PENDING'

Run it (the standard execute shortcut for query consoles in the IDE). Results render in the data grid with one row per entity and columns for mapped fields. Two things to check immediately:

• Column mapping is correct. Field values line up with what you'd see querying the table directly in the Database tool window. A mismatch usually means a stale or wrong datasource assignment.
• Lazy associations behave as configured. Accessing a lazily loaded collection in the result view either triggers a load within the console's session or shows the proxy state — either way, you'll see mapping problems here before they become LazyInitializationException in production code.

Verifying pagination logic before wiring it into a repository

The console's result grid pages through large result sets without loading everything into memory, but that is display paging — it does not prove your query-level pagination is correct. Query-level pagination is what matters when you later call setFirstResult/setMaxResults (or use Spring Data's Pageable) in application code.

A practical verification pattern in the console:

select o from Order o order by o.createdAt desc

Note the total row count and the ordering. Then run the equivalent of page 1 and page 2 by adding criteria that reproduce your offset logic, or — if you want to test the exact JPA API calls — use a small test class or a scratch that executes:

var query = em.createQuery(
  "select o from Order o order by o.createdAt desc", Order.class);
query.setFirstResult(20);  // offset: skip first 20 rows
query.setMaxResults(20);   // page size
var page2 = query.getResultList();

Run page 1 (offset 0) and page 2 (offset 20) and confirm three things: each page returns the expected row count, no row appears on both pages, and the concatenation matches the unpaginated ordering. The most common pagination bug — unstable ordering causing duplicates or gaps across pages — shows up here immediately if createdAt has ties. Fix it by adding a unique tiebreaker (order by o.createdAt desc, o.id desc) and re-run both pages to confirm.

Deliberately breaking it: confirming validation works

Before trusting the console's green results, prove its validation is active. Reference a class that is not a mapped entity:

select x from NotAnEntity x

You should get a mapping error naming the unmapped type. If instead you get a generic SQL error or silent failure, the console is not bound to your persistence unit correctly — go back to the datasource assignment step.

Common failures and what they mean

Safety and limits

The console executes against the configured datasource directly. SELECT statements are safe, but JPQL UPDATE and DELETE statements will modify real rows, and there is no built-in undo — treat any DML in the console as a production-style migration: run a SELECT with the same WHERE clause first to confirm the affected row set. Keep the assigned datasource pointed at a development database permanently, so a moment of inattention cannot become an incident.

The console also does not replace an integration test. It validates mapping and query logic against one datasource; it does not exercise transaction boundaries, repository wiring, or serialization. Use it to shorten the feedback loop while writing the query, then still cover the repository method with a test.