JPQL Essentials

Queries over entities, not tables

JPQL (Jakarta Persistence Query Language) looks like SQL but operates on entities and their fields, not tables and columns: select o from PurchaseOrder o where o.status = :status. Hibernate translates it to the database's SQL dialect. Always use parameters (named :status or positional ?1), never string concatenation, which invites SQL/JPQL injection and prevents statement caching. JPQL supports join and left join along mapped associations (with join fetch to load them), where, group by, having, order by, aggregates (count, sum, avg, min, max), subqueries, case expressions and functions. Pagination uses setFirstResult and setMaxResults, which become OFFSET/LIMIT; for deep pages, keyset pagination (where o.id < :lastSeenId order by o.id desc) is much faster. Use TypedQuery<T> (createQuery(jpql, Type.class)) for type safety, and getSingleResult only when exactly one row is guaranteed, since it throws for zero or several. Hibernate's HQL is a superset of JPQL with extra features.

From JPQL to SQL

JPQL is written against the entity model; Hibernate translates it into SQL for the specific database.

JPQL with joins, aggregates and keyset pagination

Parameters everywhere; pagination done in the database.

// revenue per city for paid orders in a period
List<Object[]> rows = em.createQuery("""
        select c.address.city, count(o), sum(o.total)
        from PurchaseOrder o join o.customer c
        where o.status = :status and o.placedAt between :from and :to
        group by c.address.city
        having count(o) > :min
        order by sum(o.total) desc
        """, Object[].class)
    .setParameter("status", OrderStatus.PAID)
    .setParameter("from", from)
    .setParameter("to", to)
    .setParameter("min", 10L)
    .getResultList();

// keyset pagination: fast even for page 10,000
List<PurchaseOrder> next = em.createQuery("""
        select o from PurchaseOrder o
        where o.id < :lastSeenId
        order by o.id desc
        """, PurchaseOrder.class)
    .setParameter("lastSeenId", lastSeenId)
    .setMaxResults(20)
    .getResultList();

// NEVER: "... where o.customer.email = '" + email + "'"   (injection risk)

getSingleResult throws on no rows

getSingleResult() throws NoResultException when nothing matches and NonUniqueResultException when several do. For optional results, use getResultStream().findFirst() or a Spring Data method returning Optional.

त्वरित जाँच: How should user input be included in a JPQL query?

• By concatenating strings
• By escaping quotes manually
• Through named or positional parameters
• Via system properties