Hibernate Under the Hood. Part 4: Querying

The chapter on querying languages is where Hibernate stops being just "magic" and becomes a manageable tool. Here, it is crucial to understand three approaches and their boundaries: HQL/JPQL (declarative, object-oriented), Criteria API (programmatic, type-safe), and Native SQL (direct database access). In Spring projects, there is an additional layer on top of this: Spring Data JPA, which changes how you write 90% of your queries.

HQL and JPQL: Object-Oriented SQL

HQL is Hibernate's proprietary language, while JPQL is the JPA standard inspired by early HQL. It is important to know the relationship: HQL is a superset of JPQL. Any valid JPQL query is a valid HQL query, but not vice versa: HQL supports more features (e.g., insert ... select, which is absent in JPQL).

The main difference from SQL is that you operate on entities and their fields, not tables and columns. In HQL, Book is a Java class, and book.title is its field. Direct referencing of database table and column names is prohibited. Because of this, HQL is more compact than SQL and portable across different databases.

Mapping Example:

-- SQL
SELECT book.title, pub.name
FROM Book AS book
JOIN Publisher AS pub ON book.publisherId = pub.id
WHERE book.title LIKE 'Hibernate%'
ORDER BY book.title

// HQL / JPQL
SELECT book.title, pub.name
FROM Book AS book
JOIN book.publisher AS pub
WHERE book.title LIKE 'Hibernate%'
ORDER BY book.title

Note: the join is performed on the association (book.publisher), not on the foreign key column. Hibernate knows how to resolve it automatically.

Key HQL features you need to know:

• Parameters: Named (:name) and positional (?1). Named parameters are preferred for readability, while positional ones are often used in native queries.
• JOIN FETCH: The primary tool for combating the N+1 query problem. It loads the association immediately in a single query.
• DTO projections: You can return a DTO directly instead of an entity, e.g., new com.example.dto.BookDto(book.title, pub.name). This saves memory and avoids creating managed objects.
• update / delete: Bulk operations executed as a single SQL statement that bypass the Persistence Context (in-memory entities will not be updated — this is a pitfall).

HQL Pitfalls:

• Bulk update/delete does not synchronize with the first-level cache. If you loaded a Book into the session and then executed update Book set title = ..., the in-memory object will retain the old title. You need session.clear() or refresh().
• JOIN FETCH on two collections simultaneously results in a MultipleBagFetchException or a Cartesian product. The solution is to use @BatchSize / SUBSELECT, or split it into two separate queries.
• JOIN FETCH + pagination (setMaxResults) can yield incorrect results: Hibernate applies the limit in memory, not in the SQL query. This is a known limitation.

Criteria API: Queries as Code

The Criteria API is a programmatic way to build queries. Instead of a string, you construct the query from objects: CriteriaBuilder, CriteriaQuery, Root, and Predicate. This provides type safety (the compiler checks field names if you use the metamodel) and flexibility for dynamic conditions.

When the Criteria API is indispensable:

• Search screens with a variable set of filters. For example, a user might specify a price, category, brand — or nothing at all. Concatenating an HQL string with if statements is a source of bugs; the Criteria API handles this cleanly.
• Dynamic sorting and pagination with type checking.
• Building queries in libraries and frameworks where you do not know the entity structure in advance.

Example (simplified):

CriteriaBuilder cb = session.getCriteriaBuilder();
CriteriaQuery<Book> cq = cb.createQuery(Book.class);
Root<Book> book = cq.from(Book.class);

List<Predicate> predicates = new ArrayList<>();
if (title != null) predicates.add(cb.like(book.get("title"), "%" + title + "%"));
if (minPrice != null) predicates.add(cb.ge(book.get("price"), minPrice));

cq.where(predicates.toArray(new Predicate[0]));
cq.orderBy(cb.asc(book.get("title")));

List<Book> result = session.createQuery(cq).getResultList();

Drawbacks of the Criteria API:

• Verbosity. A simple query turns into 10–15 lines of code.
• Poorer readability compared to HQL, especially for complex joins.
• Not for "one-off" queries. If a query is static, HQL is shorter and clearer.

Practical rule: static queries → HQL/JPQL; dynamic queries (search, filters) → Criteria API.

Native SQL: When ORM is Powerless

Sometimes you need to drop down to the database level. Native SQL is justified when:

• You need a database-specific feature (window functions, LATERAL JOIN, hints, FOR UPDATE SKIP LOCKED, specific syntax).
• The query is too complex for HQL and easier to write in SQL than to assemble via Criteria.
• You need maximum performance in a bottleneck where HQL generates suboptimal SQL.
• You are working with stored procedures.

Example:

Book book = session.createNativeQuery(
    "SELECT * FROM books WHERE isbn = ?1", Book.class)
    .setParameter(1, isbn)
    .getSingleResult();

Native Query Pitfalls:

1. Flush behavior: Hibernate does not flush by default before executing native queries, because it does not know which tables the query will affect. If there are unsaved changes in the Persistence Context, the native query might return stale data. The solution is an explicit session.flush() before the query or configuring the FlushMode.
2. Result mapping: For simple cases (SELECT * matching an entity), Hibernate infers the mapping automatically. For complex queries (aliases, aggregates, joins), you need @SqlResultSetMapping or manual parsing of Object[], which is cumbersome and fragile.
3. Portability: Native SQL ties you to a specific database dialect. If you plan to switch databases, use it sparingly.

Spring Data JPA: How It Looks

In Spring Boot, you rarely write session.createQuery() directly. Instead, you use Spring Data JPA repositories, which offer three levels of abstraction:

• Derived queries (from method names): findByEmailAddressAndLastname(String email, String lastname) is automatically translated into JPQL: SELECT u FROM User u WHERE u.emailAddress = ?1 AND u.lastname = ?2. This works for simple queries, but method names become cumbersome with 3+ conditions.
• @Query with JPQL/HQL: An explicit query on a repository method. This is the standard for medium-complexity queries:

   @Query("SELECT b FROM Book b JOIN FETCH b.author WHERE b.title LIKE %:title%")
   List<Book> findWithAuthorByTitle(@Param("title") String title);

• @Query(nativeQuery = true): Native SQL when JPQL is insufficient. The nativeQuery flag explicitly tells Spring Data that this is not JPQL.

Important: Spring Data JPA does not generate JPQL for UPDATE and DELETE from method names — only for SELECT. For modifications, you need @Query + @Modifying.

What to Use When

Approach	When to choose	Pros	Cons
HQL/JPQL	Static queries, 80% of cases	Compact, portable, readable	Not for dynamic queries; strings are not checked by the compiler
Criteria API	Dynamic filters, search, libraries	Type-safe, flexible	Verbose, harder to read
Native SQL	DB specifics, complex queries, performance	Full control over SQL	Not portable, requires manual mapping, flush nuances
Spring Data (derived)	Simple CRUD and selections	Minimal code	Cumbersome names, SELECT only
Spring Data @Query	Medium-complexity queries	Explicit, readable, located in the repository	Strings are not checked by the compiler

What to Remember

1. HQL is a superset of JPQL. Write in JPQL for portability; use HQL features consciously.
2. HQL operates on entities, not tables. This is its strength (compactness, portability) and its limitation (cannot use DB-specific features).
3. Criteria API is for dynamic queries, HQL is for static ones. Do not try to build a simple query using Criteria — it is overkill.
4. Native SQL is a targeted tool. Always remember about flush and result mapping.
5. JOIN FETCH is your best friend against N+1, but it has limitations (pagination, two collections).
6. Bulk update/delete in HQL does not synchronize with the first-level cache. You need clear() after them.
7. In Spring Data, 90% of queries are @Query with JPQL. Derived queries are for simple cases, native queries are for exceptions.