Part 038 — Hibernate Query Patterns

Query JPA/Hibernate tidak hanya repository.findById.

Production system membutuhkan banyak pola query:

• JPQL untuk entity/projection query;
• Criteria untuk dynamic query;
• native SQL untuk fitur database;
• named query untuk query stabil;
• DTO projection untuk read path;
• tuple mapping untuk ad hoc internal query;
• pagination/keyset;
• sorting whitelist;
• count query yang tidak membunuh database;
• query timeout;
• lock mode;
• read-only hints.

Query yang buruk bisa mengalahkan entity modeling yang baik.

Part ini membahas pola query Hibernate/JPA yang aman, reviewable, dan production-grade.

1. Core Thesis

Query pattern harus dipilih berdasarkan intent:

Need aggregate mutation?
  Entity query with explicit fetch/lock.

Need list/dashboard?
  DTO projection or read model.

Need dynamic filters?
  Query object + Criteria/JPQL builder.

Need vendor feature/window/CTE?
  Native SQL/jOOQ.

Need large export?
  Chunked projection, not entity graph.

Need stable reused query?
  Named query or dedicated DAO method.

Jangan memaksa semua query lewat satu abstraction.

2. JPQL Entity Query

List<CaseFileEntity> cases = entityManager.createQuery("""
    select c
    from CaseFileEntity c
    where c.tenantId = :tenantId
      and c.status = :status
    order by c.updatedAt desc
    """, CaseFileEntity.class)
    .setParameter("tenantId", tenantId)
    .setParameter("status", CaseStatus.OPEN)
    .setMaxResults(50)
    .getResultList();

Returns managed entities.

Use when:

• command needs entities;
• moderate result size;
• transaction/persistence context intended;
• entity graph/fetch plan controlled.

Avoid for:

• dashboard/report/list projection;
• large export;
• read-only API shape;
• unbounded results.

3. JPQL DTO Projection

List<CaseDashboardRow> rows = entityManager.createQuery("""
    select new com.example.CaseDashboardRow(
        c.id,
        c.caseNumber,
        c.status,
        o.displayName,
        c.updatedAt
    )
    from CaseFileEntity c
    left join c.assignedOfficer o
    where c.tenantId = :tenantId
    order by c.updatedAt desc, c.id desc
    """, CaseDashboardRow.class)
    .setParameter("tenantId", tenantId)
    .setMaxResults(limit)
    .getResultList();

DTO projection returns unmanaged objects.

Good for read path.

Caveats:

• constructor signature coupling;
• query string has fully qualified class name depending provider/JPA style;
• complex mapping can be verbose;
• generated SQL still needs review.

4. JPQL Constructor Projection Design

Use record/class with clear constructor.

public record CaseDashboardRow(
        UUID caseId,
        String caseNumber,
        CaseStatus status,
        String officerName,
        Instant updatedAt
) {}

Avoid huge constructor with many nullable fields. Split projection if needed.

For optional values, constructor can accept nullable and wrap later, or field can be nullable internally depending style. Be consistent.

5. Tuple Query

List<Tuple> tuples = entityManager.createQuery("""
    select
        c.id as caseId,
        c.caseNumber as caseNumber,
        c.status as status
    from CaseFileEntity c
    where c.tenantId = :tenantId
    """, Tuple.class)
    .setParameter("tenantId", tenantId)
    .getResultList();

for (Tuple t : tuples) {
    UUID id = t.get("caseId", UUID.class);
}

Useful for internal/ad hoc mapping.

Caveats:

• string aliases;
• runtime errors;
• less type-safe than DTO constructor;
• can become messy.

Prefer DTO projection for stable query.

6. Criteria API

Criteria is useful for dynamic query.

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<CaseDashboardRow> cq = cb.createQuery(CaseDashboardRow.class);

Root<CaseFileEntity> caseFile = cq.from(CaseFileEntity.class);
Join<CaseFileEntity, OfficerEntity> officer =
        caseFile.join("assignedOfficer", JoinType.LEFT);

List<Predicate> predicates = new ArrayList<>();
predicates.add(cb.equal(caseFile.get("tenantId"), query.tenantId()));

query.status().ifPresent(status ->
        predicates.add(cb.equal(caseFile.get("status"), status))
);

cq.select(cb.construct(
        CaseDashboardRow.class,
        caseFile.get("id"),
        caseFile.get("caseNumber"),
        caseFile.get("status"),
        officer.get("displayName"),
        caseFile.get("updatedAt")
));

cq.where(predicates.toArray(Predicate[]::new));
cq.orderBy(cb.desc(caseFile.get("updatedAt")), cb.desc(caseFile.get("id")));

List<CaseDashboardRow> rows = entityManager.createQuery(cq)
        .setMaxResults(query.limit())
        .getResultList();

Pros:

• composable;
• avoids manual string concat;
• dynamic filters.

Cons:

• verbose;
• string field names unless metamodel;
• SQL less visually obvious;
• complex criteria hard to review.

7. Criteria With Static Metamodel

If configured:

caseFile.get(CaseFileEntity_.status)

instead of:

caseFile.get("status")

Pros:

• type safer;
• refactoring support.

Cons:

• metamodel generation setup;
• still verbose.

For highly dynamic query, Criteria can be worthwhile. For fixed query, JPQL string is often more readable.

8. Native Query

Native SQL:

List<Object[]> rows = entityManager.createNativeQuery("""
    select
        c.id,
        c.case_number,
        c.status,
        row_number() over (order by c.updated_at desc) as row_no
    from case_file c
    where c.tenant_id = ?
    order by c.updated_at desc
    limit ?
    """)
    .setParameter(1, tenantId)
    .setParameter(2, limit)
    .getResultList();

Use when:

• JPQL cannot express needed SQL;
• window functions;
• CTE;
• vendor-specific lock;
• advanced pagination;
• performance critical SQL;
• bulk/reporting query.

Cautions:

• manual mapping;
• portability;
• persistence context mismatch;
• SQL injection risk if dynamic;
• migration coupling.

For heavy native SQL, jOOQ/MyBatis may be better.

9. Native Query DTO Mapping

Options:

• manual Object[] mapping;
• Tuple/result set mapping;
• provider-specific transformer;
• @SqlResultSetMapping;
• use JDBC/jOOQ instead.

Manual mapping can be okay for few columns but becomes fragile.

If query is central/complex, consider jOOQ or dedicated JDBC DAO.

10. Named Query

Entity:

@NamedQuery(
    name = "CaseFile.findOpenByTenant",
    query = """
        select c
        from CaseFileEntity c
        where c.tenantId = :tenantId
          and c.status = 'OPEN'
        order by c.updatedAt desc
        """
)

Usage:

entityManager.createNamedQuery(
        "CaseFile.findOpenByTenant",
        CaseFileEntity.class
);

Pros:

• stable name;
• can be validated at startup in some providers;
• reusable.

Cons:

• query far from usage;
• less flexible;
• can clutter entity class;
• still must review SQL/performance.

Use named query for stable, reused queries. Do not put every query as named query.

11. Query Naming for Observability

Even JPQL/native query should have recognizable operation name in application metrics/logging.

Native SQL can include comment:

/* query=CaseDashboardQuery.search */
select ...

JPQL comments may be provider-specific.

At service/DAO layer, record metric:

data.query.duration{query="CaseDashboardQuery.search"}

Names are vital during incident.

12. Derived Query Methods

Spring Data style:

findByTenantIdAndStatusOrderByUpdatedAtDesc(...)

Good for simple queries.

Bad when:

• method name becomes huge;
• dynamic filters;
• fetch plan unclear;
• count query expensive;
• projection needs join;
• lock/timeout/hints needed.

Use derived methods for simple lookup. Use explicit query for important paths.

13. @Query

@Query("""
    select new com.example.CaseDashboardRow(...)
    from CaseFileEntity c
    left join c.assignedOfficer o
    where c.tenantId = :tenantId
    """)
List<CaseDashboardRow> searchDashboard(@Param("tenantId") UUID tenantId);

Good for explicit repository query.

Cautions:

• repository can become query dumping ground;
• complex dynamic query not ideal;
• count query for pageable must be reviewed;
• method contract still matters.

14. Sorting Whitelist

Never concatenate raw sort into JPQL/native SQL.

Bad:

jpql += " order by " + request.sort();

Good:

enum CaseSort {
    UPDATED_DESC("c.updatedAt desc, c.id desc"),
    CASE_NUMBER_ASC("c.caseNumber asc, c.id asc");

    final String jpql;
}

Or Criteria order built from enum.

Request sort string maps to enum before query.

15. Pagination: Offset

query.setFirstResult(offset);
query.setMaxResults(limit);

Good for shallow pages.

Cautions:

• deep offset expensive;
• unstable under concurrent updates;
• requires deterministic order;
• count query can be expensive.

Always order by unique tiebreaker:

updatedAt desc, id desc

16. Pagination: Keyset

JPQL concept:

where c.updatedAt < :updatedAt
   or (c.updatedAt = :updatedAt and c.id < :id)
order by c.updatedAt desc, c.id desc

Use typed cursor:

record CaseCursor(Instant updatedAt, UUID id) {}

Benefits:

• stable deep navigation;
• better performance;
• no large offset.

Limitations:

• no random page number;
• cursor tied to sort/filter;
• more complex API.

17. Count Query

Page often needs count:

select count(c)
from CaseFileEntity c
where c.tenantId = :tenantId

For complex joins, count can be expensive or wrong due duplicates.

Options:

• Slice with limit + 1;
• separate optimized count;
• approximate count;
• count only when requested;
• read model with precomputed counts.

Do not blindly run count for every page.

18. Count With Join Duplication

Query:

select c
from CaseFileEntity c
join c.tags t
where t.name in :tags

Count:

select count(c)

may overcount if multiple tags match.

Need:

select count(distinct c.id)

or exists predicate.

Review count query separately.

19. exists Query

Instead of count > 0:

select 1
from CaseFileEntity c
where c.id = :id

with max result 1, or provider-specific exists pattern.

But existence check is not correctness guard under concurrency.

Use constraints/locks/conditional updates for command correctness.

20. Fetch Join in Query Pattern

select distinct c
from CaseFileEntity c
left join fetch c.assignments
where c.id = :id

Use for bounded aggregate loading.

Do not combine collection fetch join with pageable list.

If fetch join used in query path, document expected collection size.

21. Query Hints

JPA/provider hints can control:

• timeout;
• read-only;
• fetch size;
• cache;
• lock timeout;
• comment;
• fetch graph.

Example concept:

query.setHint("jakarta.persistence.query.timeout", 1000);

Provider support varies.

Use query hints for critical operations, but test on target provider/database.

22. Query Timeout

Long query should not run forever.

Set timeout for:

• interactive queries;
• admin search;
• report preview;
• lock-prone queries.

But timeout is not substitute for index/design.

If query timeout triggers, classify carefully. Retrying bad query can worsen overload.

23. Lock Mode in Query

CaseFileEntity entity = entityManager.createQuery("""
    select c
    from CaseFileEntity c
    where c.id = :id
    """, CaseFileEntity.class)
    .setParameter("id", id)
    .setLockMode(LockModeType.PESSIMISTIC_WRITE)
    .getSingleResult();

Use for command critical section.

Cautions:

• transaction required;
• lock timeout;
• generated SQL;
• deadlock risk;
• fetch graph size.

Lock mode belongs in repository method name/contract.

24. Single Result Methods

getSingleResult() throws if none or more than one.

This can be useful but often awkward.

Alternatives:

getResultStream().findFirst()

or set max results.

For unique lookup, still enforce database unique constraint.

Repository should translate:

• no result -> Optional.empty or NotFound;
• non-unique -> invariant violation.

25. Bounded Result

Always bound list query.

.setMaxResults(limit)

Unbounded entity query is dangerous.

If result may be large, use chunk/cursor/export pattern.

26. Parameter Binding

Always bind values.

Good:

where c.status = :status

Bad:

"where c.status = '" + status + "'"

Even in JPQL, string concat can create injection or syntax bugs.

Only concatenate whitelisted SQL/JPQL fragments like server-defined sort enum.

27. IN Parameter

where c.id in :ids

Cautions:

• empty list behavior provider-specific/invalid;
• huge list performance;
• parameter limit;
• plan issues.

Handle empty list before query:

if (ids.isEmpty()) {
    return List.of();
}

Cap size or use temp table/staging for huge list.

28. Optional Filters

Avoid one generic OR-heavy query if plan suffers:

where (:status is null or c.status = :status)

This is convenient but may hurt index usage.

Dynamic JPQL/Criteria with only active predicates can produce better plans.

Measure on target DB.

29. Query Object With JPQL Builder

Small safe builder:

StringBuilder jpql = new StringBuilder("""
    select new com.example.CaseDashboardRow(...)
    from CaseFileEntity c
    left join c.assignedOfficer o
    where c.tenantId = :tenantId
    """);

Map<String, Object> params = new HashMap<>();
params.put("tenantId", query.tenantId());

if (query.status().isPresent()) {
    jpql.append(" and c.status = :status");
    params.put("status", query.status().get());
}

jpql.append(" order by ").append(query.sort().jpql());

TypedQuery<CaseDashboardRow> typed =
        entityManager.createQuery(jpql.toString(), CaseDashboardRow.class);

params.forEach(typed::setParameter);

Only append trusted fragments.

30. Criteria for Optional Filters

Criteria avoids string concatenation for predicates.

Good for many optional filters.

But do not let Criteria become unreviewable. Keep query object and tests.

For very complex SQL, jOOQ/native may be clearer.

31. Native SQL for CTE/Window

Example:

with ranked_cases as (
    select
        c.*,
        row_number() over (
            partition by c.assigned_officer_id
            order by c.priority desc, c.updated_at asc
        ) as officer_rank
    from case_file c
    where c.tenant_id = ?
)
select *
from ranked_cases
where officer_rank <= 10;

JPQL may not express this well.

Use native SQL or jOOQ.

Do not contort JPQL for advanced SQL if database is source of power.

32. Stored Procedure Query

StoredProcedureQuery query =
        entityManager.createStoredProcedureQuery("close_expired_cases");

query.registerStoredProcedureParameter("cutoff", Instant.class, ParameterMode.IN);
query.setParameter("cutoff", cutoff);
query.execute();

Use when logic intentionally lives in DB.

Contract must document:

• transaction behavior;
• result;
• errors;
• locks;
• idempotency;
• migration/versioning.

33. Query and Flush Interaction

Query can trigger flush.

If query is read-only and you do not want flush of pending changes, reconsider transaction structure.

Do not rely on query order with dirty state.

Pattern:

load all data needed
validate
mutate
append audit/outbox
commit

Avoid:

mutate
query unrelated
mutate more

34. Query and Managed Entity Memory

Entity query loads managed entities into persistence context.

If query returns 10,000 entities:

• memory grows;
• dirty checking cost grows;
• flush slower.

For large read:

• DTO projection;
• streaming/chunk;
• clear;
• stateless approach;
• native/JDBC.

35. Streaming Query

JPA streaming support varies by provider/framework.

If using stream:

try (Stream<CaseExportRow> stream = query.getResultStream()) {
    stream.forEach(writer::write);
}

Contract:

• close stream;
• transaction/session open while consuming;
• do not do slow external calls while holding resources unless designed;
• fetch size/provider settings may be needed.

For export, chunked cursor is often more robust.

36. Read-Only Query

For read-only entity query, provider hint can reduce overhead.

But DTO projection is clearer.

Use read-only entity query if:

• need entity shape;
• no mutation;
• moderate size;
• provider behavior understood.

37. Query Cache Caveat

Hibernate query cache can cache query result identifiers.

Danger:

• invalidation complexity;
• stale reads if misconfigured;
• memory pressure;
• low hit rate for dynamic filters;
• not a fix for bad query.

Use cautiously, mostly for stable reference data queries.

38. First-Level Cache and Query Result

JPQL entity query returns managed entities. If entity already in persistence context, result may reuse existing instance.

If persistence context stale due native update/bulk, query can still return stale managed state.

Clear context after bulk/native updates or separate transaction.

39. Querying by Association vs ID

JPQL:

where c.assignedOfficer = :officer

requires entity reference.

Often simpler:

where c.assignedOfficer.id = :officerId

or if FK field mapped:

where c.assignedOfficerId = :officerId

Avoid loading reference entity solely for query unless needed.

40. getReference for Query Parameter

If query expects entity association:

OfficerEntity officerRef =
        entityManager.getReference(OfficerEntity.class, officerId);

query.setParameter("officer", officerRef);

Can avoid select.

But query by ID is usually clearer.

41. Query Result Cardinality

Match method return to cardinality:

Optional<CaseFileEntity> findById(...)
CaseFileEntity getRequired(...)
List<CaseDashboardRow> search(...)
Slice<CaseDashboardRow> searchPage(...)
long count(...)
boolean exists(...)

Do not return list for unique result unless multiple is valid.

42. Query Exception Translation

Common exceptions:

• no result;
• non-unique result;
• persistence exception;
• query timeout;
• lock timeout;
• optimistic/pessimistic lock exception;
• constraint violation due flush;
• syntax/mapping error.

Repository/query service should translate to semantic/data access exception.

Do not leak raw provider exception to controller everywhere.

43. Query Testing

Test:

• SQL/JPQL syntax through integration;
• mapping to DTO;
• optional filters;
• sorting;
• pagination;
• count correctness;
• tenant/authorization;
• N+1/query count;
• timeout/lock behavior if critical;
• empty IN handling;
• duplicate/non-unique cardinality;
• flush side effects if command query mixed.

44. Query Plan Review

For critical query, review generated SQL and DB plan.

Questions:

• are joins expected?
• are predicates indexed?
• does order by use index?
• is count query expensive?
• is distinct needed due join?
• does fetch join multiply rows?
• is pagination applied in DB?
• are bind types correct?
• are tenant predicates present?

ORM query is not exempt from database engineering.

45. Query Review Checklist

• Query intent is clear.
• Return type matches projection/entity need.
• Result is bounded.
• Parameters are bound.
• Sort is whitelisted.
• Pagination deterministic.
• Count query reviewed or avoided.
• Fetch join not combined with unsafe pagination.
• Tenant/security predicate present.
• Query does not rely on lazy loading later.
• Query timeout considered.
• Lock mode explicit if used.
• Generated SQL reviewed for critical path.
• Tests cover filters/sort/page.
• Metrics use query name.

46. Anti-Pattern: Derived Method Name as Architecture

Long repository method names are signal to move to query object/explicit query.

47. Anti-Pattern: Unbounded getResultList

Always bound unless data inherently tiny.

48. Anti-Pattern: Fetch Join All Associations

Graph explosion.

Fetch only what use case needs.

49. Anti-Pattern: Raw Request Sort in Query String

Injection and unstable query.

Whitelist.

50. Anti-Pattern: Count Query Ignored

Pagination can be slow because count is slow, not data query.

Review count.

51. Anti-Pattern: JPQL for Everything

Use native/jOOQ/MyBatis/JDBC when SQL feature or performance requires.

52. Anti-Pattern: Native SQL Everywhere Without Mapping Discipline

Native SQL is powerful but can become string chaos.

Use DAO/query object/tests/query names.

53. Mini Lab

Design query layer for:

Case supervisor dashboard:
- optional status;
- optional priority;
- optional assigned officer;
- keyword on case number;
- visible units scope;
- sort by updated desc or priority desc;
- shallow offset page for UI;
- export all matching cases asynchronously;

Questions:

1. Which query uses JPQL DTO projection?
2. Which query needs Criteria/dynamic builder?
3. Which query should be native SQL or read model?
4. How is sort whitelisted?
5. How is count handled?
6. How does export avoid offset?
7. What indexes are needed?
8. What query count budget?
9. What timeout?
10. What tests?

54. Summary

Hibernate/JPA query patterns must be selected intentionally.

You must master:

• JPQL entity query;
• JPQL DTO projection;
• Tuple;
• Criteria API;
• native query;
• named query;
• derived query method limits;
• sorting whitelist;
• offset vs keyset pagination;
• count query cost;
• fetch join rules;
• query hints;
• timeout;
• lock mode;
• parameter binding;
• optional filter design;
• streaming/chunking;
• query exception translation;
• generated SQL/plan review;
• query tests and metrics.

Part berikutnya membahas JPA Locking and Versioning: @Version, optimistic lock, stale update, pessimistic lock, lock timeout, retry design, and how to map lock/concurrency failures to application semantics.

55. References

• Jakarta Persistence Specification: https://jakarta.ee/specifications/persistence/3.2/jakarta-persistence-spec-3.2
• Hibernate ORM User Guide: https://docs.hibernate.org/stable/orm/userguide/html_single/
• Spring Data JPA Reference: https://docs.spring.io/spring-data/jpa/reference/
• PostgreSQL Indexes: https://www.postgresql.org/docs/current/indexes.html