JAVA-250 · Lesson 9 of 12 · 26 min

Queries with JPQL

▶ Video coming soon A video version of this lesson is on the way.

Odile Branscombe, Second Gear's general manager, had collected the questions the rental desks asked ShopBook every week, and on Friday, November 19, she sent Hamza the list: which bikes are booked on a given day, how many bookings each kind of bike had last month, which customers book cargo bikes, and the next twenty bookings in date order. em.find answers none of them, because each needs a query, not a lookup by key. JPA's own query language is the Jakarta Persistence Query Language, JPQL. This lesson shows how it differs from SQL, how to bind parameters, how to join through relationships, how to project results into records, how to aggregate and page, and when to reach for the Criteria API or plain SQL instead.

JPQL is about entities, not tables

JPQL looks like SQL, and that is deliberate, but it works on the entity model: it names entity classes and their fields, not tables and columns, and it follows relationships with dots. SELECT b FROM Booking b WHERE b.bike.kind = :kind reads as "the bookings whose bike's kind is the given kind"; the provider knows from the mapping that Booking lives in the booking table and that b.bike means a join on bike_id, and it writes the SQL for the database in use. Results are managed entities, which arrive in the persistence context like objects from find.

Three boxes left to right. The first holds a JPQL query: select b from Booking b where b.bike.kind = :kind. An arrow labelled translated using the mapping leads to the second box, SQL: select ... from booking b1_0 join bike b2_0 on b2_0.id = b1_0.bike_id where b2_0.kind = ?. An arrow labelled results become managed entities leads to the third box, a list of Booking objects in the persistence context.
JPQL names entities and fields; the provider translates it to SQL using the mapping.
JPQL and the SQL a provider writes for it.
JPQLSQL it means
SELECT p FROM Part pSELECT every mapped column FROM part
WHERE p.reorderAt > p.quantityWHERE reorder_at > quantity
b.bike.kindA join from booking to bike, then bike.kind
JOIN b.bike kJOIN bike k ON k.id = b.bike_id
WHERE p.code = :codeWHERE code = ?, with the value bound
SELECT COUNT(b) FROM Booking bSELECT count(b.id) FROM booking b
ORDER BY b.startDateORDER BY start_date

In code, a query is created with em.createQuery, which takes the JPQL string and, for a TypedQuery, the result class. Parameters are bound by name with setParameter, and getResultList or getSingleResult runs it. Since JPA 2.2, getResultStream returns a Stream. Hibernate 7 also checks the query against the mapping when it is created, so a misspelled field name fails at once with a clear message rather than as an SQL error later.

Java Example · BookingQueries.java (needs Jakarta Persistence)
public List<Booking> bookedOn(LocalDate day) {
    return em.createQuery("""
            SELECT b FROM Booking b
            WHERE b.startDate <= :day AND b.endDate >= :day
            ORDER BY b.bike.tag
            """, Booking.class)
        .setParameter("day", day)
        .getResultList();
}

public Optional<Part> byCode(String code) {
    return em.createQuery("SELECT p FROM Part p WHERE p.code = :code", Part.class)
        .setParameter("code", code)
        .getResultStream()
        .findFirst();
}

What this code does

Text blocks keep multi-line JPQL readable without string concatenation. The :day parameter appears twice but is bound once by name, which is one reason named parameters are easier to maintain than positional ones. getResultStream with findFirst gives an Optional, avoiding getSingleResult, which throws NoResultException when nothing matches.

Named parameters are bound by the provider, which turns them into the positional placeholders that JDBC understands, exactly as in lesson 3. JPQL built by concatenating user input is as open to injection as SQL built that way, so the rule does not change: values always go in parameters. The example below shows what a provider does with named parameters: it replaces each :name with ? and records the order, so a name used twice becomes two positions with the same value.

Java Example · Main.java
import java.util.*;
import java.util.regex.*;

public class Main {
    record Prepared(String sql, List<Object> values) {}

    static Prepared bind(String query, Map<String, Object> params) {
        Matcher m = Pattern.compile(":([A-Za-z]\\w*)").matcher(query);
        StringBuilder sql = new StringBuilder();
        List<Object> values = new ArrayList<>();
        while (m.find()) {
            String name = m.group(1);
            if (!params.containsKey(name)) throw new IllegalArgumentException("No value for parameter :" + name);
            values.add(params.get(name));
            m.appendReplacement(sql, "?");
        }
        m.appendTail(sql);
        return new Prepared(sql.toString(), values);
    }

    public static void main(String[] args) {
        String q = "select ... from booking where start_date <= :day and end_date >= :day and customer <> :who";
        Prepared p = bind(q, Map.of("day", "2027-10-16", "who", "Theo"));
        System.out.println(p.sql());
        for (int i = 0; i < p.values().size(); i++) System.out.println("  ?" + (i + 1) + " = " + p.values().get(i));

        try {
            bind("select ... from part where code = :code", Map.of("cod", "BELL"));
        } catch (IllegalArgumentException e) {
            System.out.println(e.getMessage());
        }
    }
}
Output
select ... from booking where start_date <= ? and end_date >= ? and customer <> ?
  ?1 = 2027-10-16
  ?2 = 2027-10-16
  ?3 = Theo
No value for parameter :code

What this code does

Each :name is replaced by a question mark, and its value is added to the list in the order the names appear, so :day fills positions 1 and 2. A misspelled parameter name is reported before any SQL is sent. Hibernate does the same work internally before handing a PreparedStatement to JDBC.

SELECT b.bike_tag, b.customer, b.start_date, b.end_date
FROM booking b
WHERE b.start_date <= '2027-10-16' AND b.end_date >= '2027-10-16'
ORDER BY b.bike_tag;

What this code does

The SQL equivalent of bookedOn for October 16, run on the sample data. Theo's two-day booking and Ana's one-day booking both cover the day, because a one-day booking is stored with the same start and end date.

Result: bikes booked on October 16, 2027.
bike_tagcustomerstart_dateend_date
SG-0003-CITheo2027-10-162027-10-17
SG-0007-ARAna2027-10-162027-10-16

Odile spotted a gap at once. The counter screen lets the desk staff take an open booking, with no end date yet, for a customer who will phone when they know their return day, and the new ShopBook design stores the missing date as NULL. Today's table declares end_date NOT NULL, so Hamza tried the design on a copy, booking_draft, which CREATE TABLE ... AS SELECT makes without the constraint. A comparison with NULL is neither true nor false but unknown, so WHERE drops the row, and an open booking disappears from the day's list although the bike is out. The script below adds an open booking to the copy and runs the same query against it.

CREATE TABLE booking_draft AS SELECT * FROM booking;
INSERT INTO booking_draft VALUES (6, 'SG-0012-TA', 'Priya', '2027-10-15', NULL);

What this code does

Priya's tandem booking started on October 15 and has no end date. The query still returns only Theo and Ana, because end_date >= '2027-10-16' is unknown for her row.

Result: the open booking is missing.
bike_tagcustomer
SG-0003-CITheo
SG-0007-ARAna

The fix treats a missing end date as "still out". JPQL supports COALESCE, which returns its first argument that is not null, so the condition becomes COALESCE(b.endDate, :day) >= :day. JPQL also has the other common SQL functions, including CONCAT, LOWER, UPPER, LENGTH, ABS, CURRENT_DATE and, since JPA 3.1, EXTRACT for the parts of a date.

CREATE TABLE booking_draft AS SELECT * FROM booking;
INSERT INTO booking_draft VALUES (6, 'SG-0012-TA', 'Priya', '2027-10-15', NULL);

What this code does

With COALESCE, an open booking counts as ending on the day being checked, so Priya's tandem now appears with Theo and Ana. The same rule written in JPQL works on every database Hibernate supports.

Result: the corrected query.
bike_tagcustomer
SG-0003-CITheo
SG-0007-ARAna
SG-0012-TAPriya

Joins

A path such as b.bike.kind joins implicitly. An explicit JOIN gives the joined entity an alias, which you need for conditions on a collection or when you want to select it. Because JPQL joins follow mapped relationships, there is no ON clause listing key columns; the provider knows them. LEFT JOIN keeps entities with no match, and JPA also allows joins on any condition with JOIN ... ON for entities with no mapped relationship.

Java Example · Join queries (needs Jakarta Persistence)
// customers who have booked a cargo bike
List<String> cargoCustomers = em.createQuery("""
        SELECT DISTINCT b.customer FROM Booking b JOIN b.bike k
        WHERE k.kind = :kind
        ORDER BY b.customer
        """, String.class)
    .setParameter("kind", Kind.CARGO)
    .getResultList();

// every bike, with or without bookings
List<Object[]> rows = em.createQuery(
        "SELECT k.tag, COUNT(b) FROM Bike k LEFT JOIN k.bookings b GROUP BY k.tag ORDER BY k.tag", Object[].class)
    .getResultList();

What this code does

The enum parameter is bound as the Java constant, and the provider stores it the way the mapping says, as the string CARGO. The second query starts from Bike and follows the bookings collection with LEFT JOIN, so bikes with no bookings appear with a count of zero. Results with several values come back as Object arrays unless you ask for a record, as the next section shows.

SELECT k.tag, k.kind, COUNT(b.id) AS bookings
FROM bike k LEFT JOIN booking b ON b.bike_tag = k.tag
GROUP BY k.tag, k.kind
ORDER BY k.tag;

What this code does

The SQL for the second query on the sample data. The LEFT JOIN keeps the four bikes nobody has booked, and COUNT(b.id) counts only matching bookings, so they show zero.

Result: bookings per bike, including bikes with none.
tagkindbookings
SG-0001-CICITY0
SG-0002-CICITY0
SG-0003-CICITY2
SG-0004-CICITY0
SG-0007-ARCARGO2
SG-0012-TATANDEM1

Projections into records

Selecting whole entities is right when the program will change them. For a report it wastes memory and fills the persistence context with objects nobody will update. A projection selects only the values needed. The cleanest form is the constructor expression, SELECT NEW, which calls a constructor for each row; records are ideal, because they are exactly a named tuple of values. Since Hibernate 6 you can also pass the record class directly to createQuery and let Hibernate match the selected values to its constructor.

Java Example · A projection query (needs Jakarta Persistence)
public record KindCount(Kind kind, long bookings) {}

List<KindCount> perKind = em.createQuery("""
        SELECT NEW ca.secondgear.shopbook.KindCount(k.kind, COUNT(b))
        FROM Booking b JOIN b.bike k
        WHERE b.startDate >= :from AND b.startDate < :to
        GROUP BY k.kind
        ORDER BY COUNT(b) DESC, k.kind
        """, KindCount.class)
    .setParameter("from", LocalDate.of(2027, 10, 1))
    .setParameter("to", LocalDate.of(2027, 11, 1))
    .getResultList();

What this code does

SELECT NEW needs the record's fully qualified name and a constructor whose parameter types match the selected values; COUNT returns a Long, so the component is long. The results are plain records, not entities, so nothing is added to the persistence context and nothing is dirty-checked. The half-open date range, from the first of the month to the first of the next, avoids problems with the last day.

The example below computes the same report in plain Java over a list of booking records, the way a test or an in-memory repository would. It is useful for checking what a query should return, and it shows what the grouping and ordering clauses mean.

Java Example · Main.java
import java.time.LocalDate;
import java.util.*;
import java.util.stream.*;

public class Main {
    enum Kind { CARGO, CITY, TANDEM }
    record Booking(int id, String bike, Kind kind, String customer, LocalDate start) {}
    record KindCount(Kind kind, long bookings) {}

    public static void main(String[] args) {
        List<Booking> bookings = List.of(
            new Booking(1, "SG-0007-AR", Kind.CARGO, "Ravi", LocalDate.of(2027, 10, 2)),
            new Booking(2, "SG-0012-TA", Kind.TANDEM, "Lin family", LocalDate.of(2027, 10, 2)),
            new Booking(3, "SG-0003-CI", Kind.CITY, "Mei", LocalDate.of(2027, 10, 9)),
            new Booking(4, "SG-0007-AR", Kind.CARGO, "Ana", LocalDate.of(2027, 10, 16)),
            new Booking(5, "SG-0003-CI", Kind.CITY, "Theo", LocalDate.of(2027, 10, 16)),
            new Booking(6, "SG-0007-AR", Kind.CARGO, "Ravi", LocalDate.of(2027, 11, 6)));

        LocalDate from = LocalDate.of(2027, 10, 1), to = LocalDate.of(2027, 11, 1);
        List<KindCount> perKind = bookings.stream()
            .filter(b -> !b.start().isBefore(from) && b.start().isBefore(to))        // WHERE
            .collect(Collectors.groupingBy(Booking::kind, TreeMap::new, Collectors.counting()))  // GROUP BY, COUNT
            .entrySet().stream()
            .map(e -> new KindCount(e.getKey(), e.getValue()))                       // SELECT NEW
            .sorted(Comparator.comparingLong(KindCount::bookings).reversed()
                    .thenComparing(KindCount::kind))                                 // ORDER BY
            .toList();
        perKind.forEach(System.out::println);

        List<String> cargoCustomers = bookings.stream()
            .filter(b -> b.kind() == Kind.CARGO).map(Booking::customer)
            .distinct().sorted().toList();                                           // SELECT DISTINCT ... ORDER BY
        System.out.println("Cargo customers: " + cargoCustomers);
    }
}
Output
KindCount[kind=CARGO, bookings=2]
KindCount[kind=CITY, bookings=2]
KindCount[kind=TANDEM, bookings=1]
Cargo customers: [Ana, Ravi]

What this code does

The filter plays the WHERE clause with the half-open range, so Ravi's November booking is left out; groupingBy and counting play GROUP BY and COUNT. Sorting by count descending and then by kind gives CARGO and CITY two each, in enum order, then TANDEM. The second pipeline is the DISTINCT query of the previous section, and Ravi appears once although he booked twice.

Aggregates, single results and updates

JPQL has the usual aggregates, COUNT, SUM, AVG, MIN and MAX, with GROUP BY and HAVING. A query that returns exactly one value, such as a count, uses getSingleResult, which throws NoResultException for no rows and NonUniqueResultException for more than one. JPQL can also run bulk UPDATE and DELETE statements with executeUpdate. These go straight to the database and bypass the persistence context, so entities already loaded do not see the change; run them in their own transaction, or clear the context afterwards.

Java Example · Aggregates and bulk updates (needs Jakarta Persistence)
long cargoCount = em.createQuery(
        "SELECT COUNT(b) FROM Booking b WHERE b.bike.kind = :kind", Long.class)
    .setParameter("kind", Kind.CARGO)
    .getSingleResult();

int archived = em.createQuery(
        "UPDATE Booking b SET b.status = :archived WHERE b.endDate < :cutoff")
    .setParameter("archived", Status.ARCHIVED)
    .setParameter("cutoff", LocalDate.of(2027, 1, 1))
    .executeUpdate();            // bypasses the persistence context
em.clear();                       // so loaded bookings do not show stale status

What this code does

COUNT returns a Long, so the TypedQuery's class is Long.class. The bulk update changes every matching row in one statement instead of loading each booking, and returns the number of rows changed. Clearing the context afterwards stops the program from working with entities whose status in memory no longer matches the database.

Paging

Odile's next-twenty list is a page. setFirstResult and setMaxResults ask the provider to return only part of the result, and it translates them into the database's own syntax, such as LIMIT and OFFSET or FETCH FIRST. Paging only makes sense with an ORDER BY that gives a stable order; otherwise the database may return rows in a different order for each page, and some rows will appear twice and others never. Large offsets are also slow, because the database still reads and discards all the skipped rows; for deep paging, keyset paging, which asks for rows after the last key seen, scales better.

Java Example · Main.java
import java.util.*;

public class Main {
    record Page(int number, int firstResult, int maxResults) {}

    static Page page(int number, int size) { return new Page(number, (number - 1) * size, size); }

    static <T> List<T> slice(List<T> sorted, Page p) {
        int from = Math.min(p.firstResult(), sorted.size());
        int to = Math.min(from + p.maxResults(), sorted.size());
        return sorted.subList(from, to);
    }

    static List<Integer> afterKey(List<Integer> sorted, int lastSeen, int size) {      // keyset paging
        return sorted.stream().filter(id -> id > lastSeen).limit(size).toList();
    }

    public static void main(String[] args) {
        List<Integer> bookingIds = new ArrayList<>();
        for (int id = 101; id <= 145; id++) bookingIds.add(id);                         // 45 bookings, in id order

        int size = 20;
        int pages = (bookingIds.size() + size - 1) / size;
        System.out.println("Pages: " + pages);
        for (int n = 1; n <= pages; n++) {
            Page p = page(n, size);
            List<Integer> rows = slice(bookingIds, p);
            System.out.println("Page " + n + ": setFirstResult(" + p.firstResult() + ") setMaxResults(" + p.maxResults()
                    + ") -> " + rows.get(0) + ".." + rows.get(rows.size() - 1) + " (" + rows.size() + " rows)");
        }
        System.out.println("Keyset after 140: " + afterKey(bookingIds, 140, size));
    }
}
Output
Pages: 3
Page 1: setFirstResult(0) setMaxResults(20) -> 101..120 (20 rows)
Page 2: setFirstResult(20) setMaxResults(20) -> 121..140 (20 rows)
Page 3: setFirstResult(40) setMaxResults(20) -> 141..145 (5 rows)
Keyset after 140: [141, 142, 143, 144, 145]

What this code does

Page n starts at offset (n − 1) × 20, so page 3 asks for rows 40 onwards and gets the last five of 45. The page count uses the round-up division from earlier lessons. The keyset version asks for ids greater than the last one shown, which a database answers from an index without reading the skipped rows.

Java Example · The next twenty bookings (needs Jakarta Persistence)
List<Booking> next20 = em.createQuery("""
        SELECT b FROM Booking b
        WHERE b.startDate >= CURRENT_DATE
        ORDER BY b.startDate, b.id
        """, Booking.class)
    .setFirstResult(0)
    .setMaxResults(20)
    .getResultList();

What this code does

Sorting by start date alone would not be stable, because several bookings start on the same day; adding b.id as a tiebreaker gives every row a fixed place. On PostgreSQL, Hibernate turns this into SQL ending in offset ? rows fetch first ? rows only.

Named queries, Criteria and native SQL

Three other ways to query are worth knowing. A named query, declared with @NamedQuery on an entity and run with em.createNamedQuery, is checked when the persistence unit starts, so a broken query stops the program at once instead of on first use. The Criteria API builds a query from Java objects instead of a string; it is verbose, but it suits search screens where filters are added only if the user filled them in, and with Hibernate's generated metamodel the field names are checked by the compiler. A native query, em.createNativeQuery, runs plain SQL, for features JPQL does not have, such as PostgreSQL's window functions or full-text search, and can still map the results to entities or records.

Java Example · Criteria and native queries (needs Jakarta Persistence)
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Booking> q = cb.createQuery(Booking.class);
Root<Booking> b = q.from(Booking.class);
List<Predicate> filters = new ArrayList<>();
if (customer != null) filters.add(cb.equal(b.get("customer"), customer));
if (kind != null)     filters.add(cb.equal(b.get("bike").get("kind"), kind));
if (from != null)     filters.add(cb.greaterThanOrEqualTo(b.get("startDate"), from));
q.select(b).where(filters.toArray(Predicate[]::new)).orderBy(cb.asc(b.get("startDate")));
List<Booking> results = em.createQuery(q).getResultList();

List<Object[]> busiest = em.createNativeQuery("""
        SELECT bike_id, count(*) AS n, rank() OVER (ORDER BY count(*) DESC) AS place
        FROM booking GROUP BY bike_id
        """).getResultList();

What this code does

The Criteria query adds a condition only for each filter the user filled in, which would need fiddly string building in JPQL. Values still travel as parameters. The native query uses a window function, rank() OVER, which plain JPQL cannot express, and returns Object arrays of columns.

Try it yourself

Bind named parameters

Complete bind so it replaces every :name in the query with ? and returns the values in the order the names appear.

The program prints the SQL and [CARGO, 2027-10-01, CARGO].

import java.util.*;
import java.util.regex.*;

public class Main {
    static List<Object> values = new ArrayList<>();

    static String bind(String query, Map<String, Object> params) {
        return query;
    }

    public static void main(String[] args) {
        String sql = bind("kind = :kind and start >= :from or spare = :kind",
                Map.of("kind", "CARGO", "from", "2027-10-01"));
        System.out.println(sql);
        System.out.println(values);
    }
}

Try it yourself

Work out a page

Write firstResult(int page, int size) for pages numbered from 1, and pageCount(int rows, int size) that rounds up.

The program prints 0 20 40, then 3 and 1.

public class Main {
    static int firstResult(int page, int size) { return 0; }
    static int pageCount(int rows, int size) { return 0; }

    public static void main(String[] args) {
        System.out.println(firstResult(1, 20) + " " + firstResult(2, 20) + " " + firstResult(3, 20));
        System.out.println(pageCount(45, 20));
        System.out.println(pageCount(20, 20));
    }
}

Common mistakes

  • MistakeWriting table and column names in JPQL.

    InsteadName entities and fields; the provider maps them to tables and columns.

  • MistakeConcatenating user input into a JPQL string.

    InsteadUse named parameters with setParameter.

  • MistakeCalling getSingleResult when there may be no match.

    InsteadUse getResultStream().findFirst() to get an Optional.

  • MistakePaging without a stable ORDER BY.

    InsteadOrder by the sort columns plus a unique key such as the id.

  • MistakeLoading entities for a read-only report.

    InsteadSelect a record projection with SELECT NEW or a record class.

Summary and key terms

  • JPQL queries entities and their fields, follows relationships with dots and explicit JOINs, and is translated to SQL by the provider using the mapping. em.createQuery with a result class gives a TypedQuery; named parameters are bound with setParameter and become JDBC placeholders, so input never becomes query text. getResultList, getResultStream and getSingleResult run the query.
  • Joins need no ON clause for mapped relationships, and LEFT JOIN keeps entities with no match.
  • SELECT NEW or a record class projects results into records that are not managed.
  • Aggregates work with GROUP BY and HAVING, and bulk UPDATE and DELETE bypass the persistence context. setFirstResult and setMaxResults page results, which needs a stable order; keyset paging scales better for deep pages.
  • Named queries are checked at startup, the Criteria API builds dynamic queries in Java, and native queries run SQL that JPQL cannot express.
JPQL
The Jakarta Persistence Query Language, which queries entities and their fields.
TypedQuery
A query whose results have a declared Java type.
Named parameter
A placeholder such as :day, bound by name with setParameter.
Implicit join
A join written as a path such as b.bike.kind.
Projection
A query that selects only some values rather than whole entities.
Constructor expression
SELECT NEW, which builds an object from each result row.
Bulk update
A JPQL UPDATE or DELETE that changes many rows directly, bypassing the persistence context.
Keyset paging
Paging by asking for rows after the last key seen, instead of by offset.
Criteria API
A Java API for building queries as objects.
Native query
A plain SQL query run through the EntityManager.

Practice problems

Work each problem on paper first, then reveal the answer to check.

  1. What does b.bike.kind mean in JPQL?

    Show answer

    The kind of each booking's bike, through an implicit join.

  2. Why was Priya missing from the October 16 result at first?

    Show answer

    Her end_date was null, so the comparison was unknown; COALESCE fixed it.

  3. What type should a record component have for COUNT(b)?

    Show answer

    long, because COUNT returns a Long.

  4. What must a bulk UPDATE be followed by if loaded entities will be used?

    Show answer

    Clearing or refreshing the persistence context.

  5. Which offset does page 4 of 20 rows per page use?

    Show answer

    60.

Lesson quiz · 5 questions

Score 60% or more to complete this lesson.

Take the quiz