Skip to content
Tutorials Center
My learning

Topics

Lesson 51 of 119 ยท JPA and Hibernate

Querying with JPQL and the Criteria API

Query JPA entities with JPQL and the Criteria API - parameters, joins, named queries, DTO projections, pagination, bulk updates and native SQL, with runnable SQL on H2.

entityManager.find(Book.class, 42) loads one entity by its id. Every other question ("books under $12", "authors with no books", "page 3 of the catalog") needs a query. JPA gives you three ways to write one: JPQL, a query language that looks like SQL but talks about entities and fields; the Criteria API, which builds the same queries from Java objects; and native queries, plain SQL for the cases JPQL can't express.

Under the hood, all three end up as SQL sent through JDBC. In this lesson you will write each kind of query and, next to it, run the SQL that Hibernate generates against a real H2 database. Seeing both halves makes JPQL far less magical, and it makes slow or wrong queries much easier to spot. The examples use the Author and Book entities from Entity Relationships.

JPQL: SQL for entities

A JPQL query names entity classes and their fields, not tables and columns:

Java
List<Book> cheap = entityManager
    .createQuery("select b from Book b where b.price < :max order by b.price", Book.class)
    .setParameter("max", new BigDecimal("12.00"))
    .getResultList();

Read it piece by piece:

  • Book is the entity name (by default the simple class name), not the book table.
  • b is an identification variable, an alias you must declare and use. select b returns whole Book entities, managed by the persistence context.
  • b.price is a field path. If the field is named price but the column is unit_price, you still write b.price; Hibernate translates.
  • :max is a named parameter. You can also use positional ones (?1, ?2). Either way the value is sent as a JDBC parameter, never concatenated, so JPQL parameters are as safe as PreparedStatement (see PreparedStatement and SQL Injection).

Keywords are case-insensitive (SELECT and select both work), but entity and field names are case-sensitive, because they are Java names.

Paths and joins

Dots in a path follow associations. b.author.country walks from a book to its author, so Hibernate adds an inner join for you. You can also join explicitly, which lets you name the joined entity and choose left join:

Java
// implicit join through a path
select b.title from Book b where b.author.country = :country

// explicit joins
select b from Book b join b.author a where a.country = :country
select a.name, count(b) from Author a left join a.books b group by a.name

You join along mapped associations (b.author, a.books), not along columns. Since Hibernate 6 you can also join unrelated entities with an explicit on condition, but if you do that often, you are probably missing a mapping.

Collections have their own operators: a.books is empty, size(a.books) and :book member of a.books. Hibernate turns them into subqueries.

The next example runs the SQL that three typical JPQL queries become. Change the parameter values and run it again.

Java JPQL and the SQL it becomes

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The real SQL that Hibernate writes has generated aliases (b1_0, a1_0) and selects every mapped column, but its shape is the same. Turn on hibernate.show_sql (or the org.hibernate.SQL logger) during development so you always see it.

Running a query

A TypedQuery<T> offers a few ways to get results:

Method Returns Watch out for
getResultList() a List<T>, possibly empty loading huge lists; add paging
getSingleResult() exactly one T throws NoResultException for zero rows and NonUniqueResultException for more than one
getSingleResultOrNull() one T or null (Jakarta Persistence 3.2+) still throws for more than one row
getResultStream() a Stream<T> close it (try-with-resources) so the cursor is released

getSingleResult() is a common source of production errors: "find the user with this email" throws as soon as a user doesn't exist. Use getSingleResultOrNull(), or getResultList() and check whether it is empty.

Projections and DTOs

select b returns managed entities, which Hibernate tracks for changes. For read-only screens, reports and APIs you often need only a few values. Loading entities for that wastes memory and CPU. JPQL can select scalar values instead, either as Object[] rows or, better, straight into a DTO with a constructor expression:

Java
public record AuthorStats(String name, Long books, Double averagePrice) {}

List<AuthorStats> stats = entityManager.createQuery("""
        select new com.example.catalog.AuthorStats(a.name, count(b), avg(b.price))
        from Author a left join a.books b
        group by a.name
        having count(b) > 0
        order by count(b) desc, a.name
        """, AuthorStats.class)
    .getResultList();

select new needs the fully qualified class name and a constructor whose parameter types match the selected expressions: count() returns Long and avg() returns Double. Records work well as DTOs. The objects are not entities, so nothing is tracked and nothing is accidentally saved. Recent Hibernate versions can also skip new and call the record's constructor when you pass the record as the query's result class.

Here is the same projection run as SQL and mapped by hand, the way Hibernate does it:

Java Projecting rows into a record DTO

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The having clause filters groups after counting, so the author without books disappears. Remove it and run again: Butler appears with 0 books and an average of 0.0, because getDouble turns SQL NULL into zero. A Double constructed by Hibernate would be null instead, which is one more reason to think about NULLs in aggregates.

Named queries

A named query is a JPQL string attached to an entity under a name:

Java
@Entity
@NamedQuery(name = "Book.byAuthor",
            query = "select b from Book b where b.author.id = :authorId order by b.title")
public class Book { ... }

List<Book> books = entityManager.createNamedQuery("Book.byAuthor", Book.class)
    .setParameter("authorId", 2L)
    .getResultList();

Hibernate parses and validates named queries at startup, so a typo in a field name stops the application from booting instead of failing on the first request. That is their main benefit. The cost is that the query lives far from the code that uses it. Spring Data's @Query annotations on repository methods (see Spring Data JPA) are validated the same way and are the more common style today.

Pagination

Never load "all orders" into memory. Ask for one page at a time:

Java
List<Book> page = entityManager
    .createQuery("select b from Book b order by b.title, b.id", Book.class)
    .setFirstResult(pageIndex * pageSize)   // rows to skip
    .setMaxResults(pageSize)                // rows to return
    .getResultList();

long total = entityManager.createQuery("select count(b) from Book b", Long.class).getSingleResult();

Hibernate turns this into the database's paging syntax (OFFSET ... FETCH FIRST ... or LIMIT). Two rules make paging correct:

  • Always sort, and sort by something unique. Without order by, the database may return rows in any order, and pages can overlap or skip rows. Adding the id as a last sort key breaks ties.
  • Offsets get slow on deep pages. To return page 5,000 the database still reads and discards everything before it. For infinite scroll and APIs, use keyset pagination instead: remember the last row you returned and ask for rows after it.
Java Offset paging, a count query and keyset paging

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

The keyset query returns the same rows as page 2, but the database can jump straight to them through an index on title. (Titles are unique here; with duplicates you would compare (title, id) pairs.)

Combining setMaxResults with a join fetch of a collection makes Hibernate fetch all matching rows and page in memory, with a warning in the log (HHH90003004 in Hibernate 6). Page the parent ids first, then fetch their collections. Fetching Strategies and the N+1 Problem covers fetch joins in detail.

Bulk update and delete

JPQL can also change many rows in one statement:

Java
int updated = entityManager
    .createQuery("update Book b set b.price = b.price * 0.9 where b.published < :year")
    .setParameter("year", 1990)
    .executeUpdate();

This runs one SQL UPDATE instead of loading and changing every entity, which is much faster. But it bypasses the persistence context: entities you already loaded keep their old prices in memory, and lifecycle callbacks and @Version increments don't run. Run bulk statements in their own transaction, or call entityManager.clear() afterwards so stale entities are not reused.

The Criteria API

JPQL strings are easy to read but awkward to build dynamically. A search form with six optional filters leads to string concatenation, where 1=1 tricks and parameter bookkeeping. The Criteria API builds the query as an object tree instead:

Java
public List<Book> search(String titlePart, BigDecimal maxPrice, String country) {
    CriteriaBuilder cb = entityManager.getCriteriaBuilder();
    CriteriaQuery<Book> query = cb.createQuery(Book.class);
    Root<Book> book = query.from(Book.class);

    List<Predicate> where = new ArrayList<>();
    if (titlePart != null) {
        where.add(cb.like(cb.lower(book.get(Book_.title)), "%" + titlePart.toLowerCase() + "%"));
    }
    if (maxPrice != null) {
        where.add(cb.le(book.get(Book_.price), maxPrice));
    }
    if (country != null) {
        Join<Book, Author> author = book.join(Book_.author);
        where.add(cb.equal(author.get(Author_.country), country));
    }

    query.select(book)
         .where(where.toArray(Predicate[]::new))
         .orderBy(cb.asc(book.get(Book_.title)));
    return entityManager.createQuery(query).getResultList();
}

Book_ and Author_ are the static metamodel: classes generated at compile time by Hibernate's annotation processor (hibernate-processor, formerly hibernate-jpamodelgen). Book_.price is typed, so cb.le(book.get(Book_.price), maxPrice) only compiles if price is comparable with a BigDecimal, and renaming the field breaks the build instead of a query at runtime. Without the metamodel you can write book.get("price"), but you lose that safety.

Criteria code is verbose; use it for genuinely dynamic queries and plain JPQL for everything else. Spring Data wraps the same idea in Specification objects, and libraries such as Querydsl and jOOQ offer more fluent alternatives.

The core idea of the Criteria API, collecting predicates and parameters separately and joining them at the end, works with plain JDBC too. This runnable version builds safe SQL from an optional-field filter:

Java A dynamic query built from optional filters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with IO.readln() or a Scanner), it gets the next line from this box.

User input only ever travels as parameters, never as part of the SQL text. (A production version would also escape % and _ inside titleContains, as shown in PreparedStatement and SQL Injection.)

Native queries

Sometimes you need SQL that JPQL doesn't cover: window functions, database-specific JSON operators, full-text search, or a carefully tuned query from your DBA. Native queries run your SQL as written:

Java
List<Book> books = entityManager.createNativeQuery("""
        select * from book
        where to_tsvector('english', title) @@ plainto_tsquery('english', :terms)
        """, Book.class)
    .setParameter("terms", "yellow sun")
    .getResultList();

Passing Book.class maps each row to a managed entity, so the query must return all of Book's columns. For scalar results, omit the class to get Object[] rows, or map them with @SqlResultSetMapping or Hibernate's tuple transformers. Native queries tie your code to one database and skip Hibernate's validation, so keep them few and well tested. Remember that Hibernate 6's HQL (a superset of JPQL) already supports many "SQL-only" features, such as common table expressions, union and window functions, so check before dropping down to native SQL.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. In select b from Book b where b.unitPrice > :min, what is unitPrice?

    JPQL talks about entities and their fields. Hibernate maps the field to whatever column it is stored in.
  2. 2. You call getSingleResult() for a query that matches no rows. What happens?

    getSingleResult throws NoResultException for zero rows and NonUniqueResultException for more than one. Use getSingleResultOrNull or getResultList when "not found" is normal.
  3. 3. Why might page 2 contain a row that already appeared on page 1?

    Without a stable, unique sort order the database may return rows in a different order for each page.
  4. 4. After update Book b set b.price = 0 with executeUpdate(), a Book you loaded earlier in the same persistence context shows what price?

    Bulk statements go straight to the database. Clear the persistence context (or use a fresh one) before reading those entities again.
  5. 5. When is the Criteria API a better choice than a JPQL string?

    Criteria shines for dynamic queries. It generates the same SQL as JPQL, so it is not faster, and it is more verbose for fixed queries.

Summary

  • JPQL queries entities and fields (select b from Book b where b.price < :max); paths like b.author.country become joins.
  • Bind values with named (:name) or positional (?1) parameters; never concatenate input into a query.
  • getSingleResult() throws for zero or several rows; prefer getSingleResultOrNull() or getResultList() when "none" is normal.
  • Constructor expressions (select new ...Dto(...)) project straight into records; use DTOs for read-only data.
  • Named queries are validated at startup.
  • Page with setFirstResult/setMaxResults, always with a unique sort order; consider keyset pagination for deep pages.
  • Bulk updates are fast but bypass the persistence context.
  • The Criteria API (with the static metamodel) builds type-safe dynamic queries; native queries cover SQL that JPQL can't express.

Next: Inheritance Mapping and Embeddables.