MQTT南向
生成点位表
1.接收前端参数
@PostMapping("/variableName")
public void VariableName(
@RequestPart("nameGateWay") String nameGateWay,
@RequestPart("communication")String communication,
@RequestPart("equipmentName") String equipmentName,
@RequestPart("file") MultipartFile file, HttpServletResponse response)
格式:multipart/form-data;
获取工作表:
Workbook sheets = WorkbookFactory.create(file.getInputStream());
2.生成点位表
service:
public List<String> variableExcel(VarVo varVo,Workbook file) {
List<String> list = new ArrayList<>();
// 获取excel的sheet页
Sheet sheet = file.getSheetAt(0);
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//转换成工具需要的实体类
VariableVo variableVo = new VariableVo();
variableVo.setNameGateWay(varVo.getNameGateWay());
variableVo.setCommunication(varVo.getCommunication());
variableVo.setEquipmentName(varVo.getEquipmentName());
//调用固定方法生成点位表实体类
List<Variable> variables = varUtil.varExcel(variableVo, file);
//调用方法生成点位表
String variable = excelUtil.makeVariable(variables);
list.add(variable);
return list;
}
pojo:
@Data
public class VariableVo {
//网关名称
private String nameGateWay;
//通信链接
private String communication;
//通信设备名称
private String equipmentName;
//设备区域
private String locations;
//设备名称
private List<String> name;
}
@Data
public class Variable {
//网关名称
private String nameGateWay;
//通信链接
private String communication;
//通信设备名称
private String equipmentName;
//变量名称
private String variableName;
//描述
private String describe;
//类型
private String type;
//初始值
private String initialValue;
//单位
private String company;
//状态
private String state;
//地址
private String address;
//记录趋势值
private String recordTrendValue;
//寄存器类型
private String registerType;
//寄存器数据类型
private String registerDataType;
//上限有效性
private String upperLimitValidity;
//下限有效性
private String lowerBoundValidity;
//增量有效性
private String incrementalEffectiveness;
//偏移量
private String offset;
}
VarUtil:
使用poi的jar包;
1,创建点位表对应的实体类集合,获取传入的X2B的网关获取的点位表,获取全部的行数
2,遍历每一行数据;
3,创建点位表每一行数据的实体类,将传进来的参数放进实体类里。
//网关名称
private String nameGateWay;
//通信链接
private String communication;
//通信设备名称
private String equipmentName;
4,根据X2B表中需要的数据,解析出来放进实体类:
变量名称:tcp_Channel_1_HBA716A_BI1
获取表中变量名称所在的位置的数据,转为字符串
Cell cell = row.getCell(1);
String variableName = getValue(cell).toString();
解析通过字符串的截取,获取我们想要的点位
获取第三个下划线后的字符串
String str1 = variableName.substring(variableName.indexOf("_",variableName.indexOf("_",variableName.indexOf("_") + 1) + 1)+1);
类型:
需要大写,将类型转为大写
Cell cell1 = row.getCell(4);
String type = getValue(cell1).toString();
if (type.equals("Boolean")){
variable.setType("BOOLEAN");
}else if (type.equals("Float")){
variable.setType("DOUBLE");
}
地址:
表中数据是整形,获取的字符串是浮点型,我们截取小数点前面的数值作为地址;
Cell cell2 = row.getCell(3);
String address = getValue(cell2).toString();
String substring = address.substring(0,address.indexOf("."));
variable.setAddress(substring);
描述:
直接放进实体类不需要处理
状态:
统一成 NORMAL
记录趋势值:
统一为 true
寄存器类型:
根据获取到的值的不同,转为相应的类型;
Cell cell5 = row.getCell(5);
String registerType = getValue(cell5).toString();
switch(registerType) {
case "1.0":
variable.setRegisterType("AO");
break;
case "0.0":
variable.setRegisterType("AI");
break;
case "3.0":
variable.setRegisterType("BI");
break;
case "4.0":
variable.setRegisterType("BO");
break;
default:
break;
}
寄存器数据类型:
需要大写,将类型转为大写
Cell cell6 = row.getCell(4);
String registerDataType = getValue(cell6).toString();
if (registerDataType.equals("Boolean")){
variable.setRegisterDataType("BOOLEAN");
}else if (registerDataType.equals("Float")){
variable.setRegisterDataType("FLOAT");
}
variables.add(variable);
其他数值为空,不做处理;
下面是完整的代码:
package com.grt.excel.util;
import com.grt.excel.pojo.Variable;
import com.grt.excel.pojo.VariableVo;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.springframework.stereotype.Component;
import java.util.ArrayList;
import java.util.List;
import static io.reactivex.internal.util.NotificationLite.getValue;
@Component
public class VarUtil {
public List<Variable> varExcel(VariableVo variableVo, Workbook file) {
// 获取excel的sheet页
Sheet sheet = file.getSheetAt(0);
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//创建一个点位表集合
List<Variable> variables = new ArrayList<>();
//遍历行
for (int i = 1; i < rows; i++) {
//将输入的信息放进实体类
Variable variable = new Variable();
variable.setNameGateWay(variableVo.getNameGateWay());
variable.setCommunication(variableVo.getCommunication());
variable.setEquipmentName(variableVo.getEquipmentName());
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
//获取到Excel文件中的所有的列
int cells = row.getPhysicalNumberOfCells();
//变量名称
Cell cell = row.getCell(1);
String variableName = getValue(cell).toString();
String str1 = variableName.substring(variableName.indexOf("_",variableName.indexOf("_",variableName.indexOf("_") + 1) + 1)+1);//包含本身位置
variable.setVariableName(str1);
//类型
Cell cell1 = row.getCell(4);
String type = getValue(cell1).toString();
if (type.equals("Boolean")){
variable.setType("BOOLEAN");
}else if (type.equals("Float")){
variable.setType("DOUBLE");
}
//地址
Cell cell2 = row.getCell(3);
String address = getValue(cell2).toString();
String substring = address.substring(0,address.indexOf("."));
variable.setAddress(substring);
//描述
Cell cell3 = row.getCell(7);
String describe = getValue(cell3).toString();
variable.setDescribe(describe);
//初始值
// Cell cell4 = row.getCell(5);
// String initialValue = getValue(cell4);
// variable.setInitialValue(initialValue);
//状态
variable.setState("NORMAL");
//记录趋势值
variable.setRecordTrendValue("true");
//寄存器类型
Cell cell5 = row.getCell(5);
String registerType = getValue(cell5).toString();
switch(registerType) {
case "1.0":
variable.setRegisterType("AO");
break;
case "0.0":
variable.setRegisterType("AI");
break;
case "3.0":
variable.setRegisterType("BI");
break;
case "4.0":
variable.setRegisterType("BO");
break;
default:
break;
}
//寄存器数据类型
Cell cell6 = row.getCell(4);
String registerDataType = getValue(cell6).toString();
if (registerDataType.equals("Boolean")){
variable.setRegisterDataType("BOOLEAN");
}else if (registerDataType.equals("Float")){
variable.setRegisterDataType("FLOAT");
}
variables.add(variable);
}
}
return variables;
}
}
调用生成点位表的工具:
传入点位表实体类的集合,遍历集合,依次将代表每一行的实体类放进表里,并生成;
package com.grt.excel.util;
import com.grt.excel.pojo.*;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import static io.reactivex.internal.util.NotificationLite.getValue;
//@author Z 张 P
// package com.grt.excel.util;
@Component
public class ExcelUtil {
//创建文件存放的路径,默认桌面
public String temp = "D:";
//描述设备名称去重
public ListName nameSet(Sheet sheet, List<String> name,Integer integer){
ListName listName = new ListName();
// 获取excel的sheet页
//一会提交用的集合
List<String> list = new ArrayList<>();
//获取到Excel文件中的所有行数
int rows = sheet.getPhysicalNumberOfRows();
//自动去重的集合
Set<String> set = new HashSet<String>();
//不去重的集合
List<String> list1 = new ArrayList<>();
Set<String> stringSet = new HashSet<>();
//遍历行
for (int i = 1; i < rows; i++) {
// 读取左上端单元格
Row row = sheet.getRow(i);
// 行不为空
if (row != null) {
//获取描述
Cell cell = row.getCell(integer);
String Description = getValue(cell).toString();
String substring = Description.substring(0,Description.indexOf("/"));
list1.add(substring);
stringSet.add(substring);
for (String s : name) {
if (s.equals(substring)){
set.add(substring);
}
}
}
}
//去重后的元素放进新的集合里
set.forEach(s -> list.add(s));
listName.setSet(list);
listName.setList(list1);
listName.setStringSet(stringSet);
return listName;
}
//创建表工具
public MakeXlsx makeXLSX(String[] heads) {
//创建工作簿
XSSFWorkbook hssfWorkbook = new XSSFWorkbook();
//创建工作表
XSSFSheet sheet = hssfWorkbook.createSheet();
hssfWorkbook.setSheetName(0, "Sheet1");
//创建表头
XSSFRow head = sheet.createRow(0);
for (int i = 0; i < heads.length; i++) {
XSSFCell cell = head.createCell(i);
cell.setCellValue(heads[i]);
}
MakeXlsx makeXlsx = new MakeXlsx();
makeXlsx.setSheet(sheet);
makeXlsx.setHead(head);
return makeXlsx;
}
//创建表工具
public String endMakeXLSX(XSSFWorkbook workbook,String s){
//创建临时文件的目录
File file = new File(temp);
if(!file.exists()){
file.mkdirs();
}
//临时文件路径/文件名
String downloadPath = file + "\\" + s + ".xlsx";
OutputStream outputStream = null;
try {
//使用FileOutputStream将内存中的数据写到本地,生成临时文件
outputStream = new FileOutputStream(downloadPath);
workbook.write(outputStream);
outputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
try {
if(outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//返回文件路径
return downloadPath;
}
//生成设备属性表
public String makeAttribute(List<Attribute> list){
String[] heads = {"设备区域","设备名称","属性名称","关联点位","描述","用途"};
MakeXlsx makeXlsx = makeXLSX(heads);
for (int i = 1;i <= list.size();i++) {
Attribute attribute = list.get(i - 1);
//创建行,从第二行开始,所以for循环的i从1开始取
XSSFRow row = makeXlsx.getSheet().createRow(i);
//创建单元格,并填充数据
XSSFCell cell = row.createCell(0);
cell.setCellValue(attribute.getLocation());
cell = row.createCell(1);
cell.setCellValue(attribute.getEquipmentName());
cell = row.createCell(2);
cell.setCellValue(attribute.getName());
cell = row.createCell(3);
cell.setCellValue(attribute.getVariableName());
cell = row.createCell(4);
cell.setCellValue(attribute.getDescribe());
cell = row.createCell(5);
cell.setCellValue(attribute.getPurpose());
}
XSSFWorkbook workbook = makeXlsx.getSheet().getWorkbook();
String top = "设备属性表";
return endMakeXLSX(workbook, top);
}
//生成设备表
public String makeEquipment(List<Equipment> list){
String[] heads = {"设备区域","设备名称","设备分类","设备供应商","供应商电话","设备维保商","维保商电话","设备寿命","采购价格","安装时间","维保时间","设备图片"};
MakeXlsx makeXlsx = makeXLSX(heads);
for (int i = 1;i <= list.size();i++) {
Equipment equipment = list.get(i - 1);
//创建行,从第二行开始,所以for循环的i从1开始取
XSSFRow row = makeXlsx.getSheet().createRow(i);
//创建单元格,并填充数据
XSSFCell cell = row.createCell(0);
cell.setCellValue(equipment.getLocation());
cell = row.createCell(1);
cell.setCellValue(equipment.getName());
cell = row.createCell(7);
cell.setCellValue(equipment.getLife_span());
cell = row.createCell(8);
cell.setCellValue(equipment.getPurchase_price());
}
XSSFWorkbook workbook = makeXlsx.getSheet().getWorkbook();
String top = "设备表";
return endMakeXLSX(workbook,top);
}
//生成点位表
public String makeVariable(List<Variable> list) {
String[] heads = {"网关名称","通信连接","通信设备名称","变量名称","描述","类型","初始值","单位","状态","地址","记录趋势值","寄存器类型","寄存器数据类型","上限有效性","下限有效性","增量有效性","偏移量"};
MakeXlsx makeXlsx = makeXLSX(heads);
for (int i = 1;i <= list.size();i++) {
Variable variable = list.get(i - 1);
//创建行,从第二行开始,所以for循环的i从1开始取
XSSFRow row = makeXlsx.getSheet().createRow(i);
//创建单元格,并填充数据
XSSFCell cell = row.createCell(0);
cell.setCellValue(variable.getNameGateWay());
cell = row.createCell(1);
cell.setCellValue(variable.getCommunication());
cell = row.createCell(2);
cell.setCellValue(variable.getEquipmentName());
cell = row.createCell(3);
cell.setCellValue(variable.getVariableName());
cell = row.createCell(4);
cell.setCellValue(variable.getDescribe());
cell = row.createCell(5);
cell.setCellValue(variable.getType());
cell = row.createCell(6);
cell.setCellValue(variable.getInitialValue());
cell = row.createCell(7);
cell.setCellValue(variable.getCompany());
cell = row.createCell(8);
cell.setCellValue(variable.getState());
cell = row.createCell(9);
cell.setCellValue(variable.getAddress());
cell = row.createCell(10);
cell.setCellValue(variable.getRecordTrendValue());
cell = row.createCell(11);
cell.setCellValue(variable.getRegisterType());
cell = row.createCell(12);
cell.setCellValue(variable.getRegisterDataType());
cell = row.createCell(14);
cell.setCellValue(variable.getUpperLimitValidity());
cell = row.createCell(15);
cell.setCellValue(variable.getLowerBoundValidity());
cell = row.createCell(16);
cell.setCellValue(variable.getIncrementalEffectiveness());
cell = row.createCell(17);
cell.setCellValue(variable.getOffset());
}
XSSFWorkbook workbook = makeXlsx.getSheet().getWorkbook();
String top = "点位表";
return endMakeXLSX(workbook,top);
}
}
3.文件下载
downloadExcel.exportStu(response, "点位表.xlsx", excallName.get(0));
根据工具类,传入参数下载的文件名称,当前文件地址。提供下载
下载文件工具类:
package com.grt.excel.util;
import org.springframework.stereotype.Component;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
//@author Z 张 P
// package com.grt.excel.util;
@Component
public class DownloadExcel {
public void exportStu(HttpServletResponse response, String name,String downloadPath){
//设置默认的下载文件名
try {
//避免文件名中文乱码,将UTF8打散重组成ISO-8859-1编码方式
name = new String (name.getBytes("UTF8"),"ISO-8859-1");
} catch (UnsupportedEncodingException e) {
e.printStackTrace();
}
//设置响应头的类型
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
//让浏览器下载文件,name是上述默认文件下载名
response.addHeader("Content-Disposition","attachment;filename=\"" + name + "\"");
InputStream inputStream=null;
OutputStream outputStream=null;
//在service层中已经将数据存成了excel临时文件,并返回了临时文件的路径
//根据临时文件的路径创建File对象,FileInputStream读取时需要使用
File file = new File(downloadPath);
try {
//通过FileInputStream读临时文件,ServletOutputStream将临时文件写给浏览器
inputStream = new FileInputStream(file);
outputStream = response.getOutputStream();
int len = -1;
byte[] b = new byte[1024];
while((len = inputStream.read(b)) != -1){
outputStream.write(b);
}
//刷新
outputStream.flush();
} catch (Exception e) {
e.printStackTrace();
} finally {
//关闭输入输出流
try {
if(inputStream != null) {
inputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
try {
if(outputStream != null) {
outputStream.close();
}
} catch (IOException e) {
e.printStackTrace();
}
}
//最后才能,删除临时文件,如果流在使用临时文件,file.delete()是删除不了的
file.delete();
}
}
InfluxDB使用教程:Linux常用操作和命令
InfluxDB的在Linux下的常用的基础操作。
启动:
sudo service influxdb start
重启:
service influxdb restart
2.在Linux中如何使用:
进入influxDB,输入命令:
influx
查看InfluxDB状态:
SHOW STATS
创建一个数据库:
create database "db_name"
显示所有的数据库
show databases
删除数据库
注:不区分大小写,会删除掉所有大小写不同,但名字相同的库
drop database "db_name"
使用数据库
use db_name
查看该数据库下所有表
show measurements
创建表
注:直接在插入数据的时候指定表名,表自动创建,字段类型由传入的值决定。
insert test,host=127.0.0.1,monitor_name=test count=1
删除表
drop measurement measurement_name
查询表
select * from database limit 10
添加条件:
注:条件中的字符串需要用单引号包裹
SELECT field1,field2 FROM "tableName" WHERE time > '2018-08-15T02:29:20Z' AND time < '2018-08-15T02:30:20Z' Order by time DESC
查看series
show series from weather
创建保存策略:
注:保存策略即存储在数据库中的数据,多长时间删除一次。
create retention policy "2_hours" on test_db duration 2h replication 1 default
解释:在数据test_db,添加了一个名字叫做 2_hours,duration为2小时,副本为1,并将其设置为默认策略。采用默认策略的表,将会执行相关配置。
修改完默认策略后,所有不是以“2_hours"为保留策略的表,都将不能直接查询,需要在表名measurement前,加上策略名,如:
select * from "defalut".weather
查看保存策略
show retention policies on test_db(数据库名)
修改保留策略:
ALTER retention policy "default" on test_db DEFAULT(修改默认)
或者在关键字上加引号也可以:
ALTER retention policy "default" on "test_db" duration 719h
删除保留策略:
drop retention policy xxxName on test_db
3.在浏览器中如何使用
InfluxDB默认是通过Http请求访问数据库的,也支持UDP协议(默认关闭),所以可以通过浏览器直接查询或新增删除数据。
格式:数据库地址 + 端口 + query?db = 数据库名&q = 查询或删除或插入的SQL语句
1
举例:
http://10.10.1.2:8086/query?db=testdb&q=select * from measurement limit 10
emqx 常用命令
命令 | 作用 |
---|---|
emqx start | 启动emqtt |
emqx stop | 关闭emqtt |
emqx restart | 重启emqtt |
emqx_ctl status | 查看emqtt状态 |