From 5d901b5e3f033e8b04e53420d68626cbd87431c8 Mon Sep 17 00:00:00 2001 From: 648540858 <648540858@qq.com> Date: 星期五, 06 五月 2022 10:12:34 +0800 Subject: [PATCH] 使用阿里代码规范。规范代码写法 --- src/main/java/com/genersoft/iot/vmp/storager/dao/DeviceChannelMapper.java | 165 +++++++++++++++++++++++++++++++++++++++--------------- 1 files changed, 119 insertions(+), 46 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 28d63c3..312d3e0 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 @@ -1,6 +1,7 @@ package com.genersoft.iot.vmp.storager.dao; import com.genersoft.iot.vmp.gb28181.bean.DeviceChannel; +import com.genersoft.iot.vmp.gb28181.bean.DeviceChannelInPlatform; import com.genersoft.iot.vmp.vmanager.gb28181.platform.bean.ChannelReduce; import org.apache.ibatis.annotations.*; import org.springframework.stereotype.Repository; @@ -54,22 +55,21 @@ int update(DeviceChannel channel); @Select(value = {" <script>" + - "SELECT * FROM ( "+ - " SELECT * , (SELECT count(0) FROM device_channel WHERE parentId=dc.channelId) as subCount FROM device_channel dc " + - " WHERE dc.deviceId=#{deviceId} " + + "SELECT " + + "dc.* " + + "from " + + "device_channel dc " + + "WHERE " + + "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>) dcr" + - " WHERE 1=1 " + - " <if test='hasSubChannel == true' > AND subCount >0</if>" + - " <if test='hasSubChannel == false' > AND subCount=0</if>" + - " ORDER BY channelId ASC" + + " <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); @@ -86,28 +86,44 @@ @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.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 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); @Select(value = {" <script>" + - "SELECT * FROM ( "+ - " SELECT dc.channelId, dc.deviceId, dc.name, de.manufacturer, de.hostAddress, " + - "(SELECT count(0) FROM device_channel WHERE parentId=dc.channelId) as subCount, " + - "(SELECT pc.platformId FROM platform_gb_channel pc WHERE pc.deviceId=dc.deviceId AND pc.channelId = dc.channelId ) as platformId " + - "FROM device_channel dc " + - "LEFT JOIN device de ON dc.deviceId = de.deviceId " + - " 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> " + - ") dcr" + - " WHERE 1=1 " + - " <if test='hasSubChannel!= null and hasSubChannel == true' > AND subCount >0</if> " + - " <if test='hasSubChannel!= null and hasSubChannel == false' > AND subCount=0</if> " + - " <if test='platformId != null and inPlatform == true ' > AND platformId='${platformId}'</if> " + - " <if test='platformId != null and inPlatform == false ' > AND (platformId != '${platformId}' OR platformId is NULL ) </if> " + - " ORDER BY deviceId, channelId ASC" + + "SELECT " + + " dc.*,\n" + + " pgc.platformId as platformId,\n" + + " pgc.catalogId as catalogId " + + " FROM device_channel dc " + + " LEFT JOIN platform_gb_channel pgc on pgc.deviceChannelId = dc.id " + + " WHERE pgc.platformId = #{platformId} " + + " ORDER BY dc.deviceId, dc.channelId ASC" + " </script>"}) + List<DeviceChannelInPlatform> queryChannelByPlatformId(String platformId); - List<ChannelReduce> queryChannelListInAll(String query, Boolean online, Boolean hasSubChannel, String platformId, Boolean inPlatform); @Select("SELECT * FROM device_channel WHERE channelId=#{channelId}") List<DeviceChannel> queryChannelByChannelId( String channelId); @@ -120,20 +136,46 @@ @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}, " + "'${item.streamId}', ${item.longitude}, ${item.latitude},'${item.createTime}', '${item.updateTime}')" + "</foreach> " + + "ON DUPLICATE KEY UPDATE " + + "updateTime=VALUES(updateTime), " + + "name=VALUES(name), " + + "manufacture=VALUES(manufacture), " + + "model=VALUES(model), " + + "owner=VALUES(owner), " + + "civilCode=VALUES(civilCode), " + + "block=VALUES(block), " + + "subCount=VALUES(subCount), " + + "address=VALUES(address), " + + "parental=VALUES(parental), " + + "parentId=VALUES(parentId), " + + "safetyWay=VALUES(safetyWay), " + + "registerWay=VALUES(registerWay), " + + "certNum=VALUES(certNum), " + + "certifiable=VALUES(certifiable), " + + "errCode=VALUES(errCode), " + + "secrecy=VALUES(secrecy), " + + "ipAddress=VALUES(ipAddress), " + + "port=VALUES(port), " + + "password=VALUES(password), " + + "PTZType=VALUES(PTZType), " + + "status=VALUES(status), " + + "streamId=VALUES(streamId), " + + "longitude=VALUES(longitude), " + + "latitude=VALUES(latitude)" + "</script>") - void batchAdd(List<DeviceChannel> addChannels); + int batchAdd(List<DeviceChannel> addChannels); @Update({"<script>" + "<foreach collection='updateChannels' item='item' separator=';'>" + @@ -146,6 +188,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>" + @@ -167,21 +210,51 @@ "WHERE deviceId=#{item.deviceId} AND channelId=#{item.channelId}"+ "</foreach>" + "</script>"}) - void batchUpdate(List<DeviceChannel> updateChannels); + int batchUpdate(List<DeviceChannel> updateChannels); + @Select(value = {" <script>" + - "SELECT * FROM ( "+ - " SELECT * , (SELECT count(0) FROM device_channel WHERE parentId=dc.channelId) as subCount FROM device_channel dc " + - " WHERE 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>) dcr" + - " WHERE 1=1 " + - " <if test='hasSubChannel == true' > AND subCount >0</if>" + - " <if test='hasSubChannel == false' > AND subCount=0</if>" + - " ORDER BY channelId ASC" + - " LIMIT #{limit} OFFSET #{start}" + + "SELECT " + + "dc1.* " + + "from " + + "device_channel dc1 " + + "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 dc1.subCount >0</if>" + + " <if test='hasSubChannel == false' > AND dc1.subCount=0</if>" + + "ORDER BY dc1.channelId ASC " + + "Limit #{limit} OFFSET #{start}" + " </script>"}) - List<DeviceChannel> queryChannelsByDeviceIdWithStartAndLimit(String deviceId, String parentChannelId, String query, Boolean hasSubChannel, Boolean online, int start, int limit); + List<DeviceChannel> queryChannelsByDeviceIdWithStartAndLimit(String deviceId, String parentChannelId, String query, + Boolean hasSubChannel, Boolean online, int start, int limit); + + @Select("SELECT * FROM device_channel WHERE deviceId=#{deviceId} AND status=1") + List<DeviceChannel> queryOnlineChannelsByDeviceId(String deviceId); + + @Delete(value = {" <script>" + + "DELETE " + + "from " + + "device_channel " + + "WHERE " + + "deviceId = #{deviceId} " + + " AND channelId NOT IN " + + "<foreach collection='channels' item='item' open='(' separator=',' close=')' > #{item.channelId}</foreach>" + + " </script>"}) + int cleanChannelsNotInList(String deviceId, List<DeviceChannel> channels); + + @Update(" update device_channel" + + " set subCount = (select *" + + " from (select count(0)" + + " from device_channel" + + " where deviceId = #{deviceId} and parentId = #{channelId}) as temp)" + + " where deviceId = #{deviceId} " + + " and channelId = #{channelId}") + int updateChannelSubCount(String deviceId, String channelId); + + @Update(value = {"UPDATE device_channel SET latitude=${latitude}, longitude=${longitude} WHERE deviceId=#{deviceId} AND channelId=#{channelId}"}) + void updatePotion(String deviceId, String channelId, double longitude, double latitude); } -- Gitblit v1.8.0