Build a JPA projection query with optional filters

Implement an order-list query with optional status and customerId filters. Return only id, status, total, and createdAt, never entities. Results are ordered by createdAt DESC, id DESC and limited to at most 100 rows. An absent filter must not generate a fake predicate.

status=PAID, customerId=empty -> latest paid orders across customers
status=empty, customerId=42   -> latest orders for customer 42
both empty, limit=20          -> latest 20 orders

• Use a constructor or record projection.
• Add predicates only for present filters.
• Clamp requested limit to 1 through 100.
• Use a unique tie-breaker after the timestamp.
• Do not call findAll and filter in Java.

1. Create a record containing only the fields needed by the list.
2. Use CriteriaBuilder.construct for the select shape.
3. Collect predicates conditionally from the optional inputs.
4. Apply deterministic descending order by timestamp and ID.
5. Set the database result limit before executing the query.

public record OrderListItem(
        long id, OrderStatus status, java.math.BigDecimal total,
        java.time.Instant createdAt) {}

@org.springframework.stereotype.Repository
class OrderListQuery {
    @jakarta.persistence.PersistenceContext
    private jakarta.persistence.EntityManager em;

    java.util.List<OrderListItem> find(
            java.util.Optional<OrderStatus> status,
            java.util.Optional<Long> customerId, int requestedLimit) {
        var cb = em.getCriteriaBuilder();
        var query = cb.createQuery(OrderListItem.class);
        var order = query.from(Order.class);
        var predicates = new java.util.ArrayList<jakarta.persistence.criteria.Predicate>();

        status.ifPresent(value -> predicates.add(cb.equal(order.get("status"), value)));
        customerId.ifPresent(value -> predicates.add(cb.equal(order.get("customer").get("id"), value)));

        query.select(cb.construct(OrderListItem.class,
                order.get("id"), order.get("status"),
                order.get("total"), order.get("createdAt")))
             .where(predicates.toArray(jakarta.persistence.criteria.Predicate[]::new))
             .orderBy(cb.desc(order.get("createdAt")), cb.desc(order.get("id")));

        int limit = Math.max(1, Math.min(requestedLimit, 100));
        return em.createQuery(query).setMaxResults(limit).getResultList();
    }
}

Application-side work is O(f) for the small number of filters and O(r) to materialize returned rows. Database cost depends on selectivity and indexes; the result is bounded to 100 rows.

• Equal timestamps remain stable because ID is a unique tie-breaker.
• An empty predicate array is valid and means no filter.
• A customer association path may generate a join; inspect the SQL and index the actual predicate.
• A projection constructor mismatch fails at query execution and deserves an integration test.

• Test every combination of the two optional filters.
• Insert equal timestamps and verify ID ordering.
• Request limits below 1 and above 100 and verify clamping.
• Inspect generated SQL or persistence context to prove full entities are not loaded.

• Using (:status is null or o.status = :status) everywhere can produce weaker plans on some databases.
• Filtering after findAll moves database work and unbounded data into application memory.
• Returning entities invites lazy loading and exposes fields the list never needed.
• Ordering only by timestamp permits unstable ties.