278 lines
13 KiB
Markdown
278 lines
13 KiB
Markdown
# 15 — HQL: the query language, its pitfalls, and what flush mode actually controls
|
|
|
|
[← Previous: 14 — Named queries](14-named-queries.md) | [Back to README →](../README.md) | [Next: 16 — Criteria API →](16-criteria-queries.md)
|
|
|
|
Backs ankurm.com post 4879 (HQL queries).
|
|
|
|
Everything below comes from [`HqlQueryTest`](../src/test/java/com/ankurm/hibernatedemo/query/HqlQueryTest.java),
|
|
run against Hibernate 7.4.5.Final / H2 2.4.240, using two new entities built for this chapter and
|
|
the next: [`Employee`](../src/main/java/com/ankurm/hibernatedemo/query/Employee.java) and
|
|
[`Department`](../src/main/java/com/ankurm/hibernatedemo/query/Department.java) (mapped with
|
|
`@Entity(name = "QueryDept")` -- the plain class name `Department` was already taken by chapter
|
|
06's `naturalid.Department`, and Hibernate requires entity names to be unique across the whole
|
|
persistence unit, not just per package).
|
|
|
|
## `FROM` and `WHERE`, and the pitfall that fails loudly
|
|
|
|
The smallest HQL query skips `SELECT` entirely:
|
|
|
|
```java
|
|
em.createQuery("FROM Employee", Employee.class).getResultList();
|
|
```
|
|
|
|
A `WHERE` clause with a named parameter is the normal shape for anything filtered:
|
|
|
|
```java
|
|
em.createQuery("SELECT e FROM Employee e WHERE e.status = :status", Employee.class)
|
|
.setParameter("status", "ACTIVE")
|
|
.getResultList();
|
|
```
|
|
|
|
**The classic mistake**: writing the database column name instead of the entity field name.
|
|
`Employee`'s column is `first_name`, but its Java field is `firstName`. HQL resolves against the
|
|
*entity model*, not the schema, so this fails before any SQL is even generated -- not a silent
|
|
wrong-result bug, a loud one:
|
|
|
|
```
|
|
columnNameInsteadOfFieldName: IllegalArgumentException: org.hibernate.query.sqm.UnknownPathException: Could not resolve attribute 'first_name' of 'com.ankurm.hibernatedemo.query.Employee' [SELECT e FROM Employee e WHERE e.first_name = 'Ada']
|
|
```
|
|
|
|
> **Trap**: this only fails loudly because the attribute name is *wrong*. If you'd written
|
|
> `e.department.name` where the association happens to share a column name with something on the
|
|
> root entity, HQL still resolves it correctly -- the failure mode above only catches typos, not
|
|
> confusion about what a path actually points to. Read the exception type, not just its presence:
|
|
> `UnknownPathException` means "this attribute doesn't exist," not "this predicate is slow" or
|
|
> "this join is wrong."
|
|
|
|
Source: [`HqlQueryTest`](../src/test/java/com/ankurm/hibernatedemo/query/HqlQueryTest.java). Raw
|
|
output: [`docs/output/hql-select-and-joins.txt`](output/hql-select-and-joins.txt).
|
|
|
|
Going deeper:
|
|
- [Jakarta Persistence 3.2 query language spec, §4](https://jakarta.ee/specifications/persistence/3.2/jakarta-persistence-spec-3.2#query-language) (`rel="nofollow"`)
|
|
- Chapter 14's [named-query startup validation](14-named-queries.md#startup-validation) catches this same class of typo at boot instead of at call time, if you move the query into a `@NamedQuery`
|
|
|
|
## `JOIN` without `FETCH` does not prevent the N+1 it looks like it prevents
|
|
|
|
A plain `JOIN` in HQL is there to *filter*, not to *load*. It issues one SELECT for the query
|
|
itself, but touching the association afterward still fires a separate SELECT per distinct value:
|
|
|
|
```java
|
|
List<Employee> withDept = em.createQuery(
|
|
"SELECT e FROM Employee e JOIN e.department d WHERE d.name = :deptName", Employee.class)
|
|
.setParameter("deptName", "Engineering")
|
|
.getResultList();
|
|
// 1 statement so far
|
|
withDept.forEach(e -> e.getDepartment().getName());
|
|
// now more than 1 -- one extra SELECT per distinct department touched
|
|
```
|
|
|
|
```
|
|
joinWithoutFetch: 1 statements for the query, 2 after touching department
|
|
```
|
|
|
|
`JOIN FETCH` is the fix -- it loads the association eagerly in the same query, so touching it
|
|
afterward costs nothing extra:
|
|
|
|
```java
|
|
em.createQuery("SELECT e FROM Employee e LEFT JOIN FETCH e.department", Employee.class).getResultList();
|
|
```
|
|
|
|
```
|
|
joinFetch: 1 statement total, 5 rows
|
|
```
|
|
|
|
Source: [`HqlQueryTest`](../src/test/java/com/ankurm/hibernatedemo/query/HqlQueryTest.java). Raw
|
|
output: [`docs/output/hql-select-and-joins.txt`](output/hql-select-and-joins.txt).
|
|
|
|
This is the single most common Hibernate performance bug in production code, and it looks
|
|
completely correct on a code review: the `JOIN` is *right there*, so it reads as "this loads the
|
|
department." It doesn't. The distinction only shows up under a profiler or `Statistics` counters,
|
|
which is exactly what this test uses instead of trusting the query text.
|
|
|
|
Going deeper:
|
|
- Chapter 12 covers the mapping side of this -- [`@ManyToOne(fetch = LAZY)` and when eager beats lazy](12-association-mappings.md)
|
|
- Chapter 11's [proxy and lazy-initialization chapter](11-proxies-and-lazy-initialization.md) is the deeper mechanism: what `getDepartment()` actually returns before it's touched
|
|
|
|
## Aggregates, `GROUP BY`, and pagination
|
|
|
|
Straightforward HQL: `COUNT`, `AVG` with `GROUP BY`, and `setFirstResult`/`setMaxResults` for
|
|
paging.
|
|
|
|
```java
|
|
em.createQuery("SELECT COUNT(e) FROM Employee e", Long.class).getSingleResult();
|
|
|
|
em.createQuery(
|
|
"SELECT d.name, AVG(e.salary) FROM Employee e JOIN e.department d GROUP BY d.name ORDER BY d.name",
|
|
Object[].class).getResultList();
|
|
```
|
|
|
|
```
|
|
aggregateCount: 5
|
|
avgSalaryGroupByDepartment: Engineering -> 95000.0
|
|
avgSalaryGroupByDepartment: Marketing -> 71500.0
|
|
```
|
|
|
|
Both averages were hand-computed from the seed data and asserted to match exactly (within a
|
|
0.01 offset for floating-point rounding) -- not just "a number came back."
|
|
|
|
Pagination combines `setFirstResult` (offset) and `setMaxResults` (limit), ordered so the pages
|
|
are deterministic:
|
|
|
|
```java
|
|
em.createQuery("FROM Employee e ORDER BY e.lastName", Employee.class)
|
|
.setFirstResult(0).setMaxResults(2).getResultList();
|
|
```
|
|
|
|
```
|
|
pagination: page1=[Byron, Hamilton], page2=[Hopper, Johnson]
|
|
```
|
|
|
|
Source: [`HqlQueryTest`](../src/test/java/com/ankurm/hibernatedemo/query/HqlQueryTest.java). Raw
|
|
output: [`docs/output/hql-aggregation-paging-bulk.txt`](output/hql-aggregation-paging-bulk.txt).
|
|
|
|
For pagination past a few thousand rows, offset-based paging degrades because the database still
|
|
has to scan and discard every skipped row -- chapter 16's [pagination-adjacent bulk operations](16-criteria-queries.md)
|
|
and chapter 09's [in-memory database chapter](09-testing-in-memory-databases.md) both touch on
|
|
where that starts to matter; keyset pagination is the usual fix, out of scope for this chapter.
|
|
|
|
## Bulk `UPDATE` and `DELETE` bypass the persistence context
|
|
|
|
HQL's `UPDATE`/`DELETE` execute directly against the database as a single SQL statement -- they
|
|
do **not** load entities into the persistence context first, and they do **not** update any
|
|
entity that's already loaded there:
|
|
|
|
```java
|
|
Employee loaded = em.createQuery("SELECT e FROM Employee e WHERE e.lastName = 'Torvalds'", Employee.class)
|
|
.getSingleResult(); // status = INACTIVE, now managed
|
|
|
|
int updated = em.createQuery("UPDATE Employee e SET e.status = 'ARCHIVED' WHERE e.status = 'INACTIVE'")
|
|
.executeUpdate();
|
|
// updated == 1, and the row in the DATABASE now says ARCHIVED
|
|
|
|
// but `loaded` -- already in the persistence context -- still says INACTIVE
|
|
```
|
|
|
|
```
|
|
bulkUpdate: updated=1 rows, stale in-memory status=INACTIVE, reloaded status=ARCHIVED
|
|
```
|
|
|
|
Only after `em.clear()` and a fresh `find()` does the already-loaded entity's Java field catch up
|
|
to what the database now holds. This is the same "bulk operations bypass the persistence context"
|
|
warning that appears throughout Hibernate's own reference docs, demonstrated here by asserting the
|
|
stale value stays stale until a clear-and-reload, not just stating it.
|
|
|
|
`DELETE` bulk operations are simpler -- one SQL statement, no persistence-context interaction to
|
|
worry about since there's no entity state left to go stale:
|
|
|
|
```java
|
|
em.createQuery("DELETE FROM Employee e WHERE e.department.id = :deptId")
|
|
.setParameter("deptId", marketingId).executeUpdate();
|
|
```
|
|
|
|
```
|
|
bulkDelete: deleted=2 rows, remaining=3
|
|
```
|
|
|
|
Source: [`HqlQueryTest`](../src/test/java/com/ankurm/hibernatedemo/query/HqlQueryTest.java). Raw
|
|
output: [`docs/output/hql-aggregation-paging-bulk.txt`](output/hql-aggregation-paging-bulk.txt).
|
|
|
|
> **Trap**: `em.remove()` on a *managed* entity is not the same operation as bulk `DELETE`.
|
|
> `remove()` triggers cascades and lifecycle callbacks per entity; bulk `DELETE` is one SQL
|
|
> statement against the database and skips both. Choose deliberately -- bulk `DELETE` is much
|
|
> faster for large sets, `remove()` is correct when cascades or `@PreRemove` matter.
|
|
|
|
## Flush mode: what actually suppresses the auto-flush, and what doesn't
|
|
|
|
This section exists because of a dead end worth documenting. The first version of these tests
|
|
tried to prove flush-mode behavior by `persist()`-ing a *new* `Employee` and checking whether a
|
|
later query saw it. That approach is broken by construction: `Employee`'s `@Id` uses
|
|
`GenerationType.IDENTITY`, and **IDENTITY forces an immediate INSERT on `persist()`, independent
|
|
of flush mode entirely** -- Hibernate has to round-trip to the database right away to obtain the
|
|
generated key, before it can even hand back a usable entity reference. Flush mode never got a
|
|
chance to defer anything. (This is the same fact chapter 03 documents from the insert side --
|
|
[`GenerationType.IDENTITY` disables JDBC batching](03-inserting-objects.md) for the identical
|
|
underlying reason.)
|
|
|
|
The fix: dirty an **already-loaded, already-managed** entity with `setSalary(...)` instead of
|
|
inserting a new one. An `UPDATE` is not tied to id generation, so this correctly isolates what
|
|
flush mode controls.
|
|
|
|
**Default (`AUTO`)** flushes the dirty change before a query that could be affected by it runs, so
|
|
a fresh query in the same transaction sees the update even though nothing called `flush()`
|
|
explicitly:
|
|
|
|
```java
|
|
Employee ada = em.createQuery("SELECT e FROM Employee e WHERE e.lastName = 'Byron'", Employee.class).getSingleResult();
|
|
ada.setSalary(999_999.0); // dirtied, not explicitly flushed
|
|
Double seen = em.createQuery("SELECT e.salary FROM Employee e WHERE e.lastName = 'Byron'", Double.class).getSingleResult();
|
|
```
|
|
|
|
```
|
|
defaultFlushMode: salary seen by a fresh query after an unflushed dirty change = 999999.0
|
|
```
|
|
|
|
**`jakarta.persistence.FlushModeType.COMMIT`** is *implementation-defined*, not a guarantee --
|
|
its own javadoc says a provider is "permitted, but not required" to flush before a query. Verified
|
|
directly against Hibernate 7.4.5: it chooses **not** to flush, so the same dirty change stays
|
|
invisible to a query set to this mode:
|
|
|
|
```java
|
|
.setFlushMode(jakarta.persistence.FlushModeType.COMMIT)
|
|
```
|
|
|
|
```
|
|
jakartaCommitFlushMode: salary seen by query under FlushModeType.COMMIT = 98000.0 (pre-update value was 98000.0)
|
|
```
|
|
|
|
**Hibernate's own native `org.hibernate.FlushMode.MANUAL`** has no `jakarta.persistence`
|
|
equivalent, and it is the one mode that genuinely, unconditionally suppresses auto-flush until an
|
|
explicit `session.flush()` call:
|
|
|
|
```java
|
|
Session session = em.unwrap(Session.class);
|
|
session.setHibernateFlushMode(FlushMode.MANUAL);
|
|
linus.setSalary(123_123.0);
|
|
// query here still sees the OLD value
|
|
session.flush();
|
|
// query here sees the NEW value
|
|
```
|
|
|
|
```
|
|
nativeManualFlushMode: before explicit flush=92000.0, after=123123.0
|
|
```
|
|
|
|
Source: [`HqlQueryTest`](../src/test/java/com/ankurm/hibernatedemo/query/HqlQueryTest.java). Raw
|
|
output: [`docs/output/hql-flush-modes.txt`](output/hql-flush-modes.txt).
|
|
|
|
> **Trap**: if you're trying to prove (or debug) flush-mode behavior and your test entity uses
|
|
> `GenerationType.IDENTITY`, use an `UPDATE` on an already-managed row, not a `persist()`. This
|
|
> cost real debugging time in this repo before the root cause -- IDENTITY's forced immediate
|
|
> INSERT -- was found by reading the log line ordering rather than assuming flush mode was
|
|
> broken.
|
|
|
|
This distinction matters most in tight loops: `FlushMode.MANUAL` plus batched explicit flushes is
|
|
a real, measurable performance technique for bulk write-heavy code (see chapter 03's batching
|
|
findings), but only for updates -- not for anything that also needs a database-generated identity
|
|
key back immediately.
|
|
|
|
Going deeper:
|
|
- [`jakarta.persistence.FlushModeType` javadoc](https://jakarta.ee/specifications/persistence/3.2/apidocs/jakarta.persistence/jakarta/persistence/flushmodetype) (`rel="nofollow"`) -- the exact "permitted, but not required" wording
|
|
- Chapter 03's [`GenerationType.IDENTITY` and JDBC batching](03-inserting-objects.md)
|
|
|
|
## Summary
|
|
|
|
| Claim | Verified value |
|
|
|---|---|
|
|
| Column name instead of field name in HQL | Fails at query-build time with `UnknownPathException`, not silently |
|
|
| `JOIN` alone (no `FETCH`) | Filters correctly, but touching the association after still costs N extra SELECTs |
|
|
| `JOIN FETCH` | Loads root + association in exactly one SELECT |
|
|
| `GROUP BY` averages | Match hand-computed values exactly |
|
|
| Pagination (`setFirstResult`/`setMaxResults`) | Returns correct, non-overlapping slices when ordered |
|
|
| Bulk `UPDATE`/`DELETE` | Execute directly against the DB; already-loaded managed entities go stale until `clear()` + reload |
|
|
| `GenerationType.IDENTITY` + flush mode | IDENTITY forces immediate INSERT on `persist()`, defeating flush-mode-based INSERT deferral entirely |
|
|
| Default `AUTO` flush | Flushes a dirty UPDATE before a query that could see it |
|
|
| `FlushModeType.COMMIT` (jakarta) | Implementation-defined; Hibernate 7.4.5 chooses not to flush |
|
|
| `FlushMode.MANUAL` (Hibernate native) | Genuinely, unconditionally suppresses auto-flush until explicit `flush()` |
|
|
|
|
[← Previous: 14 — Named queries](14-named-queries.md) | [Back to README →](../README.md) | [Next: 16 — Criteria API →](16-criteria-queries.md)
|