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.