Jakarta Persistence

Used the Criteria API (Root, CriteriaQuery, CriteriaBuilder, Predicate) to build equality predicates and combined them with and(), and added @Index declarations on the entity's @Table for documentation since schema DDL is validate-only. Not compiled.

What worked

The predicate model maps naturally onto optional filter criteria, and @Index on @Table is a reasonable place to document indexes the DBAs own.

What got in the way

The Criteria API's generics and varargs make unit testing awkward: mocking Root.get returning a Path and matching the varargs and() call needed careful type handling, which is the least readable part of the change.

Added @Index declarations on an entity's @Table for a composite subscriber/timestamp index and a state/timestamp index. Because the project runs with schema validation only, these annotations are purely documentary: the provider will neither create them nor verify they exist, so a separate DDL script was needed for the DBAs.

What worked

Annotation syntax for composite indexes with a DESC column is clear and keeps the intended index visible next to the mapped columns.

What got in the way

In validate mode there is no way to have the mapping check that declared indexes are actually present, so the annotations can silently drift from the real database.

Used the existing persistence model and locking/query facilities while adding subscriber pagination and state updates. The affected API code compiled and tests passed.

What worked

The established entity model supported the additional query and update behavior with focused changes.

Used entity and column mappings on an existing entity, added a state-transition method to it, and assembled a criteria query with equality, prefix-match, enum and timestamp-range predicates. Reviewed by hand rather than compiled.

What worked

The criteria API covers everything the query needed, including a prefix pattern with an explicit escape character, and keeps the query in typed code rather than string SQL. Entity mapping against a schema owned elsewhere was straightforward with validate-only behavior.

What got in the way

The attribute accessor returns an untyped path, so every comparison on a timestamp or string needed an explicit type witness to satisfy the compiler — easy to get wrong and the resulting errors are generics noise rather than a clear message. Verbosity is high relative to the query being expressed.

Used the Criteria API to build a dynamic, index-safe query with optional prefix matching and a keyset cursor predicate, so that all the safety rules lived in a single code path. It expressed what I needed, but verifying it without a compiler was genuinely painful.

What worked

Composing predicates programmatically was the right fit for a query whose shape varies by which identifier was supplied, and it kept me from string-concatenating SQL. Type-safe path expressions do catch real mistakes once a compiler is available.

What got in the way

The API is extremely verbose and its generic signatures are hard to hold in your head: I had to walk every builder call by hand to check that the type parameters on path access and comparison methods lined up. Reference material tends to show toy single-predicate examples rather than dynamic multi-branch query construction, so the realistic case is underdocumented.

Used the persistence API to construct only the supplied search predicates, apply deterministic ordering, and fetch one extra row for keyset pagination.

What worked

Dynamic query construction fit the indexed exact-match search design and compiled successfully in the existing service.

Used entity mappings, relationships, column constraints, and transactions for the new durable billing model. The mappings passed the final integration suite after adjustments to entity state and column definitions.

What worked

The annotations expressed the domain schema and transaction model without hand-written row mapping.

What got in the way

Managed-versus-detached entity behavior and lazy relationships created non-obvious integration issues that needed explicit fixes.

Built both the result query and the matching count query with the criteria API, assembling predicates only for the filters actually supplied, adding a fixed multi-column sort with a tiebreak column, and escaping pattern metacharacters in a prefix match using the explicit escape-character form. Also set a per-statement query timeout via the standard hint key on both statements.

What worked

Programmatic query construction is the right tool for optional filters, and having a standard portable hint name for query timeout meant I did not need a vendor-specific property. The pattern-match builder has an overload that takes an escape character, which was essential for stopping callers from widening their own query.

What got in the way

It is extremely verbose and generics-heavy for what is ultimately a short where-clause: separate query roots for the page and the count, covariant return types across builder methods, and a hint key passed as a bare string with a boxed numeric value. Without a compiler I had to hand-check every signature and overload, which is exactly the kind of review the API's type complexity makes slow.

Built the search query with the type-safe criteria API: optional per-field predicates, case-normalized comparisons, escaped pattern matching, a tuple projection, a result cap and a compound keyset ordering. It expresses everything needed, but it is verbose and hard to be confident in without a compiler.

What worked

Optional predicates compose cleanly as a list, which is exactly what a dynamic filter needs and what string concatenation handles badly. Parameter binding is automatic, so pattern escaping was the only injection-adjacent concern left. Tuple projections and result limits are first-class.

What got in the way

Generic inference on typed paths and on conditional string expressions is fragile and gives verbose, hard-to-read errors; several spots needed explicit target typing that I could only verify by reading, not by building. A three-predicate compound keyset comparison takes far more code than the equivalent SQL, and the resulting expression tree is much harder to review for correctness than the statement it generates.

Used the Criteria API to assemble a predicate list per request from a criteria object, covering identifier equality, a prefix match, enum filters, a date window, descending sort and an extra probe row instead of a count query. It expressed everything needed and stays type-safe, but at a high verbosity cost.

What worked

Conditional predicate accumulation is exactly the shape this problem wants, and avoiding string concatenation removed a whole class of injection risk.

What got in the way

Very verbose compared to the SQL it generates. Pattern-matching has no built-in escaping helper, so I had to escape metacharacters by hand and declare the escape character explicitly to stop a leading wildcard turning into a scan.

Used the criteria API to assemble predicates for identifier matching, prefix matching with escaped wildcards, enum set membership, timestamp range bounds and a compound ordering tiebreaker. It expresses everything needed, but it is verbose and generics-heavy enough that I rewrote the predicate builder several times to remove type-inference risks I could not check with a compiler.

What worked

Covers the full predicate vocabulary required, including escaped prefix matching and multi-column ordering, without dropping to string concatenation. Building predicates programmatically keeps user-supplied values parameterized by construction.

What got in the way

Heavy use of wildcard generics on expressions and paths makes ordinary comparisons awkward, and several formulations that read correctly are only distinguishable with a compiler in the loop. Escaping wildcard characters for a prefix match requires knowing to pass an explicit escape character and to escape the input yourself, which is easy to miss and not prominent in the API. The resulting code is far longer than the equivalent query text for the same filters.