Java EE / Jakarta EE

JPQL, the Criteria API and Native Queries in Jakarta Persistence

Intermediate7 min readUpdated

JPQL is the object-oriented query language of Jakarta Persistence: it looks like SQL but names entities and their attributes rather than tables and columns. This guide covers the language from simple selects to fetch joins, aggregates, subqueries and DTO projections, then shows the Criteria API for dynamic queries, entity graphs for controlled loading, bulk updates and native SQL, and the performance and correctness traps — N+1, pagination with fetch joins, stale persistence contexts — that each brings.

What you will learn

  • Write JPQL selects with joins, fetch joins, conditions, ordering, grouping and subqueries
  • Return DTOs with constructor expressions and page results safely
  • Build dynamic, type-safe queries with the Criteria API and the static metamodel
  • Control loading with entity graphs and avoid N+1 selects
  • Use bulk updates, native queries and result-set mappings without corrupting the persistence context

Mapping entities gets data in and out one row at a time; queries are how you get sets of it. Jakarta Persistence offers three ways to ask the database a question: JPQL, a string language over the object model; the Criteria API, the same language built from Java objects; and native SQL for the cases the abstraction cannot cover. All three return managed entities or plain values through the same Query interfaces, and all three interact with the persistence context in ways that are easy to get wrong. The examples assume the Order, OrderLine, Customer and Product entities from the mapping guide.

JPQL in one page

JPQL reads like SQL but every identifier is a Java name: entity names (Order, not orders), attribute paths (o.customer.name), and Java literals. Aliases are mandatory in practice, comparisons are case-insensitive for keywords and case-sensitive for identifiers, and the result of a select is whatever you name in the SELECT clause — entities, attributes, aggregates or constructed objects.

TypedQuery<Order> q = em.createQuery("""
        select o
        from Order o
        join o.customer c
        where c.country = :country
          and o.status in :statuses
          and o.placedAt >= :since
        order by o.placedAt desc""", Order.class);

List<Order> recent = q.setParameter("country", "FR")
                      .setParameter("statuses", EnumSet.of(OrderStatus.NEW, OrderStatus.PAID))
                      .setParameter("since", Instant.now().minus(30, ChronoUnit.DAYS))
                      .setMaxResults(50)
                      .getResultList();

TypedQuery<T> is the version to use: it makes the result type explicit and avoids casts. Parameters are named (:country) or positional (?1); named ones are clearer and can be reused within the query. Collection-valued parameters expand to an IN list. Never build the query string from user input — besides injection, every distinct string is parsed and cached separately.

The WHERE clause supports the SQL operators you expect (=, <>, <, BETWEEN, LIKE with ESCAPE, IS NULL, IN, EXISTS), string, arithmetic and date functions (CONCAT, SUBSTRING, LOWER, LENGTH, SIZE, CURRENT_TIMESTAMP), and operators specific to the object model: IS EMPTY for collections, MEMBER OF for membership, TYPE(o) for inheritance hierarchies, TREAT(o AS RushOrder) for downcasting. Persistence 3.1 added LOCAL DATE, LOCAL TIME and LOCAL DATETIME for java.time values, EXTRACT(YEAR FROM o.placedAt), and the numeric functions CEILING, FLOOR, EXP, LN, POWER, ROUND and SIGN.

Joins and path navigation

A path through a single-valued association can be used directly (o.customer.name) and implies an inner join. Collection-valued associations must be joined explicitly:

select distinct o
from Order o
join o.lines l
join l.product p
where p.category = 'BOOKS'

distinct is needed because the join produces one row per matching line and JPQL, like SQL, would otherwise return the same order several times. left join keeps orders with no lines. join … on (JPA 2.1) adds conditions to the join itself. Joining an entity that has no mapped association (join Product p on p.sku = l.sku) is not in the 3.1 specification but Hibernate and EclipseLink both support it as an extension, which is useful with legacy schemas.

Fetch joins: the tool against N+1

The following loop issues one query for the orders and then one per order for its lines — the N+1 problem:

for (Order o : em.createQuery("select o from Order o", Order.class).getResultList()) {
    total += o.getLines().size();     // lazy collection: SELECT ... WHERE order_id = ? each time
}

A fetch join loads the association in the same statement and initialises it on the returned entities:

select distinct o
from Order o
left join fetch o.lines
left join fetch o.customer
where o.status = :status

Fetch joins do not take an alias in portable JPQL and cannot be filtered (a filtered fetch would leave the collection partially loaded and therefore wrong). Fetching two collections at once produces a Cartesian product — ten lines times three payments becomes thirty rows per order — and Hibernate refuses bags fetched together with MultipleBagFetchException. Fetch one collection per query, or use Set collections and accept the product for small sizes.

Aggregates, grouping and projections

Aggregates (COUNT, SUM, AVG, MIN, MAX) work with GROUP BY and HAVING exactly as in SQL. Results that are not entities come back as Object[] unless you name a type. Constructor expressions build a DTO directly:

record CustomerRevenue(String name, long orders, BigDecimal revenue) { }

List<CustomerRevenue> top = em.createQuery("""
        select new shop.report.CustomerRevenue(c.name, count(o), sum(o.total.amount))
        from Order o join o.customer c
        where o.status = shop.model.OrderStatus.PAID
        group by c.name
        having sum(o.total.amount) > :minimum
        order by sum(o.total.amount) desc""", CustomerRevenue.class)
    .setParameter("minimum", new BigDecimal("10000"))
    .setMaxResults(20)
    .getResultList();

A projection query loads exactly the columns you ask for, creates no managed entities and cannot cause lazy-loading surprises later, which makes it the right shape for read-only screens and reports. Note the enum literal: portable JPQL requires the fully qualified constant name, although Hibernate 6 also accepts the short form.

Subqueries and collection predicates

Subqueries are allowed in WHERE and HAVING (where o.total.amount > (select avg(x.total.amount) from Order x)), with EXISTS, ALL, ANY and SOME. Collection predicates express many "has at least one" conditions without an explicit join: where exists (select l from o.lines l where l.product.discontinued = true) or, simpler, where :product member of o.products.

Named queries, single results and pagination

@NamedQuery attaches a query to an entity and validates it when the persistence unit starts, so a typo fails deployment rather than the first user request:

@Entity
@NamedQuery(name = "Order.byReference",
            query = "select o from Order o where o.reference = :ref")
public class Order { ... }

Order order = em.createNamedQuery("Order.byReference", Order.class)
                .setParameter("ref", ref)
                .getSingleResult();

getSingleResult throws NoResultException for zero rows and NonUniqueResultException for more than one; both are runtime exceptions, and inside a container-managed transaction the first one is not a rollback condition on its own but often propagates as one. getResultStream returns a Stream that some providers back with a scrolling cursor — close it. For a possibly-absent row, getResultList().stream().findFirst() avoids the exception; the next specification version adds getSingleResultOrNull for exactly this.

Pagination is setFirstResult(offset) plus setMaxResults(size), translated to the database's LIMIT/OFFSET or equivalent. Two rules: always add a deterministic ORDER BY (a page without a total order returns different rows on different executions), and never combine offset pagination with a collection fetch join, for the reason given in the FAQ. For deep pages, keyset pagination (where o.placedAt < :lastSeen order by o.placedAt desc) is both faster and stable under inserts.

Entity graphs

Fetch joins fix loading for one query. Entity graphs describe what to load independently of the query and pass it as a hint, which suits repositories that expose one query with several loading profiles:

@NamedEntityGraph(name = "Order.withLines",
    attributeNodes = { @NamedAttributeNode("customer"),
                       @NamedAttributeNode(value = "lines", subgraph = "lines") },
    subgraphs = @NamedSubgraph(name = "lines", attributeNodes = @NamedAttributeNode("product")))
@Entity
public class Order { ... }

EntityGraph<?> graph = em.getEntityGraph("Order.withLines");
Order order = em.find(Order.class, id, Map.of("jakarta.persistence.fetchgraph", graph));

fetchgraph loads exactly the listed attributes eagerly and treats everything else as lazy; loadgraph loads the listed attributes in addition to whatever is eager by mapping. Graphs work on find and on queries through setHint. How the provider executes them (one join, several selects) is up to the provider; Hibernate uses joins by default.

The Criteria API for dynamic queries

Search screens with optional filters are the classic case where concatenating JPQL fragments gets ugly. The Criteria API builds the same query from objects, and with the static metamodel (generated classes Order_, Customer_ produced by the provider's annotation processor) attribute references are checked by the compiler:

public List<Order> search(String customer, OrderStatus status, Instant since) {
    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Order> cq = cb.createQuery(Order.class);
    Root<Order> o = cq.from(Order.class);
    Join<Order, Customer> c = o.join(Order_.customer);

    List<Predicate> where = new ArrayList<>();
    if (customer != null) where.add(cb.like(cb.lower(c.get(Customer_.name)), customer.toLowerCase() + "%"));
    if (status != null)   where.add(cb.equal(o.get(Order_.status), status));
    if (since != null)    where.add(cb.greaterThanOrEqualTo(o.get(Order_.placedAt), since));

    cq.select(o).where(where.toArray(Predicate[]::new))
      .orderBy(cb.desc(o.get(Order_.placedAt)));

    return em.createQuery(cq).setMaxResults(100).getResultList();
}

Everything JPQL can do — fetch joins (o.fetch(Order_.lines, JoinType.LEFT)), subqueries (cq.subquery(...)), constructor projections (cb.construct(...)), grouping, CriteriaUpdate and CriteriaDelete — has a Criteria form. The cost is verbosity; keep Criteria for genuinely dynamic queries and use JPQL for the rest. Without the metamodel you fall back to string attribute names (o.get("status")), which loses the type checking that justified the verbosity in the first place.

Bulk updates and deletes

UPDATE and DELETE statements in JPQL run as a single SQL statement, which is the efficient way to change many rows:

int archived = em.createQuery("""
        update Order o set o.status = :archived
        where o.status = :closed and o.placedAt < :cutoff""")
    .setParameter("archived", OrderStatus.ARCHIVED)
    .setParameter("closed", OrderStatus.CLOSED)
    .setParameter("cutoff", cutoff)
    .executeUpdate();

They bypass the persistence context entirely: entities already loaded are not updated, @Version is not incremented unless you do it in the statement, cascades and orphanRemoval do not run, and lifecycle callbacks are not invoked. Issue bulk statements before loading entities in the same transaction, or em.clear() afterwards. They also require an active transaction.

Native SQL

When JPQL cannot express the query — window functions, recursive CTEs, database-specific JSON operators, optimiser hints — use createNativeQuery. The result can be mapped to an entity, to scalars, or to a DTO via @SqlResultSetMapping:

@SqlResultSetMapping(name = "DailyRevenue",
    classes = @ConstructorResult(targetClass = DailyRevenue.class,
        columns = { @ColumnResult(name = "day", type = LocalDate.class),
                    @ColumnResult(name = "revenue", type = BigDecimal.class) }))

List<DailyRevenue> series = em.createNativeQuery("""
        select date_trunc('day', placed_at)::date as day,
               sum(total_amount) over (order by date_trunc('day', placed_at)) as revenue
        from orders where status = 'PAID'
        group by 1 order by 1""", "DailyRevenue")
    .getResultList();

Native queries returning entities produce managed instances like any other, so the same lazy-loading rules apply. What you lose is portability across databases and the provider's knowledge of the mapping — column names in the SQL must match the schema, not the entity. Stored procedures have their own API (@NamedStoredProcedureQuery, createStoredProcedureQuery) with IN, OUT and cursor parameters.

Query hints, timeouts and locking

setHint passes provider or standard hints: jakarta.persistence.query.timeout (milliseconds), jakarta.persistence.lock.timeout, the entity-graph hints above, and Hibernate's org.hibernate.readOnly or org.hibernate.fetchSize. setLockMode(LockModeType.PESSIMISTIC_WRITE) turns the select into SELECT … FOR UPDATE so the rows stay locked until commit; LockModeType.OPTIMISTIC_FORCE_INCREMENT bumps the version of a parent when you change its children, which is how you make an aggregate's version cover its parts. Hibernate also supports a second-level cache and query cache, both off by default and worth turning on only for reference data that rarely changes.

Common mistakes

  • N+1 selects. Iterating results and touching lazy associations. Fetch join, entity graph, or a projection.
  • Offset pagination with a collection fetch join. Whole result set in memory. Paginate the root, then fetch.
  • Fetching two bags at once. MultipleBagFetchException or a Cartesian explosion. One collection per query.
  • Missing distinct with collection joins. Duplicate root entities in the list.
  • Bulk update without clear(). Stale entities and skipped version checks. Clear or run the bulk statement first.
  • getSingleResult for optional rows. NoResultException on the happy path. Use getResultList and findFirst.
  • String-built JPQL from user input. Injection and a flooded query-plan cache. Parameters and Criteria.
  • ORDER BY omitted on paged queries. Rows repeat or vanish between pages. Always order by a unique key.

Key takeaways

  • JPQL names entities and attributes, not tables and columns; use TypedQuery and named parameters, and never concatenate user input.
  • Explicit joins are needed for collections; distinct removes duplicated roots; JOIN FETCH loads associations in the same statement and is the primary cure for N+1.
  • Constructor expressions return DTOs without creating managed entities, which is what read-only screens should use.
  • Entity graphs describe loading independently of the query and are passed as fetchgraph or loadgraph hints.
  • Use the Criteria API with the generated metamodel for dynamic filters; use JPQL for everything static.
  • Bulk UPDATE/DELETE bypasses the persistence context — clear it afterwards — and native SQL is the escape hatch for database-specific features, with @SqlResultSetMapping to shape the results.
  • Paginate with a deterministic order and never together with a collection fetch join.

Sources and further reading

Frequently asked questions

What is the difference between JOIN and JOIN FETCH?
A plain JOIN lets you filter or project on the associated entity but does not load it into the result — the association stays lazy. JOIN FETCH loads the associated entity or collection as part of the same SQL statement and initialises it on the returned entities, which is how you avoid N+1 selects for a known access pattern.
Why does my paginated query with a fetch join load everything?
A collection fetch join multiplies rows: one order with five lines becomes five rows. The provider cannot apply LIMIT/OFFSET to those rows without cutting an entity in half, so Hibernate applies the pagination in memory after fetching the whole result set and logs a warning. Paginate on the root entity first, then load collections for that page with a second query or an entity graph.
Should I use JPQL, Criteria or native SQL?
JPQL for queries known at development time — it is readable and checked when the persistence unit starts if you use named queries. Criteria for queries built from optional filters at runtime, where string concatenation would be fragile. Native SQL when you need a database feature JPQL cannot express — window functions, recursive CTEs, vendor hints — or for reporting queries that should not create entities at all.
Does a bulk UPDATE in JPQL update the entities I already loaded?
No. Bulk UPDATE and DELETE go straight to the database, bypassing the persistence context, so managed entities keep their old state and their version is not incremented. Run bulk statements at the start of a transaction, or call clear() afterwards and reload what you need.