MyBatis Dynamic SQL

1. Core Thesis

Dynamic SQL adalah salah satu fitur paling kuat di MyBatis. Ia memungkinkan query berubah berdasarkan parameter runtime: filter opsional, sorting, pagination, conditional update, bulk lookup, dan search screen yang kompleks.

Tetapi dynamic SQL juga salah satu sumber risiko terbesar:

• SQL injection akibat ${} atau dynamic ORDER BY yang tidak di-whitelist,
• query yang sulit dipahami karena banyak branch XML,
• predicate wajib seperti tenant/soft delete/effective date hilang di salah satu branch,
• query plan PostgreSQL tidak stabil,
• dynamic filter membuat index sulit dipakai,
• parameter null menghasilkan SQL tak terduga,
• IN clause terlalu besar,
• count query tidak konsisten dengan data query,
• update statement mengubah kolom yang tidak dimaksud,
• PR review sulit karena SQL final tidak terlihat langsung.

Prinsip utama:

Dynamic SQL harus tetap menghasilkan query yang predictable, safe, testable, dan explainable. Fleksibilitas tidak boleh mengalahkan correctness dan observability.

Dynamic SQL yang baik bukan XML pintar. Dynamic SQL yang baik adalah query contract yang jelas untuk variasi input yang terbatas dan disengaja.

2. Mental Model: SQL Template + Runtime Branches

MyBatis dynamic SQL dapat dipahami sebagai:

static SQL template
+ runtime parameters
+ conditional fragments
= final SQL sent to JDBC/PostgreSQL

Yang direview engineer bukan hanya XML-nya, tetapi semua SQL final yang mungkin dihasilkan.

Contoh sederhana:

<select id="searchQuotes" resultMap="QuoteSearchRowResultMap">
  SELECT
    q.id,
    q.quote_number,
    q.status,
    q.created_at
  FROM quote q
  <where>
    <if test="status != null">
      q.status = #{status}
    </if>
    <if test="customerId != null">
      AND q.customer_id = #{customerId}
    </if>
  </where>
</select>

Kemungkinan SQL final:

1. Tanpa filter.
2. Filter status saja.
3. Filter customer saja.
4. Filter status dan customer.

Setiap variasi harus dipikirkan:

• apakah aman?
• apakah index masih relevan?
• apakah query tanpa filter boleh?
• apakah tenant filter selalu ada?
• apakah result size dibatasi?

3. #{} vs ${}: The Most Important Distinction

3.1 #{} parameter binding

#{} memakai prepared statement parameter binding.

WHERE q.status = #{status}

Final JDBC behavior kira-kira:

WHERE q.status = ?

Value dikirim sebagai parameter, bukan digabung ke string SQL.

Gunakan #{} untuk:

• value filter,
• ID,
• status,
• date range,
• number,
• text search value,
• limit/offset jika driver/dialect mendukung,
• update value,
• insert value.

3.2 ${} string substitution

${} melakukan string substitution mentah.

ORDER BY ${sortColumn}

Jika input tidak dikontrol, ini raw SQL injection vector.

Jangan gunakan ${} untuk user input langsung.

${} hanya boleh dipakai jika value berasal dari whitelist internal yang sangat ketat, misalnya hasil mapping enum server-side:

public enum QuoteSortField {
    CREATED_AT("q.created_at"),
    QUOTE_NUMBER("q.quote_number"),
    STATUS("q.status");

    private final String sqlExpression;
}

Lalu mapper hanya menerima SQL expression yang sudah dipilih dari enum, bukan raw request value.

Rule:

User input tidak boleh masuk ${}. Pernah.

4. <if> for Optional Predicate

<if> digunakan untuk conditional fragment.

<if test="status != null">
  AND q.status = #{status}
</if>

Risiko:

• null parameter menghilangkan filter penting,
• empty string dianggap valid atau tidak konsisten,
• list kosong menghasilkan SQL invalid,
• branch tidak ditest,
• predicate mandatory tidak sengaja optional.

Guideline:

• Bedakan optional business filter dari mandatory safety filter.
• Tenant/security/soft delete filter biasanya tidak boleh optional.
• Validasi parameter sebelum mapper jika branch terlalu banyak.
• Test kombinasi filter penting.

Anti-pattern:

<if test="tenantId != null">
  AND q.tenant_id = #{tenantId}
</if>

Jika tenant isolation wajib, tenant predicate tidak boleh hilang hanya karena parameter null.

Better:

AND q.tenant_id = #{tenantId}

Lalu service harus memastikan tenantId selalu tersedia.

5. <where> for Predicate Cleanup

<where> otomatis menambahkan WHERE jika ada condition dan menghapus leading AND/OR.

<where>
  <if test="status != null">
    AND q.status = #{status}
  </if>
  <if test="createdAfter != null">
    AND q.created_at &gt;= #{createdAfter}
  </if>
</where>

Keuntungan:

• menghindari WHERE AND,
• query lebih mudah dibaca,
• optional predicates lebih aman secara syntax.

Tetapi <where> tidak menyelesaikan masalah semantic.

Query tanpa filter bisa terjadi:

SELECT ... FROM quote q

Untuk table besar, ini bisa menjadi production problem.

Checklist:

• Apakah query tanpa filter diperbolehkan?
• Apakah limit wajib ada?
• Apakah tenant filter selalu ada?
• Apakah soft delete filter selalu ada?
• Apakah date range wajib untuk report besar?

6. <choose>, <when>, <otherwise> for Exclusive Branches

Gunakan <choose> ketika hanya satu branch boleh aktif.

<choose>
  <when test="quoteNumber != null">
    q.quote_number = #{quoteNumber}
  </when>
  <when test="externalReference != null">
    q.external_reference = #{externalReference}
  </when>
  <otherwise>
    q.created_at &gt;= #{defaultCreatedAfter}
  </otherwise>
</choose>

Cocok untuk:

• search mode exclusive,
• fallback predicate,
• different strategy based on command type,
• query route yang tidak boleh digabung.

Risiko:

• precedence branch tidak jelas,
• parameter ganda hanya memakai branch pertama,
• otherwise terlalu luas,
• logic business pindah ke XML.

Guideline:

• Jika branch merepresentasikan business decision, pilih mode di Java dulu.
• Mapper menerima parameter yang sudah jelas.
• XML jangan menjadi decision engine.

7. <trim> for Custom Prefix/Suffix Control

<trim> lebih fleksibel dari <where> dan <set>.

Contoh custom WHERE:

<trim prefix="WHERE" prefixOverrides="AND |OR ">
  <if test="status != null">
    AND q.status = #{status}
  </if>
  <if test="customerId != null">
    AND q.customer_id = #{customerId}
  </if>
</trim>

Contoh dynamic tuple fragment:

<trim prefix="(" suffix=")" suffixOverrides=",">
  <if test="quoteNumber != null">quote_number,</if>
  <if test="status != null">status,</if>
</trim>

Gunakan <trim> saat <where>/<set> tidak cukup, tetapi jangan membuat XML menjadi terlalu sulit dibaca.

8. <set> for Dynamic Update

<set> membantu membuat dynamic update.

<update id="updateQuotePatch">
  UPDATE quote
  <set>
    <if test="status != null">
      status = #{status},
    </if>
    <if test="description != null">
      description = #{description},
    </if>
    updated_at = now()
  </set>
  WHERE id = #{id}
</update>

Risiko dynamic update:

• null tidak bisa dibedakan antara “tidak update” dan “set null”,
• update tanpa changed field tetap mengubah updated_at,
• optimistic lock/version lupa,
• tenant predicate lupa,
• audit field tidak konsisten,
• partial update melanggar invariant.

Untuk enterprise command path, patch object harus jelas:

public final class UpdateQuotePatchCommand {
    private UUID quoteId;
    private OptionalField<String> description;
    private OptionalField<QuoteStatus> status;
    private long expectedVersion;
}

Dengan model seperti ini, mapper bisa membedakan:

• absent: jangan update field,
• present null: set column ke null jika allowed,
• present value: set value baru.

Jika command object hanya memakai nullable fields, semantics sering ambigu.

9. <foreach> for IN Clause and Batch-Like SQL

<foreach> sering dipakai untuk IN clause.

<select id="findQuotesByIds" resultMap="QuoteRowResultMap">
  SELECT
    q.id,
    q.quote_number,
    q.status
  FROM quote q
  WHERE q.id IN
  <foreach collection="quoteIds" item="quoteId" open="(" separator="," close=")">
    #{quoteId}
  </foreach>
</select>

Risiko:

• list kosong menghasilkan SQL invalid atau query tidak sesuai,
• list terlalu besar memperburuk plan/performance,
• parameter count limit/driver overhead,
• order result tidak sama dengan order input,
• query plan berbeda tergantung cardinality.

Guideline:

• Validate list tidak kosong sebelum mapper.
• Batasi ukuran list.
• Untuk list sangat besar, pertimbangkan temporary table, staging table, bulk load, atau join ke unnest.
• Jangan mengandalkan order IN; gunakan explicit ORDER BY.

PostgreSQL alternative:

WHERE q.id = ANY(#{quoteIdsAsArray})

Namun ini memerlukan mapping array yang benar dan tetap harus diuji.

10. <bind> for Derived Parameters

<bind> membuat variable baru dari expression.

Contoh LIKE pattern:

<bind name="quoteNumberPattern" value="'%' + quoteNumber + '%'" />
AND q.quote_number ILIKE #{quoteNumberPattern}

Gunakan untuk:

• pattern LIKE,
• normalized search value,
• derived value sederhana.

Hati-hati:

• jangan bangun SQL fragment dengan bind,
• jangan jadikan bind tempat business logic kompleks,
• escape wildcard jika user input tidak boleh dianggap pattern.

Jika user mencari literal %, _, atau backslash, LIKE pattern harus di-escape dengan benar.

11. Dynamic Filtering

Dynamic filtering biasa muncul di search/list endpoint.

Contoh filter object:

public record QuoteSearchFilter(
    UUID tenantId,
    QuoteStatus status,
    UUID customerId,
    Instant createdFrom,
    Instant createdTo,
    String quoteNumberContains,
    Boolean includeDeleted
) {}

Mapper:

<select id="searchQuotes" resultMap="QuoteSearchRowResultMap">
  SELECT
    q.id,
    q.quote_number,
    q.status,
    q.created_at
  FROM quote q
  <where>
    q.tenant_id = #{tenantId}

    <if test="includeDeleted == null or includeDeleted == false">
      AND q.deleted_at IS NULL
    </if>

    <if test="status != null">
      AND q.status = #{status}
    </if>

    <if test="customerId != null">
      AND q.customer_id = #{customerId}
    </if>

    <if test="createdFrom != null">
      AND q.created_at &gt;= #{createdFrom}
    </if>

    <if test="createdTo != null">
      AND q.created_at &lt; #{createdTo}
    </if>

    <if test="quoteNumberContains != null and quoteNumberContains != ''">
      AND q.quote_number ILIKE #{quoteNumberPattern}
    </if>
  </where>
</select>

Observations:

• tenant filter mandatory,
• soft delete default is excluded,
• filters are explicit,
• empty string handled,
• date range uses half-open interval.

Checklist dynamic filtering:

• Is there a mandatory tenant/security predicate?
• Is soft delete/effective date handled consistently?
• Is unbounded query allowed?
• Are empty strings normalized?
• Are date ranges inclusive/exclusive by design?
• Are indexes aligned with common filter combinations?
• Is count query consistent?

12. Dynamic Sorting

Sorting adalah area injection risk tinggi.

Anti-pattern:

ORDER BY ${sortBy} ${sortDirection}

Jika sortBy berasal dari request, ini dangerous.

Better approach:

1. Request menerima sort key publik.
2. Java memvalidasi sort key terhadap whitelist.
3. Java mengubah sort key menjadi enum internal.
4. Mapper hanya menerima SQL expression dari enum yang dikontrol server.

Example:

public enum QuoteSort {
    CREATED_AT("q.created_at"),
    QUOTE_NUMBER("q.quote_number"),
    STATUS("q.status");

    private final String sql;
}

Mapper:

ORDER BY ${sort.sql} ${sort.directionSql}

Ini masih memakai ${}, tetapi input sudah bukan raw user input. Tetap harus ketat:

• sort.sql hanya dari enum,
• directionSql hanya ASC atau DESC,
• default sorting selalu ada,
• tie-breaker ditambahkan untuk stable ordering.

Stable ordering:

ORDER BY q.created_at DESC, q.id DESC

Tanpa tie-breaker, pagination bisa tidak stabil saat banyak row punya timestamp sama.

13. Dynamic Pagination

Pagination biasanya memakai LIMIT dan OFFSET, atau keyset/cursor.

Offset pagination:

LIMIT #{limit}
OFFSET #{offset}

Risiko:

• large offset lambat,
• result tidak stabil tanpa deterministic order,
• count query mahal,
• data berubah antar page.

Keyset pagination:

<if test="cursorCreatedAt != null and cursorId != null">
  AND (
    q.created_at, q.id
  ) &lt; (
    #{cursorCreatedAt}, #{cursorId}
  )
</if>
ORDER BY q.created_at DESC, q.id DESC
LIMIT #{limit}

Keyset lebih cocok untuk:

• data besar,
• infinite scroll,
• event/history list,
• stable forward navigation.

Offset masih acceptable untuk:

• admin list kecil,
• bounded result,
• simple UX,
• reporting dengan explicit limit.

Checklist pagination:

• Apakah ORDER BY deterministic?
• Apakah limit punya maximum?
• Apakah offset besar dicegah?
• Apakah count query diperlukan?
• Apakah count query memakai filter sama?
• Apakah keyset lebih tepat?

14. Dynamic Count Query

Search endpoint sering punya data query dan count query.

Risk:

• filter data query dan count query berbeda,
• join di count menyebabkan duplicate count,
• count terlalu mahal,
• count tidak memakai index,
• soft delete/tenant filter lupa.

Pattern:

<sql id="QuoteSearchWhereClause">
  <where>
    q.tenant_id = #{tenantId}
    <if test="status != null">
      AND q.status = #{status}
    </if>
    <if test="customerId != null">
      AND q.customer_id = #{customerId}
    </if>
  </where>
</sql>

<select id="searchQuotes" resultMap="QuoteSearchRowResultMap">
  SELECT q.id, q.quote_number, q.status
  FROM quote q
  <include refid="QuoteSearchWhereClause" />
  ORDER BY q.created_at DESC, q.id DESC
  LIMIT #{limit}
  OFFSET #{offset}
</select>

<select id="countQuotes" resultType="long">
  SELECT count(*)
  FROM quote q
  <include refid="QuoteSearchWhereClause" />
</select>

<sql> fragment reuse bisa membantu konsistensi, tetapi jangan membuat fragment terlalu generic.

15. Dynamic SQL and PostgreSQL Query Planner

Dynamic SQL menghasilkan banyak query shapes. PostgreSQL planner bisa memilih plan berbeda untuk predicate berbeda.

Concern:

• optional predicate mengubah selectivity,
• OR condition bisa mengurangi index usage,
• ILIKE %term% butuh strategy khusus,
• JSONB predicate butuh index khusus,
• date range + tenant + status perlu composite index yang sesuai,
• generic plan vs custom plan dapat berpengaruh pada prepared statement behavior.

Contoh query yang tampak fleksibel tetapi berat:

WHERE
  (#{status} IS NULL OR q.status = #{status})
  AND (#{customerId} IS NULL OR q.customer_id = #{customerId})

Ini sering lebih buruk daripada dynamic predicate yang hanya menambahkan condition saat parameter ada, karena OR dengan parameter null bisa mengganggu planner.

Prefer:

<if test="status != null">
  AND q.status = #{status}
</if>
<if test="customerId != null">
  AND q.customer_id = #{customerId}
</if>

Tetap harus diuji dengan EXPLAIN untuk filter kombinasi umum.

16. Dynamic SQL and Transaction Boundary

Dynamic SQL memengaruhi transaksi karena query final menentukan:

• row mana yang dibaca,
• row mana yang dikunci,
• berapa lama query berjalan,
• apakah update menyentuh row terlalu banyak,
• apakah lock scope terlalu luas,
• apakah retry diperlukan.

Dangerous update:

<update id="updateQuoteStatus">
  UPDATE quote
  <set>
    status = #{status},
    updated_at = now()
  </set>
  <where>
    <if test="quoteId != null">
      id = #{quoteId}
    </if>
    <if test="tenantId != null">
      AND tenant_id = #{tenantId}
    </if>
  </where>
</update>

Jika quoteId null dan tenantId null, <where> bisa hilang dan update semua row.

Untuk write query, mandatory predicate harus bukan optional.

Better:

WHERE id = #{quoteId}
  AND tenant_id = #{tenantId}
  AND version = #{expectedVersion}

Command write path harus fail fast jika parameter wajib tidak ada.

17. Safe Dynamic Update with Optimistic Locking

Contoh update status aman:

<update id="transitionQuoteStatus">
  UPDATE quote
  SET
    status = #{newStatus},
    version = version + 1,
    updated_at = now(),
    updated_by = #{updatedBy}
  WHERE id = #{quoteId}
    AND tenant_id = #{tenantId}
    AND status = #{expectedCurrentStatus}
    AND version = #{expectedVersion}
</update>

Service harus memeriksa affected row count:

int updated = mapper.transitionQuoteStatus(command);
if (updated == 0) {
    throw new ConcurrentModificationOrInvalidStateException(...);
}

Dynamic SQL tidak boleh menghilangkan predicate correctness:

• tenant,
• identity,
• expected version,
• expected current state,
• soft delete condition jika relevant.

18. Dynamic SQL for Bulk Operations

Bulk update/delete dengan dynamic SQL harus sangat ketat.

Example:

<update id="markQuotesExpired">
  UPDATE quote
  SET
    status = 'EXPIRED',
    updated_at = now()
  WHERE tenant_id = #{tenantId}
    AND status = 'DRAFT'
    AND expires_at &lt; #{now}
</update>

Jangan terlalu dynamic untuk bulk mutation. Bulk mutation harus punya:

• explicit business condition,
• bounded scope,
• expected affected row monitoring,
• transaction size awareness,
• lock impact assessment,
• retry/idempotency plan.

Jika bulk mutation dibuat dari banyak optional filter, reviewer harus curiga.

19. Dynamic SQL for JSONB Query

PostgreSQL JSONB sering dipakai dengan dynamic predicates.

Example:

<if test="productCategory != null">
  AND q.payload -&gt;&gt; 'productCategory' = #{productCategory}
</if>

Concern:

• JSON path typo tidak ditangkap compile-time,
• index harus sesuai expression,
• value type text vs numeric bisa salah,
• query semantics tersembunyi di JSON,
• PII di JSONB harus dilindungi.

Jika JSONB query sering dipakai, pertimbangkan:

• generated column,
• expression index,
• normalized column,
• dedicated read model,
• documented JSON schema.

Dynamic JSONB filter harus punya test untuk field ada, field tidak ada, null, dan value type mismatch.

20. Reusable SQL Fragments with <sql> and <include>

<sql> fragment membantu reuse.

<sql id="BaseQuoteColumns">
  q.id,
  q.quote_number,
  q.status,
  q.created_at,
  q.updated_at
</sql>

<select id="findQuoteById" resultMap="QuoteRowResultMap">
  SELECT
    <include refid="BaseQuoteColumns" />
  FROM quote q
  WHERE q.id = #{quoteId}
</select>

Keuntungan:

• mengurangi duplikasi,
• menjaga column list konsisten,
• membantu count/data query share filter.

Risiko:

• fragment terlalu generic,
• perubahan fragment berdampak ke banyak query,
• fragment menyembunyikan SQL final,
• alias context tidak cocok,
• filter mandatory tidak terlihat di query utama.

Guideline:

• Fragment harus kecil dan jelas.
• Jangan membuat “universal where clause”.
• Hindari fragment lintas domain tanpa ownership.
• Review semua usage saat fragment berubah.

21. XML Readability Discipline

Dynamic SQL bisa menjadi tidak terbaca.

Smell:

• nested <if> terlalu dalam,
• satu <select> lebih dari 100 baris,
• banyak <choose> di query yang sama,
• business rule state transition ada di XML,
• fragment include saling include,
• sulit membayangkan SQL final.

Refactoring options:

1. Pecah query menjadi beberapa mapper method eksplisit.
2. Pilih search strategy di Java, bukan XML.
3. Gunakan query object yang lebih strict.
4. Gunakan SQL view/materialized view/read model jika query terlalu kompleks.
5. Gunakan MyBatis Dynamic SQL library jika team convention mendukung.
6. Pindahkan assembling kompleks ke service/read model assembler.

Heuristic:

Jika reviewer tidak bisa menjelaskan tiga SQL final paling umum dalam beberapa menit, dynamic SQL terlalu kompleks.

22. Dynamic SQL in Java/JAX-RS Request Lifecycle

Dalam JAX-RS backend, dynamic SQL biasanya menerima input dari query parameter atau request body.

Flow aman:

HTTP request
-> JAX-RS resource parses raw input
-> validation and normalization
-> application service builds query object
-> mapper executes dynamic SQL
-> result mapped to DTO/read model
-> response returned

Jangan biarkan mapper menerima raw HTTP parameter tanpa normalization.

Example validation:

• limit punya max,
• offset tidak negatif,
• sortBy masuk whitelist,
• sortDirection hanya ASC/DESC,
• date range valid,
• tenant context mandatory,
• status value valid,
• empty string dinormalisasi ke null jika semantics-nya optional.

Mapper bukan tempat utama validasi API. Mapper boleh defensif, tetapi request semantics harus ditentukan sebelum mapper.

23. Security Failure Modes

23.1 ORDER BY injection

Input:

sortBy=created_at; DROP TABLE quote; --

Jika masuk ${sortBy}, SQL bisa rusak atau berbahaya.

Mitigation:

• whitelist sort field,
• enum mapping,
• no raw ${} from request.

23.2 Dynamic WHERE injection

Anti-pattern:

AND ${filterExpression}

Mitigation:

• represent filter sebagai structured object,
• map operator dari whitelist,
• bind value dengan #{}.

23.3 LIKE wildcard abuse

Input % bisa match semua row.

Mitigation:

• min search length,
• escape wildcard,
• limit result,
• use full-text search if needed.

23.4 Tenant predicate bypass

Tenant filter conditional membuat cross-tenant data exposure.

Mitigation:

• tenant predicate mandatory,
• tenant from security context, not user-controlled raw param,
• integration test cross-tenant.

24. Performance Failure Modes

24.1 Unbounded query

No filter + no limit.

Mitigation:

• mandatory limit,
• default date range,
• require at least one selective filter for heavy search.

24.2 Large offset

Offset 1,000,000 causes slow scan.

Mitigation:

• max offset,
• keyset pagination,
• cursor API.

24.3 IN clause too large

Huge list creates parse/planning/execution overhead.

Mitigation:

• cap list size,
• chunking,
• temporary/staging table,
• array binding with tested strategy.

24.4 Optional OR predicates

(:param is null OR column = :param) style can reduce index effectiveness.

Mitigation:

• dynamic predicates,
• separate query paths for common modes,
• EXPLAIN common cases.

24.5 Dynamic sort on unindexed expression

User sort field causes expensive sort.

Mitigation:

• whitelist only supported sort fields,
• align indexes,
• reject unsupported sort for large datasets.

25. Correctness Failure Modes

25.1 Missing mandatory predicate

A conditional <if> removes a critical condition.

Examples:

• tenant condition,
• soft delete condition,
• status transition condition,
• expected version condition,
• effective date condition.

Mitigation:

• mandatory predicates outside <if>,
• command validation,
• affected row assertion,
• tests with unsafe null parameters.

25.2 Count query mismatch

Data query and count query use different filter.

Mitigation:

• shared carefully scoped where fragment,
• paired tests,
• verify count and result against fixture.

25.3 Null semantics ambiguity

Patch request cannot distinguish absent vs explicit null.

Mitigation:

• explicit optional field wrapper,
• separate methods for clear vs update,
• validation before mapper.

25.4 Dynamic update changes too much

Missing WHERE condition updates many rows.

Mitigation:

• never make identity predicates optional,
• assert affected row count,
• safe SQL linting/review,
• transaction rollback in tests.

26. Observability and Debugging

Dynamic SQL debugging requires seeing final SQL shape.

Debugging steps:

1. Capture mapper id.
2. Capture normalized parameters, with PII redacted.
3. Capture final SQL or reconstruct from logs.
4. Run final SQL in PostgreSQL with representative values.
5. EXPLAIN ANALYZE common cases.
6. Compare count query and data query.
7. Verify index usage.
8. Verify query duration and row count.
9. Add regression test for problematic branch.

Logging concerns:

• jangan log PII mentah,
• jangan log full JSONB payload sensitif,
• log query id/mapper id,
• log duration, row count, parameter category, not necessarily raw values,
• include correlation/request id.

27. Testing Dynamic SQL

Dynamic SQL wajib diuji per branch penting.

Test cases:

• no optional filters,
• each filter individually,
• common filter combinations,
• empty string,
• null values,
• empty list,
• large list boundary,
• invalid sort field rejected before mapper,
• valid sort fields produce expected order,
• pagination stable,
• tenant isolation,
• soft delete excluded by default,
• date range boundaries,
• SQL injection attempts rejected,
• count query matches data query.

Mapper tests harus memakai PostgreSQL nyata/Testcontainers jika query memakai PostgreSQL-specific behavior.

Untuk dynamic SQL, test tidak cukup hanya “query berhasil”. Test harus membuktikan:

• row yang tepat muncul,
• row yang tidak boleh muncul tidak muncul,
• sorting benar,
• pagination stabil,
• dangerous parameter tidak menghasilkan SQL dangerous,
• affected row count sesuai expectation.

28. PR Review Checklist

Gunakan checklist ini untuk review MyBatis dynamic SQL:

• Apakah ada penggunaan ${}?
• Jika ada ${}, apakah value berasal dari whitelist internal?
• Apakah semua user input memakai #{}?
• Apakah tenant/security predicate mandatory?
• Apakah soft delete/effective date filter konsisten?
• Apakah query tanpa filter diperbolehkan?
• Apakah limit maksimum diterapkan?
• Apakah ORDER BY deterministic?
• Apakah sorting field di-whitelist?
• Apakah pagination strategy sesuai volume data?
• Apakah IN clause punya size limit?
• Apakah dynamic update memiliki mandatory identity predicate?
• Apakah optimistic lock/version condition ada untuk concurrent update?
• Apakah affected row count dicek?
• Apakah count query memakai filter yang sama?
• Apakah query plan untuk kombinasi umum sudah dicek?
• Apakah dynamic branches punya test?
• Apakah log/debugging cukup tanpa membocorkan PII?

29. Internal Verification Checklist

Untuk konteks internal CSG/team, verifikasi:

• Apakah ada convention penggunaan ${}?
• Apakah dynamic ORDER BY pernah dipakai?
• Apakah ada whitelist sort field di application layer?
• Apakah tenant/soft-delete/effective-date filter wajib di semua mapper?
• Apakah search endpoint punya max limit?
• Apakah offset besar dibatasi?
• Apakah keyset pagination digunakan untuk dataset besar?
• Apakah IN clause size dibatasi?
• Apakah mapper update selalu mengecek affected row count?
• Apakah optimistic locking dipakai di MyBatis write path?
• Apakah dynamic SQL branch diuji dengan Testcontainers?
• Apakah SQL injection guard test ada?
• Apakah slow query incident pernah berasal dari dynamic SQL?
• Apakah query plan review dilakukan untuk mapper kompleks?
• Apakah platform/DBA punya guideline query search/reporting?

30. Senior Engineer Heuristics

1. Dynamic SQL should reduce duplication, not hide decisions.
   Jika XML mulai menjadi decision engine, pindahkan keputusan ke Java/application layer.

2. Mandatory predicates should not be dynamic.
   Tenant, identity, version, soft delete, dan security filter biasanya harus eksplisit.

3. Every ${} deserves suspicion.
   Bukan berarti selalu salah, tetapi harus terbukti aman melalui whitelist.

4. Search endpoints need product limits.
   Query fleksibel tanpa limit adalah incident menunggu waktu.

5. Sort fields are part of API contract.
   Jangan biarkan client memilih arbitrary database expression.

6. Test dangerous branches, not only happy path.
   Dynamic SQL bug biasanya muncul saat parameter null/kosong/aneh.

7. EXPLAIN common filter combinations.
   Query yang cepat untuk satu filter bisa lambat untuk kombinasi lain.

8. For write operations, count affected rows.
   Dynamic update tanpa affected row validation mudah menyembunyikan concurrent/correctness bug.

31. Summary

MyBatis dynamic SQL adalah alat yang sangat berguna untuk enterprise search, filtering, sorting, pagination, patch update, dan PostgreSQL-specific query.

Tetapi dynamic SQL harus dikendalikan dengan disiplin:

• gunakan #{} untuk value binding,
• hindari ${} kecuali dari whitelist internal,
• jangan buat predicate mandatory menjadi optional,
• validasi dan normalisasi request sebelum mapper,
• batasi query tanpa filter,
• buat ORDER BY deterministic,
• batasi IN clause,
• test semua branch penting,
• cek query plan untuk kombinasi umum,
• log mapper/query behavior dengan redaksi PII,
• review dynamic update dengan affected row dan optimistic lock.

Dynamic SQL yang baik tetap SQL-visible, secure, transaction-aware, dan production-debuggable.