# JPQL Essentials — Hibernate / JPA

Source: https://www.skillbyai.com/en/hibernate-jpa/q-jpql

> Write JPQL queries with parameters, joins, aggregates and pagination.

## 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.

![A document with entity icons on the left, an arrow through a gear, and a document with table icons on the right.](assets/figures/hibernate-jpa/section-6-map.svg) — Figure 6.1 — JPQL translated into database-specific SQL.

## JPQL with joins, aggregates and keyset pagination

Parameters everywhere; pagination done in the database.

```java
// 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`.

**Quiz:** How should user input be included in a JPQL query?

- [ ] By concatenating strings
- [ ] By escaping quotes manually
- [x] Through named or positional parameters
- [ ] Via system properties

*Answer:* Through named or positional parameters. Parameters prevent injection and let the database reuse statement plans.