From e94b99d11c46246532edc93cd25cbf8c0b88f03f Mon Sep 17 00:00:00 2001
From: 648540858 <648540858@qq.com>
Date: 星期日, 27 二月 2022 20:01:31 +0800
Subject: [PATCH] 实现国标录像级联播放，优化点播流程，加快点播速度
---
src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java | 78 +++++++++++++++++----------------------
1 files changed, 34 insertions(+), 44 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..d4cace4 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);
@@ -92,29 +86,28 @@
@Update(value = {"UPDATE device_channel SET streamId=#{streamId} WHERE deviceId=#{deviceId} AND channelId=#{channelId}"})
void startPlay(String deviceId, String channelId, String streamId);
-
-
@Select(value = {" <script>" +
- "SELECT dc.channelId, "+
- "dc.deviceId, " +
- "dc.name, " +
- "de.manufacturer, " +
- "de.hostAddress, " +
- "(SELECT count(0) FROM device_channel WHERE parentId = dc.channelId) as subCount, " +
- "pgc.platformId as platformId, " +
- "pgc.catalogId as catalogId " +
- "FROM device_channel dc " +
- "LEFT JOIN device de ON dc.deviceId = de.deviceId " +
- "LEFT JOIN platform_gb_channel pgc on de.deviceId = pgc.deviceId and pgc.channelId = dc.channelId " +
- "LEFT JOIN device_channel dc2 ON dc2.deviceId = de.deviceId AND dc2.parentId = dc.channelId " +
+ "SELECT " +
+ " dc.id,\n" +
+ " dc.channelId,\n" +
+ " dc.deviceId,\n" +
+ " dc.name,\n" +
+ " de.manufacturer,\n" +
+ " de.hostAddress,\n" +
+ " dc.subCount,\n" +
+ " pgc.platformId as platformId,\n" +
+ " pgc.catalogId as catalogId " +
+ " FROM device_channel dc " +
+ " LEFT JOIN device de ON dc.deviceId = de.deviceId " +
+ " LEFT JOIN platform_gb_channel pgc on pgc.deviceChannelId = dc.id " +
" WHERE 1=1 " +
" <if test='query != null'> AND (dc.channelId LIKE '%${query}%' OR dc.name LIKE '%${query}%' OR dc.name LIKE '%${query}%')</if> " +
" <if test='online == true' > AND dc.status=1</if> " +
" <if test='online == false' > AND dc.status=0</if> " +
- " <if test='hasSubChannel!= null and hasSubChannel == true' > AND dc2.channelId is not null</if> " +
- " <if test='hasSubChannel!= null and hasSubChannel == false' > AND dc2.channelId is null</if> " +
- " <if test='catalogId == null ' > AND pgc.platformId is null AND pgc.catalogId is null</if> " +
- " <if test='catalogId != null ' > AND pgc.platformId =#{platformId} AND pgc.catalogId = #{catalogId}</if> " +
+ " <if test='hasSubChannel!= null and hasSubChannel == true' > AND dc.subCount > 0</if> " +
+ " <if test='hasSubChannel!= null and hasSubChannel == false' > AND dc.subCount == 0</if> " +
+ " <if test='catalogId == null ' > AND dc.id not in (select deviceChannelId from platform_gb_channel where platformId=#{platformId} ) </if> " +
+ " <if test='catalogId != null ' > AND pgc.platformId = #{platformId} and pgc.catalogId=#{catalogId} </if> " +
" ORDER BY dc.deviceId, dc.channelId ASC" +
" </script>"})
List<ChannelReduce> queryChannelListInAll(String query, Boolean online, Boolean hasSubChannel, String platformId, String catalogId);
@@ -130,13 +123,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 +149,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 +176,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>"})
@@ -207,8 +197,8 @@
List<DeviceChannel> queryOnlineChannelsByDeviceId(String deviceId);
@Select(" SELECT\n" +
+ " id,\n" +
" channelId,\n" +
- " channelId as id,\n" +
" deviceId,\n" +
" parentId,\n" +
" status,\n" +
--
Gitblit v1.8.0