JPQL, Criteria API & Queries

Criteria: Predicates & Dynamic Queries

18 min Lesson 6 of 13

Criteria: Predicates & Dynamic Queries

The previous lesson introduced the Criteria API and showed how to build a basic typed query. The real payoff of the API, however, is its ability to compose query conditions at runtime. When a user can filter a product list by name, category, minimum price, maximum price, or any combination of those, a static JPQL string simply cannot express every possibility. Criteria predicates are the building blocks that solve this problem.

What Is a Predicate?

In the Criteria API, a Predicate represents a boolean SQL expression — anything that could appear in a WHERE clause or as a join condition. The CriteriaBuilder is the factory that creates predicates, and you can combine them with logical operators to model arbitrarily complex conditions.

Common builder methods that produce predicates:

cb.equal(expr, value) — equality test
cb.like(expr, pattern) — SQL LIKE
cb.greaterThanOrEqualTo(expr, value) / cb.lessThanOrEqualTo(expr, value)
cb.between(expr, lower, upper)
cb.isNull(expr) / cb.isNotNull(expr)
cb.in(expr).value(v1).value(v2) — SQL IN
cb.and(p1, p2, ...)) / cb.or(p1, p2, ...) — logical combination
cb.not(predicate) — negation

Building a Simple Predicate

Assume an Order entity with fields status, totalAmount, and a many-to-one relationship to Customer. A query for orders with status SHIPPED and total above a threshold looks like this:

import jakarta.persistence.*;
import jakarta.persistence.criteria.*;
import org.springframework.stereotype.Repository;
import java.math.BigDecimal;
import java.util.List;

@Repository
public class OrderRepository {

    @PersistenceContext
    private EntityManager em;

    public List<Order> findShippedAbove(BigDecimal minAmount) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<Order> cq = cb.createQuery(Order.class);
        Root<Order> order = cq.from(Order.class);

        Predicate isShipped  = cb.equal(order.get("status"), "SHIPPED");
        Predicate aboveLimit = cb.greaterThanOrEqualTo(
                order.get("totalAmount"), minAmount);

        cq.select(order).where(cb.and(isShipped, aboveLimit));

        return em.createQuery(cq).getResultList();
    }
}

Each predicate is an independent object. You combine them by passing them to cb.and() before handing the result to cq.where().

Dynamic Query Composition

The key pattern for search forms is to accumulate predicates into a list and only add a predicate when the corresponding filter value is actually present. This keeps the generated SQL minimal — no spurious AND 1=1 tricks required.

import java.util.ArrayList;

public List<Order> search(String status, BigDecimal minAmount,
                            BigDecimal maxAmount, Long customerId) {

    CriteriaBuilder cb = em.getCriteriaBuilder();
    CriteriaQuery<Order> cq = cb.createQuery(Order.class);
    Root<Order> order = cq.from(Order.class);

    List<Predicate> predicates = new ArrayList<>();

    if (status != null && !status.isBlank()) {
        predicates.add(cb.equal(order.get("status"), status));
    }
    if (minAmount != null) {
        predicates.add(cb.greaterThanOrEqualTo(
                order.get("totalAmount"), minAmount));
    }
    if (maxAmount != null) {
        predicates.add(cb.lessThanOrEqualTo(
                order.get("totalAmount"), maxAmount));
    }
    if (customerId != null) {
        Join<Order, Customer> customer = order.join("customer", JoinType.INNER);
        predicates.add(cb.equal(customer.get("id"), customerId));
    }

    cq.select(order)
      .where(predicates.toArray(new Predicate[0]))
      .orderBy(cb.desc(order.get("createdAt")));

    return em.createQuery(cq).getResultList();
}

Passing an empty predicate array to where(): When no filters are provided, predicates is empty and toArray produces a zero-length array. JPA treats where(new Predicate[0]) as no restriction — equivalent to omitting the WHERE clause entirely. This means the same method gracefully handles both "return everything" and fully filtered queries.

OR Conditions and Nested Logic

Mix cb.and() and cb.or() to model compound conditions. Suppose you want orders that are either PENDING or past-due (status OVERDUE), and whose amount exceeds a minimum:

Predicate isPending  = cb.equal(order.get("status"), "PENDING");
Predicate isOverdue  = cb.equal(order.get("status"), "OVERDUE");
Predicate statusPart = cb.or(isPending, isOverdue);

Predicate aboveMin   = cb.greaterThan(order.get("totalAmount"), minAmount);

cq.where(cb.and(statusPart, aboveMin));

This compiles to:

WHERE (o.status = 'PENDING' OR o.status = 'OVERDUE')
  AND o.total_amount > ?

LIKE and Case-Insensitive Search

Full-text substring matching is a common requirement. Wrap the column expression with cb.lower() and lowercase the search string in Java to achieve portable case-insensitive matching:

if (nameFragment != null && !nameFragment.isBlank()) {
    predicates.add(
        cb.like(
            cb.lower(order.get("description")),
            "%" + nameFragment.toLowerCase() + "%"
        )
    );
}

LIKE and index usage: A leading wildcard (%term) disables B-tree index scans on most databases. For production full-text search on large tables prefer a dedicated solution (PostgreSQL tsvector, Elasticsearch, or a full-text index) rather than LIKE '%...'. Reserve leading-wildcard LIKE for admin panels and low-volume searches.

Subqueries as Predicates

The Criteria API supports correlated subqueries through cq.subquery(). This is useful for "exists" conditions — for example, returning only customers who have at least one shipped order:

CriteriaQuery<Customer> cq = cb.createQuery(Customer.class);
Root<Customer> customer = cq.from(Customer.class);

Subquery<Long> sub = cq.subquery(Long.class);
Root<Order> subOrder = sub.from(Order.class);
sub.select(subOrder.get("id"))
   .where(
       cb.equal(subOrder.get("customer"), customer),
       cb.equal(subOrder.get("status"), "SHIPPED")
   );

cq.select(customer).where(cb.exists(sub));

The Specification Pattern (Spring Data JPA)

Writing all predicate logic inside a repository quickly becomes verbose. Spring Data JPA's Specification<T> interface wraps a single predicate-building lambda into a reusable, composable unit. Enable it by extending JpaSpecificationExecutor<T> in your repository interface:

import org.springframework.data.jpa.domain.Specification;
import org.springframework.data.jpa.repository.*;

public interface OrderRepository
        extends JpaRepository<Order, Long>,
                JpaSpecificationExecutor<Order> {}

Define individual specifications as static factory methods in a utility class:

public class OrderSpecs {

    public static Specification<Order> hasStatus(String status) {
        return (root, query, cb) ->
            status == null ? cb.conjunction()
                           : cb.equal(root.get("status"), status);
    }

    public static Specification<Order> amountAtLeast(BigDecimal min) {
        return (root, query, cb) ->
            min == null ? cb.conjunction()
                        : cb.greaterThanOrEqualTo(root.get("totalAmount"), min);
    }
}

Combine and execute them with a fluent API:

import static com.example.OrderSpecs.*;
import static org.springframework.data.jpa.domain.Specification.where;

List<Order> results = orderRepository.findAll(
    where(hasStatus(status))
        .and(amountAtLeast(minAmount))
);

Why cb.conjunction()? It returns a predicate that is always TRUE, acting as a no-op when a filter value is absent. This lets each specification method handle its own null check internally, so the caller never has to guard against nulls.

Performance Considerations

Bind parameters, never string concatenation. All Criteria API values are automatically bound as JDBC parameters — SQL injection is not possible, and the database can cache execution plans.
Avoid overly broad result sets. Dynamic queries can easily return millions of rows if all filters are null. Apply sensible pagination (setFirstResult / setMaxResults) for any user-facing search.
Fetch joins in dynamic queries. If you conditionally join an association to filter by it, also fetch it to avoid N+1 loads. Use root.fetch("customer", JoinType.LEFT) when the join is for data loading, and root.join() when it is purely for filtering.
Distinct when fetch-joining collections. Joining a one-to-many inflates the result set. Add cq.distinct(true) or use DISTINCT in the query to deduplicate.

Summary

Predicates are the atomic units of Criteria API queries. Build them individually with CriteriaBuilder, collect them in a list, and combine them with cb.and() / cb.or() to represent any filter combination a user might choose. The Specification pattern from Spring Data JPA raises this to an even higher level of reusability by encapsulating each predicate as a named, composable object. Together, these techniques replace the fragile string-concatenation approach to dynamic SQL with a type-safe, testable, and readable alternative.