A search endpoint accepts five optional filter fields, any combination of which may be present. How would you build that query with the JPA Criteria API, and why is that preferable to concatenating a JPQL string?
answer
- List<Predicate> + if per filter
- where(array) = implicit AND, empty = no WHERE
- no 1=1, no dangling AND
- join cache map so a join is added once
- whitelist the sort field before root.get
basics
~20 sCreate one CriteriaQuery and Root, then collect a List<Predicate>: for each filter that is non-null, add the matching predicate. Finish with cq.where(list.toArray(new Predicate[0])), which ANDs them. No string building, no dangling AND, values bound as parameters.
solid answer
~50 sI build the query once and grow a predicate list: ```java List<Predicate> ps = new ArrayList<>(); if (f.status() != null) ps.add(cb.equal(root.get("status"), f.status())); if (f.title() != null) ps.add(cb.like(cb.lower(root.get("title")), "%" + f.title().toLowerCase() + "%")); if (f.from() != null) ps.add(cb.greaterThanOrEqualTo(root.get("createdAt"), f.from())); cq.where(ps.toArray(new Predicate[0])); ``` Passing an array to `where` is an implicit AND, so no filter means no WHERE clause at all — no `1=1` placeholder, no dangling `AND`. Sorting is the same idea: map a sort key to `cb.asc(root.get(name))` — but only from a **whitelist**, since a raw client string reaching `root.get` is both an injection-shaped hole and an `IllegalArgumentException` waiting to happen. Versus string concatenation: the tree cannot produce malformed SQL, values become bind parameters automatically, joins are added once via a `Map<String, Join<?,?>>` instead of being duplicated in the string, and predicate-building can be factored into reusable methods that the paginated list query and its count query both call.
code
java · 25 linesCriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Order> cq = cb.createQuery(Order.class);
Root<Order> o = cq.from(Order.class);
List<Predicate> ps = new ArrayList<>();
if (f.status() != null) {
ps.add(cb.equal(o.get("status"), f.status()));
}
if (f.minTotal() != null) {
ps.add(cb.greaterThanOrEqualTo(o.get("total"), f.minTotal()));
}
if (f.customerName() != null) {
Join<Order, Customer> c = o.join("customer", JoinType.INNER);
ps.add(cb.like(cb.lower(c.get("name")), f.customerName().toLowerCase() + "%"));
}
cq.select(o).where(ps.toArray(new Predicate[0]));
if (SORTABLE.contains(f.sort())) {
cq.orderBy(cb.desc(o.get(f.sort())));
}
List<Order> page = em.createQuery(cq)
.setFirstResult(f.offset())
.setMaxResults(f.limit())
.getResultList();go deeper
Show the List<Predicate> loop and where(array) as implicit AND, and say why the 1=1 string hack is unnecessary.
Add the join cache, OR grouping via cb.or, wildcard placement in like, and the isNull versus equal-null distinction.
Cover reusing the predicate builder for the count query, whitelisting sort keys, and where you draw the line between criteria and plain JPQL in a codebase.
Discuss owning search as a capability: a shared filter-to-predicate mapping layer, index implications of the predicates you allow, and guarding against arbitrarily expensive filter combinations.
## The problem A filter form with N optional fields has 2^N possible WHERE clauses. Writing them as static JPQL is impossible past a couple of fields, and building the string at runtime is where the classic bugs live: a leading `AND` with no preceding condition, the `where 1=1` hack, a filter value spliced directly into the text, or a join emitted twice because two branches both needed it. ## The predicate-list pattern The Criteria API answer is boring and reliable. Build the query skeleton once, then accumulate predicates in a list, adding only for the filters the caller supplied: ```java CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Order> cq = cb.createQuery(Order.class); Root<Order> o = cq.from(Order.class); List<Predicate> ps = new ArrayList<>(); ... cq.select(o).where(ps.toArray(new Predicate[0])); ``` `where(Predicate...)` treats multiple predicates as a **conjunction**. An empty array is legal and means "no restriction", so the zero-filter case needs no special handling. If you prefer a single expression, `cb.and(ps.toArray(...))` is equivalent, and `cb.conjunction()` gives you a neutral TRUE element to fold onto — useful if you want to write the composition as a reduce rather than a list. OR groups nest the same way: build a sub-list, wrap it with `cb.or(...)`, and add that one predicate to the outer list. ## Joins added on demand When a filter lives on an association you need a join — but only once, even if two filters use it. Keep a small cache: ```java Map<String, Join<?, ?>> joins = new HashMap<>(); Function<String, Join<?, ?>> join = name -> joins.computeIfAbsent(name, n -> o.join(n, JoinType.LEFT)); if (f.customerName() != null) ps.add(cb.equal(join.apply("customer").get("name"), f.customerName())); ``` Without the cache you get two joins to the same table and, with a to-many association, duplicated rows. ## Sorting and paging Dynamic ORDER BY is the other half of a search endpoint. `cq.orderBy(cb.asc(root.get(sortField)))` works, but `sortField` must come from a **fixed whitelist** (a `Set<String>` or an enum mapping to metamodel attributes). An unknown attribute name throws `IllegalArgumentException` at build time, and echoing arbitrary client input into path navigation is precisely the class of thing security reviews flag. Paging is unchanged: `setFirstResult`/`setMaxResults` on the `TypedQuery`. ## Reusing the predicates for a count A paginated search usually needs a total. The count query is a **separate** `CriteriaQuery<Long>` with its **own** root — roots are not transferable between queries. Factor the filter logic so both call it: ```java private List<Predicate> filters(CriteriaBuilder cb, Root<Order> o, Filter f) { ... } ``` Then the list query and `count.select(cb.count(o2)).where(filters(cb, o2, f))` stay in lockstep. This is the single biggest maintainability win of building queries as objects: filter logic becomes an ordinary Java method with ordinary Java reuse. ## Why not the string - **Correctness by construction.** A predicate tree has no syntax; you cannot forget a space or leave a trailing `AND`. - **Automatic binding.** Values handed to `cb.equal`/`cb.like` are rendered as JDBC bind parameters, so the SQL text is stable (good for the database's plan cache) and the value cannot alter the statement. - **Composability.** Predicates are values: pass them, store them in a map keyed by filter name, unit-test a builder method in isolation. - **Refactor safety** when combined with the static metamodel — renaming an entity attribute breaks compilation instead of failing at runtime. ## The honest costs Criteria code is verbose and noticeably harder to read than the SQL it produces; you cannot paste it into a database console; stack traces and provider errors point at builder calls rather than at a query line. Teams usually settle on a split: **fixed-shape queries in JPQL or named queries, genuinely dynamic ones in Criteria**. Building a criteria tree per request also costs a little CPU and garbage; it is negligible next to the round trip, but it is not free, and caching the built query (with explicit `ParameterExpression`s) is the escape hatch for a very hot path. ## Pitfalls Calling `where` twice (the second replaces the first). Forgetting that `cb.like` needs the wildcards inside the value. Comparing a `null` filter with `cb.equal(path, null)` — that renders `= null`, which is never true; use `cb.isNull(path)`. And leaving `distinct` on a to-many join off, then wondering about duplicate rows.
- How do you produce the total-row count for the same filters without duplicating the filter code?Extract the filter logic into a method that takes the CriteriaBuilder and a Root and returns List<Predicate>. Build a second CriteriaQuery<Long> with its own root — roots cannot be shared across queries — select cb.count(root) and apply the same predicate list. Skip any fetch joins and ORDER BY in the count query, since they add cost and, in the case of a to-many fetch, change the counted row set.
- A caller sends sort=name for ORDER BY. What is wrong with passing that string straight to root.get(sort)?Two things. Functionally, an attribute name that does not exist throws IllegalArgumentException at build time, so any typo becomes a 500. Security-wise, letting unvalidated client input choose what the query touches is the same class of problem as injection even though no SQL text is concatenated — it can expose sortable columns you never intended. Map the incoming key through a whitelist or an enum to a metamodel attribute.
- Is there a performance cost to constructing a criteria tree on every request?A small one: object allocation plus translation of the tree to SQL. Hibernate caches the translated form keyed by the query structure, so repeated identical shapes are cheap, and the cost is dwarfed by the database round trip. If a very hot path really matters, build the CriteriaQuery once with explicit ParameterExpressions and reuse the definition, binding values per execution.
saying these in an interview costs you the question
- Building the WHERE clause as a string with a 1=1 seed and appending AND fragments
- Calling cq.where() once per filter and expecting them to accumulate
- Passing a client-supplied sort field straight into root.get without a whitelist
- Adding the same join twice because two filters each needed it
- Using cb.equal(path, null) to test for NULL instead of cb.isNull(path)