Project: Advanced Queries
This final lesson of the tutorial brings every technique you have learned — JPQL, the Criteria API, fetch joins, projections, dynamic predicates, and named queries — into a single, realistic feature. You will build an Order Search & Analytics API for an e-commerce back-end: a service that lets a front-end filter orders by multiple optional criteria and retrieve aggregate statistics, all within one Spring Boot 3 / Hibernate 6 application.
Domain Model
The domain uses three entities. Order belongs to a Customer and contains a collection of OrderItem lines, each linked to a Product. The relationships are typical in any transactional system and give us meaningful join and aggregation territory.
// Order.java
@Entity
@Table(name = "orders")
@NamedQuery(
name = "Order.findRecentByStatus",
query = "SELECT o FROM Order o WHERE o.status = :status AND o.createdAt >= :since ORDER BY o.createdAt DESC"
)
public class Order {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "customer_id", nullable = false)
private Customer customer;
@OneToMany(mappedBy = "order", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
private List<OrderItem> items = new ArrayList<>();
@Enumerated(EnumType.STRING)
private OrderStatus status; // PENDING, PROCESSING, SHIPPED, DELIVERED, CANCELLED
private BigDecimal totalAmount;
private LocalDateTime createdAt;
}
// OrderItem.java
@Entity
@Table(name = "order_items")
public class OrderItem {
@Id @GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "order_id")
private Order order;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "product_id")
private Product product;
private int quantity;
private BigDecimal unitPrice;
}
Lazy loading is the correct default for @ManyToOne and @OneToMany associations. In this project you will explicitly choose when to eagerly fetch related data — with fetch joins — rather than letting Hibernate decide.
Feature 1 — Dynamic Order Search (Criteria API)
The search endpoint accepts any combination of: customer name fragment, order status, a date range, and a minimum total. Because any field can be absent, this is exactly the scenario where the Criteria API's programmatic construction shines over hand-concatenated JPQL strings.
// OrderSearchRequest.java (a plain record — no JPA annotations)
public record OrderSearchRequest(
String customerName, // optional
OrderStatus status, // optional
LocalDateTime from, // optional
LocalDateTime to, // optional
BigDecimal minTotal // optional
) {}
// OrderSearchService.java
@Service
@Transactional(readOnly = true)
public class OrderSearchService {
@PersistenceContext
private EntityManager em;
public List<Order> search(OrderSearchRequest req) {
CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Order> cq = cb.createQuery(Order.class);
Root<Order> order = cq.from(Order.class);
// Fetch customer and items in ONE query to avoid N+1
Fetch<Order, Customer> customerFetch = order.fetch("customer", JoinType.LEFT);
order.fetch("items", JoinType.LEFT); // items will be needed in the result
List<Predicate> predicates = new ArrayList<>();
if (req.customerName() != null && !req.customerName().isBlank()) {
Join<Order, Customer> c = (Join<Order, Customer>) customerFetch;
predicates.add(
cb.like(cb.lower(c.get("name")), "%" + req.customerName().toLowerCase() + "%")
);
}
if (req.status() != null) {
predicates.add(cb.equal(order.get("status"), req.status()));
}
if (req.from() != null) {
predicates.add(cb.greaterThanOrEqualTo(order.get("createdAt"), req.from()));
}
if (req.to() != null) {
predicates.add(cb.lessThanOrEqualTo(order.get("createdAt"), req.to()));
}
if (req.minTotal() != null) {
predicates.add(cb.greaterThanOrEqualTo(order.get("totalAmount"), req.minTotal()));
}
cq.select(order)
.where(predicates.toArray(Predicate[]::new))
.distinct(true) // avoid duplicates from the items fetch join
.orderBy(cb.desc(order.get("createdAt")));
return em.createQuery(cq).getResultList();
}
}
Cast the Fetch to Join when you also need to predicate on it. When you call order.fetch("customer", JoinType.LEFT), Hibernate returns a Fetch object. Because Fetch extends Join, you can safely cast it and reuse it for like() or equal() predicates without issuing a second join to the same table.
Feature 2 — Analytics via JPQL Aggregation
The analytics endpoint returns a summary per order status: count of orders, total revenue, and average order value. This is pure aggregation — no entity graph needed — so JPQL with a DTO projection is cleaner and faster than the Criteria API here.
// OrderStatusSummary.java (projection DTO)
public record OrderStatusSummary(
OrderStatus status,
long orderCount,
BigDecimal totalRevenue,
BigDecimal avgOrderValue
) {}
// In OrderAnalyticsService.java
@Service
@Transactional(readOnly = true)
public class OrderAnalyticsService {
@PersistenceContext
private EntityManager em;
public List<OrderStatusSummary> summariseByStatus() {
String jpql = """
SELECT new com.example.shop.dto.OrderStatusSummary(
o.status,
COUNT(o),
SUM(o.totalAmount),
AVG(o.totalAmount))
FROM Order o
GROUP BY o.status
ORDER BY o.status
""";
return em.createQuery(jpql, OrderStatusSummary.class).getResultList();
}
}
Feature 3 — Named Query for Recent Urgent Orders
A support dashboard needs the last 24 hours of orders in PROCESSING status. This query is called on every page refresh, making it an ideal candidate for the @NamedQuery you saw declared on the entity above — Hibernate compiles and validates it at startup, so invalid JPQL fails fast rather than at 2 AM in production.
// In OrderSearchService.java (add this method)
public List<Order> findRecentProcessing() {
return em.createNamedQuery("Order.findRecentByStatus", Order.class)
.setParameter("status", OrderStatus.PROCESSING)
.setParameter("since", LocalDateTime.now().minusHours(24))
.setMaxResults(100) // defensive page cap
.getResultList();
}
Feature 4 — Top-Selling Products (Native SQL with DTO)
Product managers want the top 10 products by revenue for any date range, joining across three tables. The query planner benefits from a specific index hint the ORM cannot express, so you drop to native SQL and map the result into a DTO.
// TopProductDto.java
public record TopProductDto(Long productId, String productName, BigDecimal revenue) {}
// In ProductAnalyticsService.java
@Service
@Transactional(readOnly = true)
public class ProductAnalyticsService {
@PersistenceContext
private EntityManager em;
@SuppressWarnings("unchecked")
public List<TopProductDto> topProductsByRevenue(LocalDateTime from, LocalDateTime to) {
String sql = """
SELECT p.id, p.name, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.created_at BETWEEN :from AND :to
AND o.status = 'DELIVERED'
GROUP BY p.id, p.name
ORDER BY revenue DESC
LIMIT 10
""";
List<Object[]> rows = em.createNativeQuery(sql)
.setParameter("from", from)
.setParameter("to", to)
.getResultList();
return rows.stream()
.map(r -> new TopProductDto(
((Number) r[0]).longValue(),
(String) r[1],
(BigDecimal) r[2]))
.toList();
}
}
Native queries bypass Hibernate's dirty-checking and cache. If you modify entities and then run a native query in the same transaction, flush the EntityManager first (em.flush()) to ensure your changes are visible to the native SQL. Also be aware that native queries skip Hibernate's second-level cache entirely.
Wiring It Together — The REST Controller
@RestController
@RequestMapping("/api/orders")
@RequiredArgsConstructor
public class OrderController {
private final OrderSearchService searchService;
private final OrderAnalyticsService analyticsService;
@GetMapping("/search")
public List<Order> search(
@RequestParam(required = false) String customerName,
@RequestParam(required = false) OrderStatus status,
@RequestParam(required = false) @DateTimeFormat(iso = ISO.DATE_TIME) LocalDateTime from,
@RequestParam(required = false) @DateTimeFormat(iso = ISO.DATE_TIME) LocalDateTime to,
@RequestParam(required = false) BigDecimal minTotal) {
return searchService.search(
new OrderSearchRequest(customerName, status, from, to, minTotal));
}
@GetMapping("/analytics/status-summary")
public List<OrderStatusSummary> statusSummary() {
return analyticsService.summariseByStatus();
}
}
Performance Trade-offs at a Glance
- JPQL + DTO projection — best for aggregation and reporting; no entity hydration cost; works well with second-level cache.
- Criteria API — best for dynamic filtering; refactor-safe; more verbose but eliminates string-concat bugs.
- Named queries — best for hot paths with fixed structure; startup validation catches typos before they reach production.
- Native SQL — last resort for database-specific features (window functions, index hints, JSON operators); sacrifices portability and cache integration.
Summary
You have combined all the querying techniques of this tutorial into a coherent feature. The Criteria API handles open-ended dynamic search; JPQL with DTO projection powers the analytics dashboard cleanly and efficiently; a named query covers the hot support-dashboard path with startup validation; and native SQL handles the one reporting query that requires vendor-specific syntax. Choosing between them is not arbitrary — each tool has a distinct cost/benefit profile. Applying the right one to each problem is what distinguishes a working developer from a senior one.