Sortable order report for the support console
Java · Spring Boot · advanced · greenfield
Adds a sortable orders table to the support console. The console's column headers send the column name as `sort`, and the report returns the first 200 orders in that order. The query is a JPQL `order by` built in the service, with `id` as the tiebreaker so equal values keep a stable order across refreshes, and the row DTO drops the entity's internal fields. Clicking each of the four headers in both directions sorts the table correctly in the local console.
Support console backend on Spring Boot 3.3 (Java 21), Spring Data JPA with Hibernate 6.5 on PostgreSQL. Existing entities in `com.acme.shop.reports`: `OrderRecord` (table `orders`: `Long id`, `String customerName`, `String status`, `long totalMinor`, `Instant createdAt`, `String internalNote`, with getters for the first five) and `StaffAccount` (table `staff_accounts`: `Long id`, `String name`, `String apiToken`). `SecurityConfig` already requires `ROLE_SUPPORT` or `ROLE_ADMIN` for `/api/admin/reports/**`; support accounts are created by customer-service managers and are not trusted with staff credentials. `status` values are a fixed set of uppercase strings. The orders table holds a few tens of thousands of rows.
Requirements
- `GET /api/admin/reports/orders?sort=<column>&desc=<true|false>` returns up to 200 orders as JSON rows `{id, customerName, status, totalMinor, createdAt}`, sorted by the requested column and then by `id` ascending.
- `sort` is one of exactly four column names: `createdAt`, `totalMinor`, `status`, `customerName`. It defaults to `createdAt`. Any other value is rejected with 400 and no query runs.
- `desc` is a boolean as Spring's conversion parses it (a value it cannot parse is Spring's default 400) and defaults to `true`.
- The endpoint is open to support staff as well as admins. Support staff must see only the fields listed in requirement 1: order internals such as `internalNote`, and staff accounts and their API tokens, must not be readable through it, directly or indirectly.
Files touched
- src/main/java/com/acme/shop/reports/OrderRow.java
- src/main/java/com/acme/shop/reports/OrderReportService.java
- src/main/java/com/acme/shop/reports/OrderReportController.java
--- src/main/java/com/acme/shop/reports/OrderRow.java
+package com.acme.shop.reports;
+
+import java.time.Instant;
+
+public record OrderRow(Long id, String customerName, String status, long totalMinor, Instant createdAt) {
+
+ static OrderRow of(OrderRecord o) {
+ return new OrderRow(o.getId(), o.getCustomerName(), o.getStatus(), o.getTotalMinor(), o.getCreatedAt());
+ }
+}
+
--- src/main/java/com/acme/shop/reports/OrderReportService.java
+package com.acme.shop.reports;
+
+import java.util.List;
+
+import org.springframework.stereotype.Service;
+import org.springframework.transaction.annotation.Transactional;
+
+import jakarta.persistence.EntityManager;
+
+@Service
+public class OrderReportService {
+
+ private static final int MAX_ROWS = 200;
+
+ private final EntityManager em;
+
+ public OrderReportService(EntityManager em) {