实验二　连接 MySQL,实现 3 个真实数据接口

实验信息

一、实验目的

1. 知识目标

• 理解三层架构(Controller-Service-Dao)的数据流动方向

• 理解 JdbcTemplate 在 Spring Boot 中的角色与使用方式

• 理解 Lambda 表达式 + 泛型 <T> 在代码复用中的价值

• 理解"双数据源"的概念——同一个项目里同时连接 MySQL 与 ClickHouse

2. 能力目标(核心)

• 能在实验一的项目基础上,继续往里加代码,完成真实数据接口

• 能正确编写 DTO 类,设计统一的接口返回格式

• 能用 JdbcTemplate 写 SQL 查询并把结果封装成 Java 对象

• 能用构造方法注入的方式,在各层之间组织依赖关系

• 能用 @RequestParam 接收前端参数,并做参数防御

• 能用浏览器或 curl 验证接口返回真实 JSON

3. 素养目标

• 建立"让代码长出来"的工程感——不是写一次性 demo,而是在已有项目上持续叠加

• 培养"永远不相信前端参数"的安全意识

• 培养面对 SQL 报错、连接失败时分层定位问题的能力

• 体会"写一次就能复用"的程序员美学(通过泛型 ApiResp)

二、对接岗位与能力点(校企合作特色)

💼 行业说明:本实验做的事情,就是企业里 90% 的 Java 后端工作的核心场景——按需求,从数据库里把数据查出来,组装成 JSON,通过 HTTP 给前端。区别只是字段更多、SQL 更复杂、数据库可能是多个。

三、实验环境

1. 硬件环境

2. 软件环境

3. 前置条件检查清单

• 实验一已完成,/api/health 接口能返回 {"code":200,"msg":"ok"}

• master 节点的 MySQL、ClickHouse 服务都已启动

• Flink 任务正在运行,4 张 MySQL 表里有数据

• Spark 任务已运行,ClickHouse 的 iot_report.dws_device_pred_detail 表有数据

⚠️ 没有数据的话,接口能跑通但返回的全是 0/空数组,看不出实验效果。任务 1 会先验证这些前置条件。

四、实验原理与知识准备

1. 三层架构里数据是怎么流的

前端浏览器
   ↓ 发请求 GET /api/kpi/summary?limit=60
[Controller]   接收请求 + 参数校验 + 返回 JSON      ← 不写 SQL、不写业务计算
   ↓ 调用 service.xxx()
[Service]      组装数据、业务计算                   ← 不写 SQL
   ↓ 调用 dao.xxx()
[DAO]          执行 SQL                            ← 不写业务、不拼 JSON
   ↓
[MySQL / ClickHouse]

每一层只干自己的事,这就是分层架构的核心规矩。本实验你会真切体会到分层的好处:Controller 干净到一行 SQL 都没有,SQL 改了只动 DAO,业务变了只动 Service。

2. JdbcTemplate 是什么

直接用 JDBC 写代码很啰嗦——开连接、写 SQL、读结果、关连接,样板代码一大堆:

// ❌ 原始 JDBC(20 行写一个查询)
Connection conn = DriverManager.getConnection(...);
PreparedStatement ps = conn.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
while (rs.next()) { ... }
rs.close(); ps.close(); conn.close();

Spring 把这些重复活全包了,给我们一个工具叫 JdbcTemplate:

// ✅ 用 JdbcTemplate(1 行查询)
jdbcTemplate.query(sql, (rs, rowNum) -> new Xxx(...));

JdbcTemplate 是 Spring 启动时自动创建好的(因为 application.properties 配了 MySQL),我们在 DAO 里直接用构造方法注入就行,不用自己 new。

3. 泛型 ApiResp<T> —— 一个类通吃所有返回类型

实验一的 CommonResp 只有 code + msg 两个字段,本实验要返回真实数据,需要再加一个 data 字段。但 data 装的东西每个接口都不一样:

为每种类型写一个 ApiRespXxx?不可能(违反"不重复"铁律)。

用泛型 <T>——一个类装得下所有类型:

public class ApiResp<T> {           // <T> = 占位符,意思是"某种类型,到时候再说"
    private Integer code;
    private String msg;
    private T data;                  // T 装啥都行
}

// 使用时再指定 T 是什么:
ApiResp<KpiSummary> r1;              // T = KpiSummary
ApiResp<List<TempTrendPoint>> r2;    // T = List<TempTrendPoint>

类比:ApiResp<T> 就像通用快递盒,盒子尺寸标准,但里面装啥都行;<KpiSummary> 就是快递单上写的"装的是手机"。

4. 双数据源(MySQL + ClickHouse)

本实验要在同一个项目里同时连两个数据库:

为什么 ClickHouse 不走自动配置?

• Spring Boot 自动配置只支持一个 DataSource

• 我们让 MySQL 走自动配置,ClickHouse 用工具类手动连

• 这是企业里多数据源最常见的简单做法

五、实验内容与任务分解

本实验包含 6 个子任务,全部在实验一的项目基础上继续写,按以下顺序逐步完成:

整体进化路线图

实验一交付物(/api/health 能跑)
         ↓ 任务 1
[破冰] 验证 Java 能连 MySQL(用临时代码查数据)
         ↓ 任务 2
搭好数据格式("快递盒"4 个)
         ↓ 任务 3
搭好 ClickHouse 连接通道
         ↓ 任务 4
写 3 个 DAO(底层:查数据库)
         ↓ 任务 5
写 Service + Controller(中层 + 上层)
         ↓ 任务 6
浏览器看到 3 个真实接口

💡 关键提示:不要新建项目! 整个实验全在实验一的 smart-screen-backend 项目里继续写。这才是企业开发的真实节奏——项目从 0 慢慢长大,而不是每次都重头来。

六、实验步骤与参考答案

📖 详细操作过程见配套教程《阶段二:连接 MySQL,实现 3 个真实接口》。 本节列出 核心步骤 + 验收点 + 参考代码(含完整注释),学生完成后可对照检查。

任务 1:破冰——验证 MySQL 连通性

1.1 任务目标

在写一堆复杂的 DAO/Service/Controller 之前,先做一件事:

确认你的 Java 代码真的能连上 MySQL,并把表里的数据查出来。

如果连这一步都跑不通,后面的代码写得再漂亮也是白搭。先把"地基"验证一下,再上楼。

1.2 第 1 步:在 MySQL 命令行检查 4 张表

登录 master 节点,进 mysql 命令行,验证 4 张表里有数据:

# 登录 mysql
mysql -uroot -p123456

-- 选择数据库
use rt;

-- 查看 4 张表的最新数据(每张表都应该有数据)
SELECT * FROM rt_kpi_online_10s ORDER BY ts DESC LIMIT 3;
SELECT * FROM rt_kpi_alarm_10s  ORDER BY ts DESC LIMIT 3;
SELECT * FROM rt_temp_trend_10s ORDER BY ts DESC LIMIT 3;
SELECT * FROM rt_alarm_top5_1m  ORDER BY dt DESC LIMIT 3;

期望看到每张表都返回 3 行数据。

⚠️ 如果表是空的:说明 Flink 还没在写数据,先去启动 Flink 任务,等几分钟数据流过来再回来。表是空的就做后面的实验,接口会返回 0 或 [],无法验证。

1.3 第 2 步:在启动类里加临时测试代码

打开 SmartScreenBackendApplication.java,临时加一段验证代码:

📁 路径: src/main/java/com/demo/smartscreenbackend/SmartScreenBackendApplication.java

package com.demo.smartscreenbackend;

import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;
import org.springframework.jdbc.core.JdbcTemplate;

@SpringBootApplication
public class SmartScreenBackendApplication {

    public static void main(String[] args) {
        SpringApplication.run(SmartScreenBackendApplication.class, args);
    }

    /**
     * ⚠️ 这是临时验证代码,任务 1 完成后会删掉。
     *
     * @Bean + CommandLineRunner 的作用:
     *   Spring Boot 启动完成后,会自动执行 CommandLineRunner 里的代码。
     *   非常适合做"启动时跑一次"的验证工作。
     *
     * 参数 JdbcTemplate 是 Spring 自动注入的——
     *   因为我们在 application.properties 配了 MySQL,
     *   Spring 启动时已经自动给我们 new 好了一个 JdbcTemplate。
     */
    @Bean
    public CommandLineRunner mysqlConnTest(JdbcTemplate jdbcTemplate) {
        return args -> {
            System.out.println("===============================");
            System.out.println("        MySQL 连通性测试        ");
            System.out.println("===============================");

            // 查询 4 张表的数据行数
            // queryForObject(SQL, 返回类型.class):返回单个值
            Long online = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM rt.rt_kpi_online_10s", Long.class);
            Long alarm = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM rt.rt_kpi_alarm_10s", Long.class);
            Long temp = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM rt.rt_temp_trend_10s", Long.class);
            Long top  = jdbcTemplate.queryForObject(
                    "SELECT COUNT(*) FROM rt.rt_alarm_top5_1m", Long.class);

            System.out.println("rt_kpi_online_10s 表 = " + online + " 条");
            System.out.println("rt_kpi_alarm_10s  表 = " + alarm  + " 条");
            System.out.println("rt_temp_trend_10s 表 = " + temp   + " 条");
            System.out.println("rt_alarm_top5_1m  表 = " + top    + " 条");
            System.out.println("===============================");
        };
    }
}

1.4 第 3 步:启动项目,看控制台输出

启动 Spring Boot 项目,等控制台输出到最后,应该看到:

===============================
        MySQL 连通性测试        
===============================
rt_kpi_online_10s 表 = 1234 条
rt_kpi_alarm_10s  表 = 856 条
rt_temp_trend_10s 表 = 1456 条
rt_alarm_top5_1m  表 = 12 条
===============================

🎉 看到这些数字,说明:

• ✅ Spring Boot 自动配置 MySQL 数据源成功

• ✅ JdbcTemplate 工具可用

• ✅ 你的 Java 代码能跟 MySQL 对话

• ✅ MySQL 表里确实有数据

💼 这是你第一次让 Java 跟数据库说上话——后面所有的接口,本质上都是这个动作的"放大版":换个 SQL、换个返回类型而已。

1.5 第 4 步:删除临时测试代码

验证成功后,把刚才加的 mysqlConnTest 方法(包括 @Bean 注解)整段删掉,把启动类恢复成原样:

@SpringBootApplication
public class SmartScreenBackendApplication {
    public static void main(String[] args) {
        SpringApplication.run(SmartScreenBackendApplication.class, args);
    }
}

💼 企业开发节奏:写一段临时代码验证可行性 → 验证通过 → 删掉/搬到正式位置 → 写规范代码。 这就是真实工作流——不要把验证代码当作正式代码留在项目里,会污染代码库。

1.6 ✅ 任务 1 验收点

任务 2:写 4 个 DTO

2.1 任务目标

DTO = Data Transfer Object(数据传输对象),就是专门用来"装数据传给前端"的类。

我们要做 4 个"快递盒":

💡 先把盒子做好,后面查到数据直接往里装就行。这就是 DTO 优先的设计思路。

2.2 📝 参考代码

2.2.1 ApiResp.java —— 统一返回格式(泛型版)

📁 路径: src/main/java/com/demo/smartscreenbackend/dto/ApiResp.java

package com.demo.smartscreenbackend.dto;

/**
 * 统一接口返回结果(泛型版)
 *
 * 所有接口都用它包装返回,前端看到的 JSON 长这样:
 *   { "code": 200, "msg": "ok", "data": ??? }
 *
 * <T> 是泛型占位符,意思是"某种类型,具体是啥到时候再说":
 *   ApiResp<KpiSummary>           → data 是 KpiSummary 对象
 *   ApiResp<List<TempTrendPoint>> → data 是温度趋势点列表
 *   ApiResp<String>               → data 是字符串
 *
 * 对比实验一的 CommonResp:
 *   CommonResp 只有 code + msg,没法装真实数据;
 *   ApiResp<T> 多了 data 字段,而且通过泛型实现"一个类装下所有类型"。
 */
public class ApiResp<T> {

    /** 状态码:200=成功,500=失败 */
    private Integer code;

    /** 提示信息 */
    private String msg;

    /** 实际数据载荷(类型由 T 决定) */
    private T data;

    /** 无参构造方法(Jackson 反序列化 JSON 需要) */
    public ApiResp() {}

    /** 双参构造方法:仅状态码 + 提示信息(失败时常用,没 data) */
    public ApiResp(Integer code, String msg) {
        this.code = code;
        this.msg = msg;
    }

    /** 三参构造方法:状态码 + 提示信息 + 数据(成功时常用) */
    public ApiResp(Integer code, String msg, T data) {
        this.code = code;
        this.msg = msg;
        this.data = data;
    }

    /**
     * 静态工厂方法:快速创建成功响应
     *
     * 用法:return ApiResp.ok(data);   ← 比 new ApiResp<>(200, "ok", data) 短得多
     *
     * ⚠️ 注意 static 方法前面必须再声明一次 <T>:
     *   因为类上的 <T> 是"对象的 T",
     *   static 方法不依赖对象,需要方法自己声明一个 <T>。
     */
    public static <T> ApiResp<T> ok(T data) {
        return new ApiResp<>(200, "ok", data);
    }

    /** 静态工厂方法:快速创建失败响应 */
    public static <T> ApiResp<T> fail(String msg) {
        return new ApiResp<>(500, msg, null);
    }

    // ============ getter / setter ============
    public Integer getCode() { return code; }
    public void setCode(Integer code) { this.code = code; }

    public String getMsg() { return msg; }
    public void setMsg(String msg) { this.msg = msg; }

    public T getData() { return data; }
    public void setData(T data) { this.data = data; }
}

💡 可以删掉实验一的 CommonResp.java 吗? 暂时不用删,留着也无所谓。任务 5 我们会用 ApiResp 替代它的角色,但删不删 CommonResp 不影响功能。强迫症同学可以删。

2.2.2 KpiSummary.java —— 4 个 KPI 指标

📁 路径: dto/KpiSummary.java

package com.demo.smartscreenbackend.dto;

/**
 * 大屏顶部 4 个 KPI 卡片数据
 *
 * 字段说明:
 *   onlineCount    在线设备数      ← 来自 MySQL.rt_kpi_online_10s
 *   alarmCount     告警总数        ← 来自 MySQL.rt_kpi_alarm_10s
 *   avgTemp        平均温度        ← 来自 MySQL.rt_temp_trend_10s
 *   highRiskCount  预测高风险数    ← 来自 ClickHouse.dws_device_pred_detail
 *
 * 字段类型说明:
 *   用包装类(Long、Double)而不是基本类型(long、double),
 *   因为 Spring 转 JSON 时,包装类可以为 null,基本类型不能。
 */
public class KpiSummary {

    private Long   onlineCount;     // 在线设备数
    private Long   alarmCount;      // 告警总数
    private Double avgTemp;         // 平均温度(摄氏度)
    private Long   highRiskCount;   // 预测高风险设备数

    public KpiSummary() {}

    public KpiSummary(Long onlineCount, Long alarmCount,
                      Double avgTemp, Long highRiskCount) {
        this.onlineCount = onlineCount;
        this.alarmCount = alarmCount;
        this.avgTemp = avgTemp;
        this.highRiskCount = highRiskCount;
    }

    // getter / setter
    public Long getOnlineCount() { return onlineCount; }
    public void setOnlineCount(Long onlineCount) { this.onlineCount = onlineCount; }

    public Long getAlarmCount() { return alarmCount; }
    public void setAlarmCount(Long alarmCount) { this.alarmCount = alarmCount; }

    public Double getAvgTemp() { return avgTemp; }
    public void setAvgTemp(Double avgTemp) { this.avgTemp = avgTemp; }

    public Long getHighRiskCount() { return highRiskCount; }
    public void setHighRiskCount(Long highRiskCount) { this.highRiskCount = highRiskCount; }
}

2.2.3 TempTrendPoint.java —— 温度趋势点

📁 路径: dto/TempTrendPoint.java

package com.demo.smartscreenbackend.dto;

/**
 * 温度趋势曲线上的一个点
 *
 * 字段说明:
 *   ts       时间(字符串格式,前端直接渲染)
 *   avgTemp  平均温度
 *   maxTemp  最高温度
 *
 * 为什么 ts 用 String 而不是 Date/LocalDateTime?
 *   前端拿到字符串可直接显示,不用做日期格式化转换。
 *   后端从 MySQL 查出来时,直接 getString() 拿到的就是格式化好的字符串。
 *   这样前后端都省事。
 */
public class TempTrendPoint {

    private String ts;          // 时间点
    private Double avgTemp;     // 平均温度
    private Double maxTemp;     // 最高温度

    public TempTrendPoint() {}

    public TempTrendPoint(String ts, Double avgTemp, Double maxTemp) {
        this.ts = ts;
        this.avgTemp = avgTemp;
        this.maxTemp = maxTemp;
    }

    public String getTs() { return ts; }
    public void setTs(String ts) { this.ts = ts; }

    public Double getAvgTemp() { return avgTemp; }
    public void setAvgTemp(Double avgTemp) { this.avgTemp = avgTemp; }

    public Double getMaxTemp() { return maxTemp; }
    public void setMaxTemp(Double maxTemp) { this.maxTemp = maxTemp; }
}

2.2.4 AlarmTopItem.java —— 告警 Top 排名项

📁 路径: dto/AlarmTopItem.java

package com.demo.smartscreenbackend.dto;

/**
 * 告警最多的设备排行榜的一行
 *
 * 字段说明:
 *   deviceId  设备编号(例如 "device-003")
 *   cnt       该设备的告警次数
 */
public class AlarmTopItem {

    private String deviceId;    // 设备编号
    private Long   cnt;         // 告警次数

    public AlarmTopItem() {}

    public AlarmTopItem(String deviceId, Long cnt) {
        this.deviceId = deviceId;
        this.cnt = cnt;
    }

    public String getDeviceId() { return deviceId; }
    public void setDeviceId(String deviceId) { this.deviceId = deviceId; }

    public Long getCnt() { return cnt; }
    public void setCnt(Long cnt) { this.cnt = cnt; }
}

2.3 ✅ 任务 2 验收点

任务 3:准备 ClickHouse 连接工具

3.1 任务目标

后面任务 4 的 KpiSummaryDao 要查 ClickHouse(算"高风险设备数")。 为了让 ClickHouse 的连接逻辑只写一遍,我们先建两个工具类:

• ClickHouseProps(配置类):集中读取配置文件里的 url/账号/密码

• ClickHouseJdbc(连接工具):根据配置生成 Connection

3.2 关键技术点:@Value 和 @Component

• @Value("${xxx}"): 把 application.properties 里的配置值"塞进"Java 变量

• @Component: 标记这个类是个"Spring 管理的 Bean",可被其他类自动注入

3.3 📝 参考代码

3.3.1 ClickHouseProps.java —— 读取配置

📁 路径: src/main/java/com/demo/smartscreenbackend/config/ClickHouseProps.java

package com.demo.smartscreenbackend.config;

import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Component;

/**
 * ClickHouse 配置属性类
 *
 * 作用:
 *   把 application.properties 里的 clickhouse.url/username/password 三行配置,
 *   集中读到这个类的成员变量里,供其他类(如 ClickHouseJdbc)使用。
 *
 * 为什么不直接在每个用到的地方写 @Value?
 *   1) 集中管理:改配置 key 时只动一处
 *   2) 类型安全:取的时候是 getUrl()/getUsername(),IDE 有提示
 *   3) 可测试:单元测试时容易 mock
 */
@Component  // 交给 Spring 管理 → 别的类可以注入它
public class ClickHouseProps {

    /**
     * @Value("${xxx}") 是 Spring 提供的注解
     *
     * Spring 启动时,会:
     *   1) 读 application.properties 里的 clickhouse.url
     *   2) 把值"塞进"下面这个 url 变量
     *
     * 注意:不要给字段写初始值(= "xxx"),不然会覆盖 Spring 注入的值。
     */
    @Value("${clickhouse.url}")
    private String url;

    @Value("${clickhouse.username}")
    private String username;

    @Value("${clickhouse.password}")
    private String password;

    public String getUrl() { return url; }
    public String getUsername() { return username; }
    public String getPassword() { return password; }
}

3.3.2 ClickHouseJdbc.java —— 获取连接

📁 路径: src/main/java/com/demo/smartscreenbackend/util/ClickHouseJdbc.java

package com.demo.smartscreenbackend.util;

import com.demo.smartscreenbackend.config.ClickHouseProps;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;

/**
 * ClickHouse 连接工具类
 *
 * 作用:
 *   根据配置类拿到 url/账号/密码,生成 ClickHouse 数据库连接(Connection)。
 *   DAO 层用这个连接执行 SQL。
 *
 * 为什么自己手写,不让 Spring 自动配置?
 *   Spring Boot 默认只能自动配置一个数据源(已经被 MySQL 占了),
 *   ClickHouse 只能手动连。
 *   这种做法在企业里很常见,叫"双数据源"。
 */
@Component  // 交给 Spring 管理 → DAO 类可以注入它
public class ClickHouseJdbc {

    /**
     * private final:
     *   - private:外部不能直接读写
     *   - final:一旦初始化就不能改(更安全)
     *
     * 这里我们用"构造方法注入"——
     *   Spring 在 new ClickHouseJdbc 时,会自动把 ClickHouseProps 对象传进来。
     */
    private final ClickHouseProps props;

    /**
     * 构造方法注入
     * Spring 看到这个构造方法,会自动找到 ClickHouseProps 对象并传进来。
     * 不需要写 @Autowired(Spring 4.3+ 单构造方法自动注入)。
     */
    public ClickHouseJdbc(ClickHouseProps props) {
        this.props = props;
    }

    /**
     * 获取一个 ClickHouse 数据库连接
     *
     * @return Connection 可执行 SQL 的连接对象
     * @throws Exception 连接失败时抛出(比如服务没起、网络不通)
     */
    public Connection getConn() throws Exception {
        return DriverManager.getConnection(
                props.getUrl(),
                props.getUsername(),
                props.getPassword()
        );
    }
}

3.4 ✅ 任务 3 验收点

任务 4:写 3 个 DAO

4.1 任务目标

按"一个接口 = 一个 DAO"的原则,创建 3 个 DAO 类:

DAO 只干一件事:执行 SQL。不写业务、不拼 JSON。

4.2 关键技术点

Lambda 表达式 rs -> ... 怎么读

jdbcTemplate.query(sql, rs -> rs.next() ? rs.getLong("cnt") : 0L);
//                       └──┬──┘  └──────────┬───────────────┘
//                          参数              函数体

• rs:JdbcTemplate 自动传入的结果集(ResultSet),代表"查出来的整批数据"

• ->:Lambda 写法,左边参数 → 右边表达式

• rs.next():游标移到下一行,有数据返回 true

• ? ::三元运算符,格式 = 条件 ? 真时执行 : 假时执行

• 0L:数字 0,字母 L 表示 long 类型

jdbcTemplate.query() 的两种用法

4.3 📝 参考代码

4.3.1 KpiSummaryDao.java —— 查 MySQL + ClickHouse

📁 路径: src/main/java/com/demo/smartscreenbackend/dao/KpiSummaryDao.java

package com.demo.smartscreenbackend.dao;

import com.demo.smartscreenbackend.util.ClickHouseJdbc;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * KPI 汇总 DAO(数据访问层)
 *
 * 职责:
 *   从 MySQL 和 ClickHouse 查询"大屏顶部 4 个 KPI"的原始数据。
 *
 * 提供 4 个方法:
 *   latestOnlineCount()   ← MySQL  : 最新在线设备数
 *   latestAlarmCount()    ← MySQL  : 最新告警数
 *   latestAvgTemp()       ← MySQL  : 最新平均温度
 *   latestHighRiskCount() ← ClickHouse : 当前高风险设备数
 */
@Repository  // 标记为数据访问层 Bean,语义上和 @Component 等价但更明确
public class KpiSummaryDao {

    /** MySQL 操作工具(Spring 自动注入) */
    private final JdbcTemplate jdbcTemplate;

    /** ClickHouse 连接工具(我们任务 3 自己写的) */
    private final ClickHouseJdbc clickHouseJdbc;

    /**
     * 构造方法注入
     * Spring 会自动把两个工具对象传进来
     */
    public KpiSummaryDao(JdbcTemplate jdbcTemplate, ClickHouseJdbc clickHouseJdbc) {
        this.jdbcTemplate = jdbcTemplate;
        this.clickHouseJdbc = clickHouseJdbc;
    }

    /**
     * 查询:最新在线设备数
     * 表 rt_kpi_online_10s 是按时间倒序查最新一条
     */
    public Long latestOnlineCount() {
        String sql = "SELECT online_count " +
                     "FROM rt.rt_kpi_online_10s " +
                     "ORDER BY ts DESC LIMIT 1";

        // jdbcTemplate.query(sql, ResultSetExtractor):
        //   ResultSetExtractor 接收整个 ResultSet,自己决定怎么处理
        //   这里用 Lambda:rs -> rs.next() ? rs.getLong(...) : 0L
        //   含义:如果游标能 next() 到下一行,取 online_count;否则返回 0L
        return jdbcTemplate.query(
                sql,
                rs -> rs.next() ? rs.getLong("online_count") : 0L
        );
    }

    /** 查询:最新告警数 */
    public Long latestAlarmCount() {
        String sql = "SELECT alarm_count " +
                     "FROM rt.rt_kpi_alarm_10s " +
                     "ORDER BY ts DESC LIMIT 1";
        return jdbcTemplate.query(
                sql,
                rs -> rs.next() ? rs.getLong("alarm_count") : 0L
        );
    }

    /** 查询:最新平均温度 */
    public Double latestAvgTemp() {
        String sql = "SELECT avg_temp " +
                     "FROM rt.rt_temp_trend_10s " +
                     "ORDER BY ts DESC LIMIT 1";
        return jdbcTemplate.query(
                sql,
                rs -> rs.next() ? rs.getDouble("avg_temp") : 0.0
        );
    }

    /**
     * 查询:当前高风险设备数(从 ClickHouse)
     *
     * 业务规则:
     *   每台设备取它"最新一次预测"的 prob1(风险概率),
     *   prob1 >= 0.9 算高风险,统计这种设备的个数。
     *
     * SQL 解读:
     *   内层子查询:按 device_id 分组,用 argMax 取每组 event_time 最大那条的 prob1
     *   外层:对子查询结果做过滤(last_prob >= 0.9)和计数
     *
     * 为什么不用 JdbcTemplate?
     *   JdbcTemplate 绑定的是 MySQL 数据源,
     *   ClickHouse 是另一个数据源,我们用任务 3 的 ClickHouseJdbc 手动连。
     */
    public Long latestHighRiskCount() {
        String sql = "SELECT count() AS cnt " +
                     "FROM ( " +
                     "    SELECT device_id, " +
                     "           argMax(prob1, event_time) AS last_prob " +
                     "    FROM iot_report.dws_device_pred_detail " +
                     "    GROUP BY device_id " +
                     ") t " +
                     "WHERE t.last_prob >= 0.9";

        // try-with-resources:自动关 conn / ps / rs,避免资源泄露
        // 不写 close(),Java 自动在 try 块结束时关掉
        try (
            Connection conn = clickHouseJdbc.getConn();
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery()
        ) {
            return rs.next() ? rs.getLong("cnt") : 0L;
        } catch (Exception e) {
            // 查询失败不让接口挂掉,打日志返回 0
            // 生产环境会用 log.error(e),实验里 e.printStackTrace() 简化
            e.printStackTrace();
            return 0L;
        }
    }
}

4.3.2 TempTrendDao.java —— 查温度趋势列表

📁 路径: dao/TempTrendDao.java

package com.demo.smartscreenbackend.dao;

import com.demo.smartscreenbackend.dto.TempTrendPoint;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * 温度趋势 DAO
 *
 * 职责:
 *   从 MySQL 查询最近 N 条温度趋势,封装成 TempTrendPoint 列表。
 */
@Repository
public class TempTrendDao {

    private final JdbcTemplate jdbcTemplate;

    public TempTrendDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    /**
     * 查询最近 N 条温度趋势
     *
     * @param limit 要查多少条(60 表示 60 条 = 10 分钟数据)
     * @return 温度趋势点列表
     */
    public List<TempTrendPoint> latestTrend(int limit) {

        // SQL:按时间倒序取前 limit 条
        // 注意:LIMIT 后面用 ? 占位符,不要直接拼字符串,防 SQL 注入
        String sql = "SELECT ts, avg_temp, max_temp " +
                     "FROM rt.rt_temp_trend_10s " +
                     "ORDER BY ts DESC " +
                     "LIMIT ?";

        // jdbcTemplate.query(sql, 参数数组, RowMapper):
        //   RowMapper 是"每行怎么转对象"的规则
        //   这里用 Lambda 写:每行 (rs, rowNum) → new TempTrendPoint(...)
        //
        //   rs     :当前这一行的数据
        //   rowNum :行号(从 0 开始),这里没用到但必须写出来
        return jdbcTemplate.query(
                sql,
                new Object[]{limit},        // 给 ? 传值
                (rs, rowNum) -> new TempTrendPoint(
                        rs.getString("ts"),       // 时间字符串
                        rs.getDouble("avg_temp"), // 平均温度
                        rs.getDouble("max_temp")  // 最高温度
                )
        );
    }
}

⚠️ IDEA 里 rt.rt_temp_trend_10s 下面会有红色波浪线:Unable to resolve table 'rt'。 不用管——这只是 IDEA 的 SQL 检查提示,因为 IDEA 没连你的数据库,不知道这个库存在。 运行时只要 MySQL 里有这个表就 OK。

4.3.3 AlarmTopDao.java —— 查告警 Top

📁 路径: dao/AlarmTopDao.java

package com.demo.smartscreenbackend.dao;

import com.demo.smartscreenbackend.dto.AlarmTopItem;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.Collections;
import java.util.List;

/**
 * 告警 Top DAO
 *
 * 职责:
 *   从 MySQL 查"最新 1 分钟窗口里告警最多的 N 个设备"。
 *
 * 业务复杂度:
 *   表 rt_alarm_top5_1m 里有多个时间窗口的数据,
 *   我们要先找出"最新的窗口"(window_end 最大),
 *   再在这个窗口里取 rn ≤ N 的几条。
 *   所以这个 DAO 要执行 2 条 SQL。
 */
@Repository
public class AlarmTopDao {

    private final JdbcTemplate jdbcTemplate;

    public AlarmTopDao(JdbcTemplate jdbcTemplate) {
        this.jdbcTemplate = jdbcTemplate;
    }

    public List<AlarmTopItem> latestTop(int limit) {

        // 第 1 条 SQL:取最新窗口的结束时间
        String latestWindowSql =
                "SELECT MAX(window_end) AS window_end " +
                "FROM rt.rt_alarm_top5_1m";

        String windowEnd = jdbcTemplate.query(
                latestWindowSql,
                rs -> rs.next() ? rs.getString("window_end") : null
        );

        // 防御:如果表是空的,直接返回空列表
        // Collections.emptyList() 比 new ArrayList<>() 更优雅,且不可变
        if (windowEnd == null) {
            return Collections.emptyList();
        }

        // 第 2 条 SQL:在最新窗口里取前 limit 名
        // COALESCE(cnt, 0):如果 cnt 是 null,用 0 替代,避免后续报错
        String sql =
                "SELECT device_id, COALESCE(cnt, 0) AS cnt " +
                "FROM rt.rt_alarm_top5_1m " +
                "WHERE window_end = ? AND rn <= ? " +
                "ORDER BY rn ASC";

        // 这里 ? 有两个,所以参数数组有两个元素:windowEnd 和 limit
        return jdbcTemplate.query(
                sql,
                new Object[]{windowEnd, limit},
                (rs, rowNum) -> new AlarmTopItem(
                        rs.getString("device_id"),
                        rs.getLong("cnt")
                )
        );
    }
}

4.4 ✅ 任务 4 验收点

任务 5:写 Service + Controller

5.1 任务目标

DAO 已经能查数据库了,接下来:

• Service:调用 DAO,把零散数据组装成 DTO

• Controller:接收请求,调用 Service,把结果包装成 ApiResp 返回前端

5.2 关键技术点

@RequestParam 怎么用

前端访问 /api/kpi/tempTrend?limit=60 时,limit=60 是查询参数。 后端用 @RequestParam 接住:

public ApiResp<...> tempTrend(
        @RequestParam(defaultValue = "60") int limit  // 默认 60
) { ... }

• defaultValue = "60":前端不传 limit 时默认用 60

• int limit:Spring 自动把字符串 "60" 转成整型

参数防御(必做)

if (limit <= 0)    limit = 60;     // 防止前端传 0 或负数
if (limit > 500)   limit = 500;    // 防止前端传 999999 拖垮数据库

💼 企业铁律:永远不要相信前端传过来的参数。任何参数都要做边界检查。这是后端工程师的基本素养。

5.3 📝 参考代码

5.3.1 KpiService.java —— 业务层

📁 路径: src/main/java/com/demo/smartscreenbackend/service/KpiService.java

package com.demo.smartscreenbackend.service;

import com.demo.smartscreenbackend.dao.AlarmTopDao;
import com.demo.smartscreenbackend.dao.KpiSummaryDao;
import com.demo.smartscreenbackend.dao.TempTrendDao;
import com.demo.smartscreenbackend.dto.AlarmTopItem;
import com.demo.smartscreenbackend.dto.KpiSummary;
import com.demo.smartscreenbackend.dto.TempTrendPoint;
import org.springframework.stereotype.Service;

import java.util.List;

/**
 * KPI 业务层
 *
 * 职责:
 *   调用 3 个 DAO 拿原始数据 → 组装成 DTO → 交给 Controller。
 *
 * 注意 Service 的"分寸感":
 *   - 它不写 SQL(SQL 在 DAO)
 *   - 它不拼 JSON(JSON 在 Controller)
 *   - 它只做"业务组装"——把零散数据装进 DTO
 */
@Service
public class KpiService {

    private final KpiSummaryDao kpiSummaryDao;
    private final TempTrendDao  tempTrendDao;
    private final AlarmTopDao   alarmTopDao;

    /**
     * 构造方法注入 3 个 DAO
     *
     * Spring 看到这个构造方法,自动把 3 个 DAO 对象传进来。
     * 多个依赖时,构造方法注入比 @Autowired 字段注入更优——
     * 因为参数列表本身就把"我需要哪些依赖"写明白了。
     */
    public KpiService(KpiSummaryDao kpiSummaryDao,
                      TempTrendDao  tempTrendDao,
                      AlarmTopDao   alarmTopDao) {
        this.kpiSummaryDao = kpiSummaryDao;
        this.tempTrendDao  = tempTrendDao;
        this.alarmTopDao   = alarmTopDao;
    }

    /**
     * 业务:大屏顶部 4 个 KPI 汇总
     *
     * 流程:
     *   1) 分别调 4 次 DAO,拿到 4 个原始值
     *   2) 把 4 个值塞进 KpiSummary
     *   3) 返回
     */
    public KpiSummary summary() {
        Long   online   = kpiSummaryDao.latestOnlineCount();
        Long   alarm    = kpiSummaryDao.latestAlarmCount();
        Double avgTemp  = kpiSummaryDao.latestAvgTemp();
        Long   highRisk = kpiSummaryDao.latestHighRiskCount();

        return new KpiSummary(online, alarm, avgTemp, highRisk);
    }

    /**
     * 业务:温度趋势列表
     *
     * 这里业务很简单,直接转发给 DAO 即可。
     * 但仍然走 Service 这一层,是为了**保持架构一致性**——
     * 以后想加缓存、加业务处理,在这里加就行,Controller 不动。
     */
    public List<TempTrendPoint> tempTrend(int limit) {
        return tempTrendDao.latestTrend(limit);
    }

    /**
     * 业务:告警设备 Top
     */
    public List<AlarmTopItem> alarmTop(int limit) {
        return alarmTopDao.latestTop(limit);
    }
}

5.3.2 KpiController.java —— 接口层

📁 路径: src/main/java/com/demo/smartscreenbackend/controller/KpiController.java

package com.demo.smartscreenbackend.controller;

import com.demo.smartscreenbackend.dto.AlarmTopItem;
import com.demo.smartscreenbackend.dto.ApiResp;
import com.demo.smartscreenbackend.dto.KpiSummary;
import com.demo.smartscreenbackend.dto.TempTrendPoint;
import com.demo.smartscreenbackend.service.KpiService;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

import java.util.List;

/**
 * KPI 接口层(Controller)
 *
 * 职责:
 *   1) 接收 HTTP 请求,接住查询参数
 *   2) 参数防御(永远不相信前端)
 *   3) 调用 Service 拿数据
 *   4) 用 ApiResp.ok() 包装返回 JSON
 *
 * 提供 3 个接口:
 *   GET /api/kpi/summary             顶部 KPI 汇总
 *   GET /api/kpi/tempTrend?limit=60  温度趋势
 *   GET /api/kpi/alarmTop?limit=3    告警 Top
 */
@RestController
@RequestMapping("/api/kpi")  // 类前缀,3 个接口路径都以 /api/kpi 开头
public class KpiController {

    private final KpiService kpiService;

    public KpiController(KpiService kpiService) {
        this.kpiService = kpiService;
    }

    // ==================== 接口 1:KPI 汇总 ====================

    /**
     * GET /api/kpi/summary
     *
     * 没有查询参数,最简单的接口形式
     */
    @GetMapping("/summary")
    public ApiResp<KpiSummary> summary() {
        KpiSummary data = kpiService.summary();
        return ApiResp.ok(data);
    }

    // ==================== 接口 2:温度趋势 ====================

    /**
     * GET /api/kpi/tempTrend?limit=60
     *
     * @param limit 查多少条,默认 60
     */
    @GetMapping("/tempTrend")
    public ApiResp<List<TempTrendPoint>> tempTrend(
            @RequestParam(defaultValue = "60") int limit
    ) {
        // 参数防御:把异常值掰回合理范围
        if (limit <= 0)  limit = 60;
        if (limit > 500) limit = 500;  // 上限保护数据库

        List<TempTrendPoint> data = kpiService.tempTrend(limit);
        return ApiResp.ok(data);
    }

    // ==================== 接口 3:告警 Top ====================

    /**
     * GET /api/kpi/alarmTop?limit=3
     *
     * @param limit 取前几名,默认 3
     */
    @GetMapping("/alarmTop")
    public ApiResp<List<AlarmTopItem>> alarmTop(
            @RequestParam(defaultValue = "3") int limit
    ) {
        if (limit <= 0)  limit = 3;
        if (limit > 20)  limit = 20;   // 排行榜不应该太长

        List<AlarmTopItem> data = kpiService.alarmTop(limit);
        return ApiResp.ok(data);
    }
}

5.4 ✅ 任务 5 验收点

任务 6:浏览器测试 3 个接口

6.1 任务目标

启动项目,用浏览器(或 curl)依次访问 3 个接口,确认返回正确 JSON。

6.2 接口 1 — KPI 汇总

http://localhost:8080/api/kpi/summary

期望返回(数字以你实际数据为准):

{
  "code": 200,
  "msg": "ok",
  "data": {
    "onlineCount": 50,
    "alarmCount": 10,
    "avgTemp": 26.35,
    "highRiskCount": 8
  }
}

6.3 接口 2 — 温度趋势

http://localhost:8080/api/kpi/tempTrend?limit=60

期望返回:

{
  "code": 200,
  "msg": "ok",
  "data": [
    {"ts":"2026-02-16 14:30:30","avgTemp":26.3543,"maxTemp":45.27},
    {"ts":"2026-02-16 14:30:20","avgTemp":26.8192,"maxTemp":45.27}
    // ... 共 60 条
  ]
}

尝试一下不同 limit:

• ?limit=5 → 应该返回 5 条

• ?limit=0 → 防御代码会改成 60,返回 60 条

• ?limit=99999 → 防御代码会改成 500,返回 500 条(不会拖垮 MySQL)

• 不传 limit → 用默认值 60

6.4 接口 3 — 告警 Top

http://localhost:8080/api/kpi/alarmTop?limit=3

期望返回:

{
  "code": 200,
  "msg": "ok",
  "data": [
    {"deviceId":"device-003","cnt":3},
    {"deviceId":"device-033","cnt":3},
    {"deviceId":"device-046","cnt":3}
  ]
}

6.5 ✅ 任务 6 验收点

七、常见问题处理(FAQ)