Tutorial 09: Analytics API

Once orders are flowing, managers want to know what's popular and how much revenue each day brings. The Stats API exposes four read-only endpoints backed by JPQL aggregation queries and Java stream calculations. This tutorial examines each one and the trade-offs in how the data is computed.

1. Controller: four read-only endpoints

@RestController
@RequestMapping("/api/stats")
public class StatsController {

    private final StatsService statsService;

    // constructor injection ...

    @GetMapping("/most-ordered-items")
    public ResponseEntity<List<MostOrderedItemDTO>> getMostOrderedItems() {
        return ResponseEntity.ok(statsService.getMostOrderedItems());
    }

    @GetMapping("/total-revenue")
    public ResponseEntity<HashMap<String, Double>> getTotalRevenueByDate() {
        return ResponseEntity.ok(statsService.getTotalRevenueByDate());
    }

    @GetMapping("/total-revenue-by-menu-item")
    public ResponseEntity<HashMap<String, Double>> getTotalRevenueByMenuItem() {
        return ResponseEntity.ok(statsService.getTotalRevenueByMenuItem());
    }

    @GetMapping("/average-session-revenue-by-date")
    public ResponseEntity<List<TableSessionAverageRevenueByDate>> getAverageSessionRevenueByDate() {
        return ResponseEntity.ok(statsService.getAverageTotalByTableSessionGroupedByDate());
    }
}

• All four are GET: stats endpoints are read-only and should be idempotent.
• Two endpoints return HashMap<String, Double> directly: simple enough for a dashboard chart where the key is the label and the value is the amount.
• The controller is thin: each method is a one-liner delegating entirely to the service.

2. Most-ordered items: JPQL aggregation

This is the most efficient stat: computed entirely in the database via a JPQL constructor expression.

// OrderItemRepository
@Query("SELECT new com.wassimlagnaoui.RestaurantOrder.DTO.MostOrderedItemDTO(" +
       "oi.menuItem.name, SUM(oi.quantity), COUNT(DISTINCT oi.order.id)) " +
       "FROM OrderItem oi " +
       "WHERE oi.served = true " +
       "GROUP BY oi.menuItem.name " +
       "ORDER BY SUM(oi.quantity) DESC")
List<MostOrderedItemDTO> findMostOrderedItems();

// MostOrderedItemDTO: must have a matching constructor
public class MostOrderedItemDTO {
    private String name;
    private Long totalQuantity;
    private Long orderCount;

    public MostOrderedItemDTO(String name, Long totalQuantity, Long orderCount) { ... }
}

• Constructor expression (new DTO(...) in JPQL): the DB executes the GROUP BY and SUM, and Spring instantiates the DTO directly from the result set: no intermediate entity objects are created.
• WHERE oi.served = true ensures only delivered items count towards popularity.
• COUNT(DISTINCT oi.order.id) counts how many separate orders contained this item: different from total quantity.
• The fully-qualified class name is required in the JPQL new expression.

3. Total revenue by date: Java stream calculation

public HashMap<String, Double> getTotalRevenueByDate() {
    HashMap<String, Double> result = new HashMap<>();

    // Get all unique dates from sessions (formatted "yyyy-MM-dd")
    List<String> dates = tableSessionRepository.findAllDates();

    for (String date : dates) {
        Double revenue = orderItemRepository.findAll().stream()
            .filter(oi -> oi.getOrder().getOrderDate()
                           .toLocalDate().toString().equals(date))
            .mapToDouble(oi -> oi.getMenuItem().getPrice() * oi.getQuantity())
            .sum();
        result.put(date, revenue);
    }
    return result;
}

• Trade-off: findAll() loads every order item into memory and filters in Java. This works at small scale but becomes slow as the table grows: a DB-level GROUP BY query would be more efficient.
• mapToDouble(...).sum() is the stream equivalent of a for-loop accumulator: cleaner and avoids boxing overhead.
• toLocalDate().toString() formats the date as "yyyy-MM-dd" automatically, matching the format returned by findAllDates().

4. Average session revenue by date: native SQL

// TableSessionRepository: native SQL returning Object[]
@Query(value =
    "SELECT TO_CHAR(session_start, 'YYYY-MM-DD') AS session_date, " +
    "       AVG(total) AS avg_total " +
    "FROM orders " +
    "JOIN table_session ts ON orders.session_id = ts.id " +
    "GROUP BY session_date " +
    "ORDER BY session_date DESC",
    nativeQuery = true)
List<Object[]> findAverageTotalByTableSessionGroupedByDate();

// Service: map Object[] to a typed DTO
public List<TableSessionAverageRevenueByDate> getAverageTotalByTableSessionGroupedByDate() {
    return tableSessionRepository.findAverageTotalByTableSessionGroupedByDate()
        .stream()
        .map(record -> new TableSessionAverageRevenueByDate(
            record[0].toString(),                   // session_date (String)
            Double.parseDouble(record[1].toString()) // avg_total (Double)
        ))
        .toList();
}

• nativeQuery = true: drops out of JPQL into raw SQL. Necessary here because TO_CHAR is a PostgreSQL-specific function that has no JPQL equivalent.
• Object[] mapping: native queries return rows as Object[]. The service manually casts each column by index. This is fragile: if the SELECT column order changes, the mapping silently breaks. Projections or a @SqlResultSetMapping are safer alternatives.
• AVG(total) operates on the stored order total: not recomputed from items, so it reflects the value at the time the order was placed.

Key Takeaways

• Push aggregation (SUM, COUNT, AVG, GROUP BY) into the database where possible: a JPQL constructor expression is the cleanest way to map results directly to a DTO.
• In-memory filtering with findAll().stream().filter() is quick to write but doesn't scale: refactor to a DB query when the dataset grows.
• Use nativeQuery = true only when JPQL can't express what you need (DB-specific functions, complex window queries). Document why native SQL was chosen.
• Object[] mapping from native queries is positional and fragile: prefer Spring Data projections (interface-based) for type safety.
• mapToDouble(...).sum() is the idiomatic stream way to accumulate a numeric total without a mutable variable.