Part 027 — Query Object and Specification Pattern

Query flexibility adalah area yang mudah berubah menjadi chaos.

Awalnya hanya:

findByStatus(status)

Lalu bertambah:

findByStatusAndOfficer(status, officer)
findByStatusAndOfficerAndDateRange(...)
findByStatusAndUnitAndKeywordAndPriority(...)
findByEverything(...)

Jika tidak didesain, data access layer menjadi ledakan method, SQL injection risk, query plan tidak stabil, pagination rusak, dan query ownership kabur.

Part ini membahas Query Object dan Specification Pattern untuk query yang fleksibel tetapi tetap reviewable.

1. Core Thesis

Query Object adalah object yang merepresentasikan parameter query secara eksplisit.

Specification adalah object/predicate yang merepresentasikan kondisi yang bisa dikomposisi.

Tujuan keduanya:

Make query intent explicit,
avoid method explosion,
keep SQL safe,
keep query ownership clear,
and preserve performance reviewability.

Bukan tujuan:

Create a universal query engine for everything.

Jika terlalu generik, Query Object berubah menjadi mini database language yang sulit dikontrol.

2. Problem: Method Explosion

Bad repository/DAO API:

List<CaseFile> findByStatus(CaseStatus status);
List<CaseFile> findByOfficer(OfficerId officerId);
List<CaseFile> findByStatusAndOfficer(CaseStatus status, OfficerId officerId);
List<CaseFile> findByStatusAndOfficerAndOpenedAtBetween(...);
List<CaseFile> findByStatusAndPriorityAndUnitAndKeyword(...);

Problems:

• API surface meledak;
• duplicate SQL;
• inconsistent ordering;
• inconsistent tenant filter;
• inconsistent pagination;
• hard to test all combinations;
• performance unpredictable;
• method name tidak scalable.

Better:

CaseSearchResult search(CaseSearchQuery query);

Where CaseSearchQuery is explicit and validated.

3. Query Object Basic Shape

public record CaseSearchQuery(
        TenantId tenantId,
        Optional<CaseStatus> status,
        Optional<OfficerId> assignedOfficerId,
        Optional<UnitId> unitId,
        Optional<String> keyword,
        Optional<LocalDate> openedFrom,
        Optional<LocalDate> openedTo,
        CaseSort sort,
        PageRequest page
) {
    public CaseSearchQuery {
        Objects.requireNonNull(tenantId);
        Objects.requireNonNull(status);
        Objects.requireNonNull(assignedOfficerId);
        Objects.requireNonNull(unitId);
        Objects.requireNonNull(keyword);
        Objects.requireNonNull(openedFrom);
        Objects.requireNonNull(openedTo);
        Objects.requireNonNull(sort);
        Objects.requireNonNull(page);

        page = page.normalized(1, 200);
    }
}

Characteristics:

• tenant scope explicit;
• optional filters explicit;
• sort is enum/whitelist;
• page is bounded;
• validation at boundary;
• query object is immutable.

4. Query Object Is Not Request DTO

HTTP request DTO:

public record CaseSearchRequest(
        String status,
        String officerId,
        String keyword,
        Integer limit,
        String sort
) {}

Query object:

public record CaseSearchQuery(
        TenantId tenantId,
        Optional<CaseStatus> status,
        Optional<OfficerId> officerId,
        Optional<Keyword> keyword,
        CaseSort sort,
        PageRequest page
) {}

Convert in application/controller layer:

CaseSearchQuery query = requestMapper.toQuery(request, currentTenant);

Reason:

• request DTO is transport-facing;
• query object is application/data-access-facing;
• parsing/validation should happen before DAO;
• DAO should not parse arbitrary strings from HTTP.

5. Query Object Should Be Use-Case Specific

Good:

CaseDashboardQuery
CaseExportQuery
CaseBackfillQuery
OfficerWorkloadSearchQuery

Bad:

UniversalQuery {
    Map<String, Object> filters;
    List<String> sort;
    String rawWhere;
}

Use-case-specific query object keeps:

• allowed filters limited;
• SQL reviewable;
• indexes predictable;
• validation meaningful;
• authorization/scope clear.

Do not expose database flexibility as public API unless building an internal query platform deliberately.

6. Query Object and DAO

DAO method:

public Slice<CaseDashboardRow> search(
        Connection connection,
        CaseDashboardQuery query
);

Implementation owns SQL:

StringBuilder sql = new StringBuilder("""
    /* query=CaseDashboardDao.search */
    select
        c.id,
        c.case_number,
        c.status,
        c.priority,
        c.updated_at,
        o.display_name as officer_name
    from case_file c
    left join officer o on o.id = c.assigned_officer_id
    where c.tenant_id = ?
    """);

Then append filters safely.

7. Dynamic SQL Safely

public Slice<CaseDashboardRow> search(Connection connection, CaseDashboardQuery query) {
    SqlParts parts = new SqlParts("""
        /* query=CaseDashboardDao.search */
        select
            c.id,
            c.case_number,
            c.status,
            c.priority,
            c.updated_at,
            o.display_name as officer_name
        from case_file c
        left join officer o on o.id = c.assigned_officer_id
        where c.tenant_id = ?
        """);

    parts.bind(ps -> ps.setObject(query.tenantId().value()));

    query.status().ifPresent(status -> {
        parts.append(" and c.status = ?");
        parts.bind(ps -> ps.setString(status.dbCode()));
    });

    query.assignedOfficerId().ifPresent(officerId -> {
        parts.append(" and c.assigned_officer_id = ?");
        parts.bind(ps -> ps.setObject(officerId.value()));
    });

    query.keyword().ifPresent(keyword -> {
        parts.append(" and lower(c.case_number) like ?");
        parts.bind(ps -> ps.setString("%" + keyword.normalized() + "%"));
    });

    parts.append(" order by ").append(query.sort().sql());
    parts.append(" limit ?");
    parts.bind(ps -> ps.setInt(query.page().limitPlusOne()));

    return executeSearch(connection, parts);
}

Important:

• values are bound;
• sort is whitelisted enum;
• limit is bounded;
• no raw user SQL;
• query shape still understandable.

8. Simple SQL Builder

A small internal helper can reduce boilerplate.

public final class SqlParts {
    private final StringBuilder sql;
    private final List<SqlParameterBinder> binders = new ArrayList<>();

    public SqlParts(String baseSql) {
        this.sql = new StringBuilder(baseSql);
    }

    public SqlParts append(String fragment) {
        sql.append(fragment);
        return this;
    }

    public void bind(SqlParameterBinder binder) {
        binders.add(binder);
    }

    public PreparedStatement prepare(Connection connection) throws SQLException {
        PreparedStatement ps = connection.prepareStatement(sql.toString());

        for (int i = 0; i < binders.size(); i++) {
            binders.get(i).bind(ps, i + 1);
        }

        return ps;
    }

    public String sql() {
        return sql.toString();
    }
}

@FunctionalInterface
public interface SqlParameterBinder {
    void bind(PreparedStatement ps, int index) throws SQLException;
}

Keep it small. Do not build a half-baked ORM.

9. Sorting Whitelist

public enum CaseSort {
    UPDATED_DESC("c.updated_at desc, c.id desc"),
    UPDATED_ASC("c.updated_at asc, c.id asc"),
    PRIORITY_DESC("c.priority desc, c.updated_at desc, c.id desc"),
    CASE_NUMBER_ASC("c.case_number asc, c.id asc");

    private final String sql;

    CaseSort(String sql) {
        this.sql = sql;
    }

    public String sql() {
        return sql;
    }
}

Never concatenate raw request sort:

" order by " + request.getSort()

Whitelist both column and direction.

10. Pagination Object

public record PageRequest(int limit, int offset) {
    public PageRequest {
        if (limit < 1) {
            throw new IllegalArgumentException("limit must be positive");
        }
        if (offset < 0) {
            throw new IllegalArgumentException("offset must not be negative");
        }
    }

    public PageRequest normalized(int minLimit, int maxLimit) {
        int boundedLimit = Math.min(Math.max(limit, minLimit), maxLimit);
        return new PageRequest(boundedLimit, offset);
    }

    public int limitPlusOne() {
        return limit + 1;
    }
}

Use limit + 1 to know whether next page exists.

List<Row> rows = mapRows(rs);

boolean hasNext = rows.size() > query.page().limit();

if (hasNext) {
    rows = rows.subList(0, query.page().limit());
}

11. Offset Pagination vs Keyset Pagination

Offset pagination:

order by updated_at desc, id desc
limit ?
offset ?

Good for shallow UI.

Bad for deep scan:

• offset grows slower;
• unstable under concurrent changes;
• expensive for large offsets.

Keyset pagination:

where (updated_at < ?)
   or (updated_at = ? and id < ?)
order by updated_at desc, id desc
limit ?

Good for infinite scroll/large browsing.

Query object should expose correct pagination model:

public record CaseCursor(
        Instant updatedAt,
        CaseFileId id
) {}

12. Keyset Query Object

public record CaseFeedQuery(
        TenantId tenantId,
        Optional<CaseStatus> status,
        Optional<CaseCursor> after,
        int limit
) {
    public CaseFeedQuery {
        limit = Math.min(Math.max(limit, 1), 200);
    }
}

SQL:

where c.tenant_id = ?
  and (? is null or c.status = ?)
  and (
      ? is null
      or c.updated_at < ?
      or (c.updated_at = ? and c.id < ?)
  )
order by c.updated_at desc, c.id desc
limit ?

In dynamic SQL, include cursor predicate only when present.

Cursor must match sort order.

13. Query Object Validation

Validate:

• limit bounds;
• date range valid;
• keyword length;
• allowed status transitions if query command-specific;
• tenant present;
• sort allowed;
• cursor belongs to same sort/filter model;
• not too many expensive filters together if needed.

Example:

if (openedFrom.isPresent() && openedTo.isPresent()
        && openedFrom.get().isAfter(openedTo.get())) {
    throw new InvalidQuery("openedFrom must be before openedTo");
}

keyword.ifPresent(k -> {
    if (k.normalized().length() < 3) {
        throw new InvalidQuery("keyword must have at least 3 characters");
    }
});

Data access should reject pathological queries before hitting database.

14. Query Object and Index Awareness

Every optional filter combination can affect query plan.

Ask:

• is tenant predicate always included?
• which filter is most selective?
• does order by match index?
• does keyword require full-text index?
• does date range use index?
• what happens when status absent?
• what happens with high offset?
• are statistics good?
• does join multiply rows?

Query object design should be informed by indexes.

Example indexes:

create index ix_case_dashboard_tenant_status_updated
on case_file(tenant_id, status, updated_at desc, id desc);

create index ix_case_dashboard_tenant_updated
on case_file(tenant_id, updated_at desc, id desc);

15. Query Object and Keyword Search

Bad:

lower(description) like '%keyword%'

on huge table.

Options:

• limit keyword to indexed fields;
• full-text search feature;
• dedicated search service;
• trigram/index if database supports;
• separate search read model;
• require date/status filter;
• cap result size.

Do not let arbitrary keyword search kill OLTP database.

16. Specification Pattern

Specification represents a predicate.

Domain-style:

public interface Specification<T> {
    boolean isSatisfiedBy(T candidate);
}

Persistence-style:

public interface SqlSpecification {
    void apply(SqlParts sql);
}

Be careful not to mix them blindly.

Domain specification can validate in memory. SQL specification generates query predicate. They are related but not identical.

17. Domain Specification Example

public final class CaseCanBeAssignedSpec implements Specification<CaseFile> {
    @Override
    public boolean isSatisfiedBy(CaseFile caseFile) {
        return caseFile.status() == CaseStatus.OPEN
                || caseFile.status() == CaseStatus.UNDER_REVIEW;
    }
}

This is domain rule.

It should not know SQL.

18. SQL Specification Example

public interface CaseSqlSpecification {
    void appendTo(SqlParts parts);
}

public final class StatusEqualsSpec implements CaseSqlSpecification {
    private final CaseStatus status;

    public StatusEqualsSpec(CaseStatus status) {
        this.status = status;
    }

    @Override
    public void appendTo(SqlParts parts) {
        parts.append(" and c.status = ?");
        parts.bind((ps, index) -> ps.setString(index, status.dbCode()));
    }
}

Composable:

List<CaseSqlSpecification> specs = List.of(
        new StatusEqualsSpec(CaseStatus.OPEN),
        new AssignedOfficerSpec(officerId)
);

for (CaseSqlSpecification spec : specs) {
    spec.appendTo(parts);
}

19. Specification Composition

public final class AndSpec implements CaseSqlSpecification {
    private final List<CaseSqlSpecification> specs;

    public AndSpec(List<CaseSqlSpecification> specs) {
        this.specs = List.copyOf(specs);
    }

    @Override
    public void appendTo(SqlParts parts) {
        for (CaseSqlSpecification spec : specs) {
            spec.appendTo(parts);
        }
    }
}

For simple query filters, Query Object is often enough. Specification helps when predicates are reused across queries.

Do not add Specification pattern if it only adds ceremony.

20. Specification Reuse Example

Common predicate:

visible to officer

SQL spec:

public final class VisibleToOfficerSpec implements CaseSqlSpecification {
    private final OfficerId officerId;
    private final Set<UnitId> unitIds;

    @Override
    public void appendTo(SqlParts parts) {
        parts.append("""
            and (
                c.assigned_officer_id = ?
                or c.assigned_unit_id in (
            """);

        // bind officer and whitelisted/bound unit IDs
    }
}

But complex authorization predicates deserve careful review and tests.

Sometimes it is better as named DAO method than generic spec.

21. Avoid Specification Over-Abstraction

Bad:

Specification.where("status", "=", request.get("status"))
    .and("priority", ">", request.get("priority"))
    .orderBy(request.get("sort"));

This is raw query language.

Risks:

• SQL injection;
• unindexed queries;
• data leak;
• bypass authorization;
• impossible plan review;
• API coupling to schema.

Keep specification types explicit and finite.

22. Criteria API

JPA Criteria or similar APIs can build dynamic queries type-safely.

Pros:

• avoids string concatenation;
• can compose predicates;
• IDE refactoring with metamodel if configured;
• integrates with JPA.

Cons:

• verbose;
• generated SQL less obvious;
• join/fetch behavior can surprise;
• performance review still required;
• complex criteria becomes unreadable.

Use criteria for moderate dynamic queries, but still name and test query behavior.

23. Spring Data Specification Caveat

Spring Data JPA Specification can be useful:

Specification<CaseFileEntity> spec =
        hasTenant(tenantId)
        .and(hasStatus(status))
        .and(assignedTo(officerId));

But beware:

• repository can become arbitrary query engine;
• fetch joins with pagination tricky;
• count query performance;
• N+1 if fetch plan unclear;
• entity returned when projection would be better;
• business authorization hidden in spec composition.

Use with discipline.

24. Query Object With jOOQ

jOOQ dynamic query:

List<Condition> conditions = new ArrayList<>();
conditions.add(CASE_FILE.TENANT_ID.eq(query.tenantId().value()));

query.status().ifPresent(status ->
        conditions.add(CASE_FILE.STATUS.eq(status.dbCode()))
);

SelectConditionStep<?> select = dsl
        .select(
                CASE_FILE.ID,
                CASE_FILE.CASE_NUMBER,
                CASE_FILE.STATUS,
                CASE_FILE.UPDATED_AT
        )
        .from(CASE_FILE)
        .where(conditions);

Benefits:

• type-safe columns;
• composable conditions;
• SQL visibility;
• mapping support.

Still use whitelisted sort and bounded pagination.

25. Query Object With MyBatis

MyBatis XML dynamic SQL:

<select id="search" resultMap="CaseDashboardRowMap">
  select
    c.id,
    c.case_number,
    c.status,
    c.updated_at
  from case_file c
  where c.tenant_id = #{tenantId}
  <if test="status != null">
    and c.status = #{status}
  </if>
  <if test="assignedOfficerId != null">
    and c.assigned_officer_id = #{assignedOfficerId}
  </if>
  order by ${sortSql}
  limit #{limit}
</select>

Caution:

• ${sortSql} is raw substitution. Only pass server-side whitelisted enum value.
• #{} binds parameters safely.

26. Query Object With JDBC

JDBC requires manual builder but gives full SQL control.

Good for:

• critical performance queries;
• report/export;
• complex vendor SQL;
• explicit mapping;
• migration-sensitive code.

Cost:

• boilerplate;
• dynamic SQL helper needed;
• more tests.

27. Query Object and Count Query

Pagination often needs count.

select count(*)
from ...
where ...

Count can be expensive.

Alternatives:

• Slice with limit + 1;
• approximate count;
• delayed count;
• count only for first page;
• read model with precomputed counts.

Do not automatically run count for every search if expensive.

Return type:

public record Slice<T>(
        List<T> items,
        boolean hasNext,
        Optional<Long> totalCount
) {}

28. Query Object and Join Multiplication

Filtering by child table can duplicate parent rows.

Example:

select c.*
from case_file c
join case_tag t on t.case_id = c.id
where t.tag in (?, ?)

If case has multiple matching tags, duplicates.

Fix:

• exists predicate;
• distinct carefully;
• group by;
• two-step query;
• read model.

Prefer exists for parent filtering:

and exists (
    select 1
    from case_tag t
    where t.case_id = c.id
      and t.tag = ?
)

29. Query Object and Projection

Query should return projection, not aggregate, for read/search.

public record CaseDashboardRow(
        CaseFileId id,
        String caseNumber,
        CaseStatus status,
        Priority priority,
        Instant updatedAt,
        Optional<String> officerName
) {}

This avoids:

• loading domain aggregate for display;
• lazy loading;
• over-fetching;
• accidental mutation.

30. Query Object and Security

Query object must include scope.

public record CaseDashboardQuery(
        TenantId tenantId,
        UserScope scope,
        Optional<CaseStatus> status,
        ...
) {}

SQL:

where c.tenant_id = ?
  and (
      c.assigned_officer_id = ?
      or c.assigned_unit_id in (...)
  )

Do not apply authorization only after fetching rows.

31. Query Object and Multi-Tenancy

Always include tenant predicate.

Bad:

CaseSearchQuery(Optional<TenantId> tenantId, ...)

for tenant-scoped application.

Better:

TenantId tenantId

non-optional.

Admin cross-tenant query should be separate query object with explicit elevated permission.

AdminCaseSearchQuery

This avoids accidental cross-tenant data leak.

32. Query Object and Null Semantics

Define filter semantics.

status absent -> all statuses
status present -> exact status
assignedOfficer absent -> any officer
assignedOfficer present empty? not allowed
unassigned only? separate flag

Example:

public sealed interface OfficerFilter {
    record AnyOfficer() implements OfficerFilter {}
    record AssignedTo(OfficerId officerId) implements OfficerFilter {}
    record Unassigned() implements OfficerFilter {}
}

This is clearer than Optional<OfficerId> plus boolean flags.

33. Query Object With Sealed Filters

public sealed interface AssignmentFilter
        permits AssignmentFilter.Any, AssignmentFilter.Unassigned, AssignmentFilter.AssignedTo {

    record Any() implements AssignmentFilter {}
    record Unassigned() implements AssignmentFilter {}
    record AssignedTo(OfficerId officerId) implements AssignmentFilter {}
}

SQL builder:

switch (query.assignmentFilter()) {
    case AssignmentFilter.Any ignored -> {}
    case AssignmentFilter.Unassigned ignored ->
            parts.append(" and c.assigned_officer_id is null");
    case AssignmentFilter.AssignedTo assigned -> {
        parts.append(" and c.assigned_officer_id = ?");
        parts.bind((ps, index) -> ps.setObject(index, assigned.officerId().value()));
    }
}

This avoids ambiguous null behavior.

34. Query Object and Date Range

Use half-open intervals.

openedFrom inclusive
openedTo exclusive

SQL:

and c.opened_at >= ?
and c.opened_at < ?

This avoids end-of-day/timezone bugs.

Represent:

public record TimeRange(
        Instant fromInclusive,
        Instant toExclusive
) {
    public TimeRange {
        if (!fromInclusive.isBefore(toExclusive)) {
            throw new InvalidQuery("from must be before to");
        }
    }
}

35. Query Object and Timezone

Do not let DAO interpret user-local dates.

Controller/application converts:

LocalDate + user timezone -> Instant range

Then query object carries Instant range.

This keeps DAO timezone-safe.

36. Query Object and Enum Mapping

Query object should use domain enum:

CaseStatus.UNDER_REVIEW

DAO maps to DB code:

ps.setString(index, status.dbCode());

Avoid passing raw "underReview" string from request into DAO.

37. Query Object and Large IN Lists

where id in (...) with thousands of IDs can be problematic.

Options:

• cap list size;
• use temporary/staging table;
• batch chunks;
• join to table-valued parameter where supported;
• use array parameter if supported;
• redesign query.

Query object should validate max IDs.

if (ids.size() > 500) {
    throw new InvalidQuery("Too many ids");
}

38. Query Object and Query Plan Stability

Optional filters can create many query shapes.

For high-throughput path, consider:

• separate query methods for common shapes;
• database prepared statement behavior;
• indexes per shape;
• avoiding OR-heavy predicates;
• using dynamic SQL that omits absent filters rather than (? is null or col = ?) if plan suffers;
• measuring.

Dynamic SQL is not automatically bad. It can create better plans than one generic OR-heavy query.

39. OR-Heavy Query Caveat

Generic SQL:

where tenant_id = ?
  and (? is null or status = ?)
  and (? is null or officer_id = ?)
  and (? is null or priority = ?)

This is convenient, but can make optimizer choose poor plan depending database.

Dynamic SQL that includes only active filters may be better:

where tenant_id = ?
  and status = ?
  and officer_id = ?

Measure on target DB.

40. Query Object and Query Ownership

Who owns query object?

• application/query package owns semantic query object;
• DAO/query service owns SQL implementation;
• controller maps request to query object.

Avoid putting SQL fragments inside request object.

Avoid putting HTTP parsing inside DAO.

41. Query Object and API Evolution

Adding filter:

1. add request field;
2. validate/map to query object;
3. add query object field;
4. update SQL builder;
5. add index if needed;
6. add tests;
7. update API docs;
8. monitor query plan.

A filter is a production feature, not a trivial parameter.

42. Query Object Testing

Test:

• no optional filters;
• each filter alone;
• important filter combinations;
• sorting;
• pagination boundary;
• keyset cursor;
• tenant isolation;
• authorization scope;
• date range;
• invalid query rejection;
• SQL injection attempts via keyword/sort;
• result mapping;
• count/slice behavior.

Use real DB for integration.

43. Query Object Test Example

@Test
void searchFiltersByStatusAndOfficerWithinTenant() {
    fixture.caseFile(tenantA, case1, OPEN, officerA);
    fixture.caseFile(tenantA, case2, CLOSED, officerA);
    fixture.caseFile(tenantA, case3, OPEN, officerB);
    fixture.caseFile(tenantB, case4, OPEN, officerA);

    CaseDashboardQuery query = new CaseDashboardQuery(
            tenantA,
            Optional.of(OPEN),
            Optional.of(officerA),
            Optional.empty(),
            Optional.empty(),
            Optional.empty(),
            Optional.empty(),
            CaseSort.UPDATED_DESC,
            new PageRequest(20, 0)
    );

    Slice<CaseDashboardRow> result = dao.search(connection, query);

    assertThat(result.items())
            .extracting(CaseDashboardRow::id)
            .containsExactly(case1);
}

44. Sort Injection Test

@Test
void rejectsUnknownSort() {
    assertThatThrownBy(() -> requestMapper.parseSort("updated_at desc; drop table case_file"))
            .isInstanceOf(InvalidQuery.class);
}

Sort should become enum before DAO.

45. Query Object and Performance Smoke Test

For critical search:

• insert realistic data volume sample;
• run common filters;
• assert query completes under threshold in test environment;
• inspect explain plan manually/CI if available;
• track slow query in staging.

Do not overfit test threshold, but performance smoke catches obvious missing index.

46. Query Object and Observability

Metrics:

query.duration{query="CaseDashboardDao.search"}
query.rows{returned}
query.filter.status.present
query.page.limit
query.error.count

Avoid high-cardinality labels like keyword, user ID, case ID.

For debugging, logs can include safe query object summary:

{
  "query": "CaseDashboardDao.search",
  "tenantScoped": true,
  "statusPresent": true,
  "officerPresent": false,
  "sort": "UPDATED_DESC",
  "limit": 50
}

47. Query Object and Caching

Caching query results is hard because filters create many keys.

If caching:

• only cache stable/common queries;
• include tenant/scope in cache key;
• avoid caching sensitive user-specific results broadly;
• set short TTL;
• invalidate carefully;
• consider read model instead.

Do not add cache to hide bad query plan.

48. Query Object and Read Model

If query becomes too complex for OLTP schema:

• create read model table;
• denormalize dashboard fields;
• update synchronously or via outbox/event;
• query read model with simpler filters;
• index read model for UI patterns.

This is often better than monstrous join query.

49. Query Object and Export

Export query object differs from dashboard query.

Dashboard:

small page, interactive latency

Export:

large result, async, cursor, evidence, snapshot, row count

Use separate query object:

CaseExportQuery

Do not reuse dashboard query blindly for export.

50. Query Object and Specification Review Checklist

• Query object is use-case specific.
• Tenant/scope is required.
• Request DTO is converted before DAO.
• Sort is whitelisted.
• Limit is bounded.
• Date range semantics are clear.
• Null/absent filter semantics are explicit.
• Dynamic SQL uses bind parameters.
• Authorization is applied in SQL.
• Index support reviewed.
• Count query is not blindly expensive.
• Keyset used for deep scan.
• Query returns projection, not aggregate, for read path.
• Tests cover filter combinations and tenant isolation.
• Observability uses query name.

51. Anti-Pattern: Map<String,Object> Query

search(Map<String, Object> filters)

This hides type, validation, semantics, and index expectations.

Use typed query object.

52. Anti-Pattern: Raw Sort From Request

SQL injection and plan chaos.

Use enum whitelist.

53. Anti-Pattern: One Universal Search Endpoint Backed by OLTP Tables

If users can filter/sort by anything, database becomes ad hoc analytics engine.

Use dedicated search/read model/reporting store.

54. Anti-Pattern: Query Object Returning Aggregate for Dashboard

Dashboard needs projection. Aggregate repository is wrong tool.

55. Anti-Pattern: Optional Tenant Filter

Tenant filter should not be optional for tenant-scoped query.

56. Anti-Pattern: Count Everything Always

select count(*) over complex join for every page can be more expensive than data query.

Use slice or approximate/async count.

57. Mini Lab

Design query object for:

Case dashboard search

Requirements:

• tenant scoped;
• current user can see own assigned cases and cases in their units;
• optional status;
• optional priority;
• optional assigned officer;
• optional opened date range;
• keyword on case number only;
• sort by updated desc, priority desc, case number asc;
• page size max 100;
• no cross-tenant access;
• must support keyset pagination later.

Questions:

1. What fields are in request DTO?
2. What fields are in query object?
3. How is authorization scope represented?
4. What sort enum values exist?
5. What index supports common query?
6. What filter combinations need tests?
7. What should not be supported?
8. Would dashboard query return aggregate or DTO?
9. When would you move to read model?
10. What metrics/logs are emitted?

58. Summary

Query Object and Specification Pattern help manage flexible query without chaos.

You must master:

• typed query object;
• request DTO separation;
• safe dynamic SQL;
• sort whitelist;
• bounded pagination;
• keyset cursor;
• explicit null semantics;
• tenant/authorization scope;
• filter validation;
• index-aware query design;
• count query cost;
• projection return type;
• domain specification vs SQL specification;
• avoiding universal query engines;
• testing filter combinations;
• observability and query naming.

Part berikutnya membahas Command Query Separation in Data Access: memisahkan write repository dan read/query service secara pragmatis tanpa overengineering CQRS.

59. References

• Oracle Java SE JDBC PreparedStatement: https://docs.oracle.com/en/java/javase/21/docs/api/java.sql/java/sql/PreparedStatement.html
• Oracle Java SE JDBC ResultSet: https://docs.oracle.com/en/java/javase/21/docs/api/java.sql/java/sql/ResultSet.html
• Jakarta Persistence Criteria API: https://jakarta.ee/specifications/persistence/3.2/jakarta-persistence-spec-3.2
• Spring Data JPA Specifications: https://docs.spring.io/spring-data/jpa/reference/jpa/specifications.html
• jOOQ Dynamic SQL Manual: https://www.jooq.org/doc/latest/manual/sql-building/dynamic-sql/
• MyBatis Dynamic SQL: https://mybatis.org/mybatis-dynamic-sql/docs/introduction.html