README.md · 17751758772/iot-mqtt

# 一、制定Topic的规范

```bash
Pattern pattern = Pattern.compile("^iot/deviceId/\d{1,10}/sensorTag/*");
iot/deviceId/+/sensorTag/+/#,home/#

# 举例
iot/deviceId/332244/sensorTag/co2
```

##     正则匹配测试

```java
 // 测试
 String topic = "iot/deviceId/332244/sensorTag/co2";

Pattern pattern = Pattern.compile("^iot/deviceId/\\d{1,10}/sensorTag/*");
 boolean ok = pattern.matcher(topic).matches();
```

# 二、制定数据库存储结构

## 1. 表结构

```sql
/*
 Navicat Premium Data Transfer

Source Server         : iot
 Source Server Type    : MySQL
 Source Server Version : 80018
 Source Host           : localhost:3306
 Source Schema         : iot_mqtt

Target Server Type    : MySQL
 Target Server Version : 80018
 File Encoding         : 65001

Date: 08/11/2022 12:53:36
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for tb_device
-- ----------------------------
DROP TABLE IF EXISTS `tb_device`;
CREATE TABLE `tb_device`  (
                              `device_id` int(32) NOT NULL COMMENT '设备ID 惟一',
                              `device_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                              PRIMARY KEY (`device_id`) USING BTREE,
                              UNIQUE INDEX `deviceId`(`device_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_device
-- ----------------------------
INSERT INTO `tb_device` VALUES (10000001, 'Test1');
INSERT INTO `tb_device` VALUES (10000002, 'Test2');
INSERT INTO `tb_device` VALUES (10000003, 'Test3');
INSERT INTO `tb_device` VALUES (10000004, 'Test4');
INSERT INTO `tb_device` VALUES (10000005, 'Test5');

-- ----------------------------
-- Table structure for tb_sensor
-- ----------------------------
DROP TABLE IF EXISTS `tb_sensor`;
CREATE TABLE `tb_sensor`  (
                              `device_id` int(32) NULL DEFAULT NULL,
                              `sensor_tag` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                              `sensor_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
                              `sensor_val` float(32, 2) NULL DEFAULT NULL,
                              `record_time` datetime(0) NULL DEFAULT NULL,
                              INDEX `deviceId`(`device_id`) USING BTREE,
                              CONSTRAINT `deviceId` FOREIGN KEY (`device_id`) REFERENCES `tb_device` (`device_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of tb_sensor
-- ----------------------------
INSERT INTO `tb_sensor` VALUES (10000001, 'co', '一氧化碳', 20.10, '2022-11-07 20:23:48');
INSERT INTO `tb_sensor` VALUES (10000001, 'co2', '二氧化碳', 22.10, '2022-11-07 20:24:16');
INSERT INTO `tb_sensor` VALUES (10000001, 'co2', '二氧化碳', 19.20, '2022-11-07 20:24:42');
INSERT INTO `tb_sensor` VALUES (10000002, 'so2', '二氧化硫', 2.30, '2022-11-07 20:25:42');
INSERT INTO `tb_sensor` VALUES (10000002, 'co', '一氧化碳', 18.20, '2022-11-07 20:26:15');
INSERT INTO `tb_sensor` VALUES (10000003, 'so2', '二氧化硫', 3.20, '2022-11-07 20:26:38');
INSERT INTO `tb_sensor` VALUES (10000004, 'temp', '温度', 30.20, '2022-11-07 20:27:07');
INSERT INTO `tb_sensor` VALUES (10000004, 'humity', '湿度', 40.00, '2022-11-07 20:28:15');
INSERT INTO `tb_sensor` VALUES (10000005, 'tmep', '温度', 31.20, '2022-11-07 20:28:41');
INSERT INTO `tb_sensor` VALUES (10000005, 'humity', '湿度', 45.00, '2022-11-07 20:29:02');

SET FOREIGN_KEY_CHECKS = 1;

-- JOIN 查询举例
-- SELECT
--   device.device_id,
--   device.device_name,
--   sensor.sensor_tag,
--   sensor.sensor_val,
--   sensor.sensor_name,
--   sensor.record_time
-- FROM
--   tb_sensor AS sensor
--   RIGHT JOIN tb_device AS device ON sensor.device_id = device.device_id
-- WHERE
--   device.device_id = 10000002;
```

## 2.使用MybatisPlus操作数据库

测试

![1667884209771](imgs/1667884209771.png)

***部分Mapper书写举例而已***

```xml
<select id="getSensorDetailByDeviceId" resultType="com.hua.iot.domain.IOTDevice">
        SELECT device.device_id,device.device_name,
               sensor.sensor_name,sensor.sensor_tag,sensor.sensor_val,sensor.record_time
        FROM tb_sensor as sensor
        INNER JOIN tb_device as device
        ON sensor.device_id = device.device_id
        <where>
            <if test="deviceId!=null">
                device.device_id = #{deviceId}
            </if>
        </where>
    </select>
```

## 3.问题汇总

mysql 的group by 满足的规则要求：ONLY_FULL_GROUP_BY：合法检查

这个会报错，所以尽量关闭

- 查看mysql是否启用ONLY_FULL_GROUP_BY

> select @@global.sql_mode
  >
  > 
  >
  >  select @@global.sql_mode;
  > +-------------------------------------------------------------------------------------------------------------------------------------------+
  > | @@global.sql_mode                                                                                                                         |
  > +-------------------------------------------------------------------------------------------------------------------------------------------+
  > | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
  > +-------------------------------------------------------------------------------------------------------------------------------------------+
  > 1 row in set (0.00 sec)

- 如果有，删除这个模式

> set @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
  >

-  但是这样的操作只能对当前mysql服务有效，如果重启mysql，则发现还有这样的问题。
  需要在mysql的配置文件中设置sql_mode

>  在my.ini中添加 (windows)
  > sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

8.0 已经没有这个模式了

>[mysqld] 
  >sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION

sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

# 三、Redis 设计

## 1.数据保存规则

首先手动插入数据库中的值，也按预设的规则

key:   iot:deviceId:10000002:sensortag:co2 （  iot+设备id+传感器id)

val:   22.3   (传感器的值) )保存到redis

```bash
LPUSH iot:deviceId:10000001:sensorTag:co		 20.1  3.5  8.1  12.2 13.2 5.5  6.6

LPUSH iot:deviceId:10000001:sensorTag:co2		 22.1   19.2  20.2

LPUSH iot:deviceId:10000001:sensorTag:temp       28

LPUSH iot:deviceId:10000002:sensorTag:co2		 18.2
LPUSH iot:deviceId:10000002:sensorTag:so2		 19.2

LPUSH iot:deviceId:10000003:sensorTag:so2		 3.2

LPUSH iot:deviceId:10000004:sensorTag:humity     40
LPUSH iot:deviceId:10000004:sensorTag:temp     30.2

LPUSH iot:deviceId:10000005:sensorTag:humity     41
LPUSH iot:deviceId:10000005:sensorTag:temp     31.2
```

## 2.序列化

*注意，redis 存取的时候要加序列化，否则会有乱码问题*

![1667901844323](imgs/1667901844323.png)

![1668059930144](imgs/1668059930144.png)

# 四、服务端设计

## 1.传感器控制器设计

- 通过 Mybatis 获取传感器数据

- 通过Redis 获取传感器数据

## 2.设备控制器设计

- 添加设备
- 更新设备
- 删除设备
- 获取设备信息

# 五、测试

## 1. 分页查询指定设备下所有传感器的历史数据

## 2.根据设备ID和传感器ID ， 分页查询

## 3. 简单分页，查询指定设备下某传感器的历史数据

## 4. 简单获取指定设备下,所有传感器的最新数据

![1668061667686](imgs/1668061667686.png)

## 5. 添加设备

<img src="./imgs/1668062003654.png" alert=""  style="zoom:50%; display: flex; margin-left: 20px">
***用数据库查看--添加设备成功***

## 6.更新设备

***用数据库查看更新-->成功***

## 7.删除设备

## 8.查询设备

# 六 MQTT+MyBatis+Redis综合测试

发送 MQTT消息到指定主题中，各个设备如果订阅了此主题就可以收到此消息，

TODO: 做心跳连接

举例：

## 1.模拟硬件设备发送消息

发送消息到  iot:deviceId:10000006:calciumion   值为  6.1

![1668065140881](imgs/1668065140881.png)

## 2.查看数据库保存

##  3.查看Redis 缓存

![1668065688063](imgs/1668065688063.png)

## 4.使用查询传感器数据API ，简单查询

![1668065746502](imgs/1668065746502.png)

## 5.查询设备信息

17751758772/iot-mqtt

简介

发行版

贡献者

近期动态

17751758772/iot-mqtt .gitee-modal { width: 500px !important; }

简介

发行版

贡献者

近期动态

搜索帮助

17751758772/iot-mqtt