From 5bcd8495e03b08ff417f74f816d20e028f9b1fe0 Mon Sep 17 00:00:00 2001
From: 648540858 <648540858@qq.com>
Date: 星期日, 20 二月 2022 16:22:43 +0800
Subject: [PATCH] 兼容海康不规范的xml，大幅度提高通道分页查询速度，优化节点的保活。
---
src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java | 43 +++++++++++++++++--------------------------
1 files changed, 17 insertions(+), 26 deletions(-)
diff --git a/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java b/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java
index 32dd7df..9de19df 100644
--- a/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java
+++ b/src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java
@@ -56,26 +56,20 @@
@Select(value = {" <script>" +
"SELECT " +
- "dc1.*, " +
- "COUNT(dc2.channelId) as subCount " +
+ "dc.* " +
"from " +
- "device_channel dc1 " +
- "left join device_channel dc2 on " +
- "dc1.channelId = dc2.parentId " +
+ "device_channel dc " +
"WHERE " +
- "dc1.deviceId = #{deviceId} " +
- " <if test='query != null'> AND (dc1.channelId LIKE '%${query}%' OR dc1.name LIKE '%${query}%' OR dc1.name LIKE '%${query}%')</if> " +
- " <if test='parentChannelId != null'> AND dc1.parentId=#{parentChannelId} </if> " +
- " <if test='online == true' > AND dc1.status=1</if>" +
- " <if test='online == false' > AND dc1.status=0</if>" +
- " <if test='hasSubChannel == true' > AND subCount >0</if>" +
- " <if test='hasSubChannel == false' > AND subCount=0</if>" +
- "GROUP BY dc1.channelId " +
+ "dc.deviceId = #{deviceId} " +
+ " <if test='query != null'> AND (dc.channelId LIKE '%${query}%' OR dc.name LIKE '%${query}%' OR dc.name LIKE '%${query}%')</if> " +
+ " <if test='parentChannelId != null'> AND dc.parentId=#{parentChannelId} </if> " +
+ " <if test='online == true' > AND dc.status=1</if>" +
+ " <if test='online == false' > AND dc.status=0</if>" +
+ " <if test='hasSubChannel == true' > AND dc.subCount > 0 </if>" +
+ " <if test='hasSubChannel == false' > AND dc.subCount = 0 </if>" +
+ "GROUP BY dc.channelId " +
" </script>"})
List<DeviceChannel> queryChannels(String deviceId, String parentChannelId, String query, Boolean hasSubChannel, Boolean online);
-
- @Select("SELECT * FROM device_channel WHERE deviceId=#{deviceId}")
- List<DeviceChannel> queryChannelsByDeviceId(String deviceId);
@Select("SELECT * FROM device_channel WHERE deviceId=#{deviceId} AND channelId=#{channelId}")
DeviceChannel queryChannel(String deviceId, String channelId);
@@ -100,7 +94,7 @@
"dc.name, " +
"de.manufacturer, " +
"de.hostAddress, " +
- "(SELECT count(0) FROM device_channel WHERE parentId = dc.channelId) as subCount, " +
+ "dc.subCount, " +
"pgc.platformId as platformId, " +
"pgc.catalogId as catalogId " +
"FROM device_channel dc " +
@@ -130,13 +124,13 @@
@Insert("<script> " +
"insert into device_channel " +
- "(channelId, deviceId, name, manufacture, model, owner, civilCode, block, " +
+ "(channelId, deviceId, name, manufacture, model, owner, civilCode, block, subCount, " +
" address, parental, parentId, safetyWay, registerWay, certNum, certifiable, errCode, secrecy, " +
" ipAddress, port, password, PTZType, status, streamId, longitude, latitude, createTime, updateTime) " +
"values " +
"<foreach collection='addChannels' index='index' item='item' separator=','> " +
"('${item.channelId}', '${item.deviceId}', '${item.name}', '${item.manufacture}', '${item.model}', " +
- "'${item.owner}', '${item.civilCode}', '${item.block}'," +
+ "'${item.owner}', '${item.civilCode}', '${item.block}',${item.subCount}," +
"'${item.address}', ${item.parental}, '${item.parentId}', ${item.safetyWay}, ${item.registerWay}, " +
"'${item.certNum}', ${item.certifiable}, ${item.errCode}, '${item.secrecy}', " +
"'${item.ipAddress}', ${item.port}, '${item.password}', ${item.PTZType}, ${item.status}, " +
@@ -156,6 +150,7 @@
"<if test='item.owner != null'>, owner='${item.owner}'</if>" +
"<if test='item.civilCode != null'>, civilCode='${item.civilCode}'</if>" +
"<if test='item.block != null'>, block='${item.block}'</if>" +
+ "<if test='item.subCount != null'>, block=${item.subCount}</if>" +
"<if test='item.address != null'>, address='${item.address}'</if>" +
"<if test='item.parental != null'>, parental=${item.parental}</if>" +
"<if test='item.parentId != null'>, parentId='${item.parentId}'</if>" +
@@ -182,21 +177,17 @@
@Select(value = {" <script>" +
"SELECT " +
- "dc1.*, " +
- "COUNT(dc2.channelId) as subCount " +
+ "dc1.* " +
"from " +
"device_channel dc1 " +
- "left join device_channel dc2 on " +
- "dc1.channelId = dc2.parentId " +
"WHERE " +
"dc1.deviceId = #{deviceId} " +
" <if test='query != null'> AND (dc1.channelId LIKE '%${query}%' OR dc1.name LIKE '%${query}%' OR dc1.name LIKE '%${query}%')</if> " +
" <if test='parentChannelId != null'> AND dc1.parentId=#{parentChannelId} </if> " +
" <if test='online == true' > AND dc1.status=1</if>" +
" <if test='online == false' > AND dc1.status=0</if>" +
- " <if test='hasSubChannel == true' > AND subCount >0</if>" +
- " <if test='hasSubChannel == false' > AND subCount=0</if>" +
- "GROUP BY dc1.channelId " +
+ " <if test='hasSubChannel == true' > AND dc1.subCount >0</if>" +
+ " <if test='hasSubChannel == false' > AND dc1.subCount=0</if>" +
"ORDER BY dc1.channelId ASC " +
"Limit #{limit} OFFSET #{start}" +
" </script>"})
--
Gitblit v1.8.0