JPQL Basics

Queries on Entities, Not Tables

JPQL (Jakarta Persistence Query Language) looks like SQL, but you write entity names and Java field names, not table and column names. Hibernate translates it to SQL for your database (MySQL, PostgreSQL, Oracle...). Hibernate's own dialect, HQL, is a superset of JPQL.

JPQL	Generated SQL (MySQL)
`select p from Product p where p.price < 500`	`select p1_0.id,... from products p1_0 where p1_0.price<500`
`select p from Product p where p.category.name = 'Books'`	joins `categories` automatically (implicit join)
`select count(o) from Order o where o.status = :s`	`select count(o1_0.id) from orders o1_0 where o1_0.status=?`

Running JPQL with EntityManager

@Repository
@RequiredArgsConstructor
public class ProductQueries {

    private final EntityManager em;

    public List<Product> cheapInCategory(String category, BigDecimal maxPrice) {
        return em.createQuery("""
                select p from Product p
                where p.category.name = :category
                  and p.price <= :maxPrice
                order by p.price
                """, Product.class)
            .setParameter("category", category)
            .setParameter("maxPrice", maxPrice)
            .getResultList();
    }

    public long countByStatus(OrderStatus status) {
        return em.createQuery("select count(o) from Order o where o.status = :status", Long.class)
                 .setParameter("status", status)
                 .getSingleResult();
    }
}

select p1_0.id,p1_0.category_id,p1_0.description,p1_0.name,p1_0.price,p1_0.stock
from products p1_0
join categories c1_0 on c1_0.id=p1_0.category_id
where c1_0.name=? and p1_0.price<=?
order by p1_0.price

Useful JPQL Features

Joins: select o from Order o join o.items i where i.product.id = :pid (join through the field, no ON needed).
Aggregates: count, sum, avg, min, max with group by / having.
Functions: lower(), upper(), concat(), coalesce(), size(o.items), extract(year from o.createdAt).
Enums and dates as parameters: where o.status = :status and o.createdAt >= :from.
Subqueries in WHERE: where p.price > (select avg(p2.price) from Product p2).

Note: Always bind values with parameters (:name or ?1). Never build JPQL by concatenating user input: JPQL injection is as dangerous as SQL injection.

Practice

Exercise: Write a JPQL query that returns the total revenue (sum of quantity * price of order items) for orders with status DELIVERED.

Show answer

BigDecimal revenue = em.createQuery("""
        select coalesce(sum(i.quantity * i.price), 0)
        from OrderItem i
        where i.order.status = :status
        """, BigDecimal.class)
    .setParameter("status", OrderStatus.DELIVERED)
    .getSingleResult();