缓存协作与 Redis 旁路

第 16 章 · shop-db 综合练习

本章目标：在 shop-db（或 db-demo）完成三项综合交付：纯 JDBC/SQL 运营报表、JPA 批处理查询脚本、Flyway 一次 schema 迁移；巩固 slug / is_published / price（分） 契约；为 ch17 毕业项目热身；对照 ch08～ch15 与 spring-boot-web ch05 持久化层。

学时建议：5～6 小时（含 3 小时编码）

前置：完成 ch01～ch15；MySQL Docker 与 Java 17+ 可用；HikariCP、JPA、Flyway 环境已按 ch09/ch10/ch12 配置。

16.1 练习背景

db-demo 是 shop-db 的精简沙箱：同一套商品域表，数据量为教学规模（商品 ≥500 行、订单 ≥2000 行 seed）。

shop-db/   （或 db-demo/）
├── src/main/java/com/zixian/shopdb/
│   ├── catalog/Product.java
│   ├── catalog/ProductRepository.java
│   ├── report/ReportDailyMain.java    ← JDBC
│   └── report/CatalogStatsMain.java   ← JPA
├── src/main/resources/db/migration/
│   ├── V1__initial_schema.sql
│   └── V2__add_product_view_count.sql  ← 本章
├── sql/reports/
│   ├── daily_sales.sql
│   └── top_products.sql
├── output/
│   └── report-YYYYMMDD.csv
└── docs/
    ├── PERF.md    ← ch14
    └── SCALE.md   ← ch15

虚构数据：用户 slug 均为 user-demo-*；商品 slug 如 java-handbook；不连接真实生产。

16.2 交付物清单

合计 100 分（本章练习，非 ch17 毕业答辩）。

16.3 数据模型（复习）

CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    slug VARCHAR(128) NOT NULL UNIQUE,
    name VARCHAR(256) NOT NULL,
    price BIGINT NOT NULL COMMENT '分',
    is_published TINYINT(1) NOT NULL DEFAULT 0,
    category_id BIGINT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(32) NOT NULL UNIQUE,
    user_slug VARCHAR(64) NOT NULL,
    status VARCHAR(16) NOT NULL,
    total_amount BIGINT NOT NULL COMMENT '分',
    created_at DATETIME NOT NULL
);

CREATE TABLE order_items (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    product_slug VARCHAR(128) NOT NULL,
    product_name VARCHAR(256) NOT NULL,
    price BIGINT NOT NULL COMMENT '成交单价分',
    quantity INT NOT NULL,
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

冗余字段 product_slug / product_name / price 便于报表 少 JOIN（architecture ch10 快照思想）。

16.4 任务 A：纯 SQL 日报（daily_sales.sql）

需求：统计 指定日期 各 订单状态 的订单数、销售额（分）、客单价（分，整数除法）。

sql/reports/daily_sales.sql：

-- 参数：@report_date 例如 '2026-08-18'
SELECT
    o.status,
    COUNT(*) AS order_count,
    SUM(o.total_amount) AS gmv_cents,
    IFNULL(SUM(o.total_amount) / NULLIF(COUNT(*), 0), 0) AS avg_order_cents
FROM orders o
WHERE DATE(o.created_at) = @report_date
GROUP BY o.status
ORDER BY gmv_cents DESC;

16.4.1 运行方式

mysql -h127.0.0.1 -ushop -pshop shop_db \
  -e "SET @report_date='2026-08-18'; SOURCE sql/reports/daily_sales.sql;"

16.4.2 EXPLAIN（ch14）

EXPLAIN SELECT ... FROM orders o WHERE DATE(o.created_at) = @report_date;

若 type=ALL，考虑 created_at range 改写：

WHERE o.created_at >= @report_date AND o.created_at < DATE_ADD(@report_date, INTERVAL 1 DAY)

16.5 任务 B：Top 商品（top_products.sql）

需求：指定日期销量 Top 10，输出 slug、销量、revenue_cents（分）。

SELECT
    oi.product_slug AS slug,
    SUM(oi.quantity) AS units_sold,
    SUM(oi.price * oi.quantity) AS revenue_cents
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.created_at >= @report_date
  AND o.created_at < DATE_ADD(@report_date, INTERVAL 1 DAY)
  AND o.status IN ('paid', 'shipped', 'completed')
GROUP BY oi.product_slug
ORDER BY revenue_cents DESC
LIMIT 10;

price 为分：oi.price * oi.quantity 仍为整数，禁止浮点元。

16.6 任务 C：JDBC 报表主程序

ReportDailyMain.java 使用 ch09 HikariCP：

package com.zixian.shopdb.report;

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.io.IOException;
import java.nio.file.*;
import java.sql.*;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.*;

public class ReportDailyMain {

    private static final String DAILY_SALES_SQL = """
        SELECT o.status, COUNT(*) AS order_count,
               SUM(o.total_amount) AS gmv_cents,
               IFNULL(SUM(o.total_amount)/NULLIF(COUNT(*),0),0) AS avg_order_cents
        FROM orders o
        WHERE o.created_at >= ? AND o.created_at < DATE_ADD(?, INTERVAL 1 DAY)
        GROUP BY o.status ORDER BY gmv_cents DESC
        """;

    private static final String TOP_PRODUCTS_SQL = """
        SELECT oi.product_slug AS slug, SUM(oi.quantity) AS units_sold,
               SUM(oi.price * oi.quantity) AS revenue_cents
        FROM order_items oi
        JOIN orders o ON o.id = oi.order_id
        WHERE o.created_at >= ? AND o.created_at < DATE_ADD(?, INTERVAL 1 DAY)
          AND o.status IN ('paid','shipped','completed')
        GROUP BY oi.product_slug
        ORDER BY revenue_cents DESC LIMIT 10
        """;

    public static void main(String[] args) throws Exception {
        LocalDate reportDate = args.length > 0
            ? LocalDate.parse(args[0])
            : LocalDate.now().minusDays(1);
        Path outDir = Path.of("output");
        Files.createDirectories(outDir);

        HikariConfig cfg = new HikariConfig();
        cfg.setJdbcUrl(System.getenv().getOrDefault(
            "JDBC_URL", "jdbc:mysql://127.0.0.1:3306/shop_db?useSSL=false&serverTimezone=UTC"));
        cfg.setUsername(System.getenv().getOrDefault("DB_USER", "shop"));
        cfg.setPassword(System.getenv().getOrDefault("DB_PASS", "shop"));

        try (HikariDataSource ds = new HikariDataSource(cfg)) {
            java.sql.Date sqlDate = java.sql.Date.valueOf(reportDate);
            List<String[]> rows = new ArrayList<>();
            rows.add(new String[]{"section", "key", "metric", "value"});

            try (Connection conn = ds.getConnection()) {
                appendDailySales(conn, sqlDate, rows);
                appendTopProducts(conn, sqlDate, rows);
            }

            String fname = "report-" + reportDate.format(DateTimeFormatter.BASIC_ISO_DATE) + ".csv";
            writeCsv(outDir.resolve(fname), rows);
            System.out.println("报告已生成: " + outDir.resolve(fname));
        }
    }

    private static void appendDailySales(Connection conn, java.sql.Date d, List<String[]> rows)
            throws SQLException {
        long t0 = System.nanoTime();
        try (PreparedStatement ps = conn.prepareStatement(DAILY_SALES_SQL)) {
            ps.setDate(1, d);
            ps.setDate(2, d);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String status = rs.getString("status");
                    rows.add(new String[]{"daily_sales", status, "order_count",
                        String.valueOf(rs.getLong("order_count"))});
                    rows.add(new String[]{"daily_sales", status, "gmv_cents",
                        String.valueOf(rs.getLong("gmv_cents"))});
                }
            }