Querying with JPA

Analogy categoryAnalogies

JPQL: Querying Objects, Not Tables

JPQL (Jakarta Persistence Query Language) looks like SQL but operates on entities and their fields rather than tables and columns — SELECT b FROM Book b WHERE b.author.lastName = :lastName references the Book entity and navigates the author association directly, and Hibernate translates it into the appropriate SQL joins against the underlying books and authors tables. This object-oriented querying is what lets JPQL remain portable across database vendors, since the same JPQL string produces correct SQL whether the underlying database is PostgreSQL, MySQL, or Oracle.

🏏

Cricket analogy: JPQL is like describing a fielding instruction in cricket terms ('move point square') rather than GPS coordinates on the ground — the abstraction is portable across any stadium, just as JPQL is portable across any database vendor.

The Criteria API for Dynamic Queries

When a query's shape depends on runtime conditions — an optional search filter here, an optional date range there — building a JPQL string with conditional concatenation becomes error-prone and hard to read. The JPA Criteria API solves this by letting you build a query programmatically using CriteriaBuilder, CriteriaQuery, and Root<T> objects, adding Predicate objects to a list only when the corresponding filter is actually present, and combining them with cb.and(predicates.toArray(new Predicate[0])) at the end.

🏏

Cricket analogy: The Criteria API is like a captain setting a field plan dynamically overs before the game, adding a slip only if the pitch is seaming and a deep square leg only if the batter is a puller — assembled conditionally rather than fixed in advance.

java

public List<Book> search(String titleKeyword, BookStatus status, Integer sinceYear) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Book> query = cb.createQuery(Book.class);
    Root<Book> book = query.from(Book.class);

    List<Predicate> predicates = new ArrayList<>();
    if (titleKeyword != null) {
        predicates.add(cb.like(cb.lower(book.get("title")), "%" + titleKeyword.toLowerCase() + "%"));
    }
    if (status != null) {
        predicates.add(cb.equal(book.get("status"), status));
    }
    if (sinceYear != null) {
        predicates.add(cb.greaterThanOrEqualTo(book.get("publishedYear"), sinceYear));
    }

    query.select(book).where(cb.and(predicates.toArray(new Predicate[0])));
    return entityManager.createQuery(query).getResultList();
}

Pagination and Sorting

Repository methods that accept a Pageable parameter — built with PageRequest.of(pageNumber, pageSize, Sort.by("title").ascending()) — return a Page<T> that carries not just the current page's content but also totalElements, totalPages, and hasNext(), which is exactly what a paginated REST API or UI needs. Under the covers, Spring Data JPA issues two queries for a Page result: the main query with LIMIT/OFFSET (or the vendor equivalent) for the current page's rows, and a separate COUNT query to compute the total, which is why returning Slice<T> instead (which only knows whether a next page exists, not the total count) can be a meaningful performance optimization when you don't need the total.

🏏

Cricket analogy: A Page<T> result is like a ball-by-ball highlights reel that also tells you the total overs remaining in the innings, whereas a Slice<T> is like a highlights reel that only tells you whether there's another over coming, without needing the full innings total upfront.

For read-only queries, annotate the service method with @Transactional(readOnly = true). This lets Hibernate skip dirty-checking on loaded entities, and many database drivers use it as a hint to route the query to a read replica, both of which can meaningfully improve throughput on read-heavy endpoints.

Avoiding the N+1 Select Problem

The N+1 select problem occurs when you fetch a list of N parent entities with one query, and then, because an association is lazily loaded, Hibernate issues one additional query per parent to fetch each one's related collection — turning what should be a couple of queries into N+1 round trips to the database. The standard fixes are JOIN FETCH in a JPQL query (SELECT DISTINCT b FROM Book b JOIN FETCH b.author WHERE ...), an @EntityGraph annotation on the repository method naming which associations to eagerly fetch for that specific call, or, for collections, Hibernate's @BatchSize annotation which fetches related rows in batches instead of one query per parent.

🏏

Cricket analogy: The N+1 problem is like a scorer fetching the full career stats for one batter, then realizing they need to make a separate lookup for each of the other ten players individually instead of pulling the whole team's stats in one query.

JOIN FETCH combined with a to-many association and Pageable together can silently break pagination — Hibernate has to load the entire result set into memory and paginate in application code (with a warning logged) rather than pushing LIMIT/OFFSET to the database, since a SQL-level LIMIT on a joined, duplicated row set would cut rows mid-collection. Use @EntityGraph or a separate count query strategy instead when you need both eager fetching and pagination together.

JPQL queries entities and their fields, not tables and columns directly, keeping queries portable across database vendors.
The Criteria API builds queries programmatically, ideal for dynamic filters that vary based on runtime conditions.
Page<T> includes total count and page metadata, while Slice<T> avoids the extra COUNT query for lighter-weight infinite scroll.
@Transactional(readOnly = true) allows Hibernate to skip dirty checking and can enable read-replica routing.
The N+1 select problem arises from lazy-loaded associations triggering one extra query per parent entity.
JOIN FETCH, @EntityGraph, and @BatchSize are the standard fixes for the N+1 select problem.
Combining JOIN FETCH on a collection with Pageable can break SQL-level pagination; prefer @EntityGraph in that case.