• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
1 /*
2  * Copyright (c) 2021 Huawei Device Co., Ltd.
3  * Licensed under the Apache License, Version 2.0 (the "License");
4  * you may not use this file except in compliance with the License.
5  * You may obtain a copy of the License at
6  *
7  *     http://www.apache.org/licenses/LICENSE-2.0
8  *
9  * Unless required by applicable law or agreed to in writing, software
10  * distributed under the License is distributed on an "AS IS" BASIS,
11  * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
12  * See the License for the specific language governing permissions and
13  * limitations under the License.
14  */
15 
16 package ohos.devtools.datasources.utils.device.dao;
17 
18 import ohos.devtools.datasources.databases.databasepool.AbstractDataStore;
19 import ohos.devtools.datasources.utils.common.util.CloseResourceUtil;
20 import ohos.devtools.datasources.utils.device.entity.DeviceIPPortInfo;
21 import ohos.devtools.datasources.utils.profilerlog.ProfilerLogManager;
22 import org.apache.commons.lang.StringUtils;
23 import org.apache.logging.log4j.LogManager;
24 import org.apache.logging.log4j.Logger;
25 
26 import java.sql.Connection;
27 import java.sql.PreparedStatement;
28 import java.sql.ResultSet;
29 import java.sql.SQLException;
30 import java.sql.Statement;
31 import java.util.ArrayList;
32 import java.util.List;
33 import java.util.Optional;
34 
35 import static ohos.devtools.datasources.utils.device.entity.DeviceType.FULL_HOS_DEVICE;
36 import static ohos.devtools.datasources.utils.device.entity.DeviceType.LEAN_HOS_DEVICE;
37 
38 /**
39  * Device-related execution sql class
40  *
41  * @since 2021/5/19 16:39
42  */
43 public class DeviceDao extends AbstractDataStore {
44     private static final Logger LOGGER = LogManager.getLogger(DeviceDao.class);
45     private static final String DEVICE_TABLE = "DeviceIPPortInfo";
46 
47     /**
48      * insertDeviceIPPortInfo
49      *
50      * @param info info
51      */
insertDeviceIPPortInfo(DeviceIPPortInfo info)52     public void insertDeviceIPPortInfo(DeviceIPPortInfo info) {
53         if (ProfilerLogManager.isInfoEnabled()) {
54             LOGGER.info("insertDeviceIPPortInfo");
55         }
56         Connection conn = null;
57         PreparedStatement ps = null;
58         try {
59             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
60             if (optionalConnection.isPresent()) {
61                 conn = optionalConnection.get();
62                 String sql = "insert into "
63                         + "DeviceIPPortInfo ("
64                         + "deviceID,"
65                         + "deviceName,"
66                         + "ip,"
67                         + "deviceType,"
68                         + "connectType,"
69                         + "deviceStatus,"
70                         + "retryNum,"
71                         + "port,"
72                         + "forwardPort) "
73                         + "values "
74                         + "(?,?,?,?,?,?,?,?,?)";
75                 ps = conn.prepareStatement(sql);
76                 ps.setString(1, info.getDeviceID());
77                 ps.setString(2, info.getDeviceName());
78                 ps.setString(3, info.getIp());
79                 ps.setString(4, info.getDeviceType().getCpuAbi());
80                 ps.setString(5, info.getConnectType());
81                 ps.setInt(6, info.getDeviceStatus());
82                 ps.setInt(7, info.getRetryNum());
83                 ps.setInt(8, info.getPort());
84                 ps.setInt(9, info.getForwardPort());
85                 ps.executeUpdate();
86             }
87         } catch (SQLException sqlException) {
88             if (ProfilerLogManager.isErrorEnabled()) {
89                 LOGGER.error("sql Exception ", sqlException.getMessage());
90             }
91         } finally {
92             CloseResourceUtil.closeResource(LOGGER, conn, ps, null);
93         }
94     }
95 
96     /**
97      * deleteExceptDeviceIPPort
98      *
99      * @param list List<DeviceIPPortInfo>
100      * @return List <DeviceIPPortInfo>
101      */
selectOfflineDevice(List<DeviceIPPortInfo> list)102     public List<DeviceIPPortInfo> selectOfflineDevice(List<DeviceIPPortInfo> list) {
103         if (ProfilerLogManager.isInfoEnabled()) {
104             LOGGER.info("selectOfflineDevice");
105         }
106         StringBuilder str = new StringBuilder();
107         for (DeviceIPPortInfo info : list) {
108             str.append(" '").append(info.getDeviceID()).append("',");
109         }
110         String selectSql;
111         if (StringUtils.isNotBlank(str.toString())) {
112             str = new StringBuilder(str.substring(0, str.length() - 1));
113             selectSql = "select "
114                     + "*  "
115                     + "from "
116                     + "DeviceIPPortInfo "
117                     + "where "
118                     + "deviceID "
119                     + "not in "
120                     + "(" + str + ");";
121         } else {
122             selectSql = "select "
123                     + "*  "
124                     + "from "
125                     + "DeviceIPPortInfo";
126         }
127         PreparedStatement statement = null;
128         Connection connection = null;
129         ResultSet rs = null;
130         List<DeviceIPPortInfo> deviceIPPortInfoList = new ArrayList<>();
131         try {
132             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
133             if (optionalConnection.isPresent()) {
134                 connection = optionalConnection.get();
135                 statement = connection.prepareStatement(selectSql);
136                 rs = statement.executeQuery();
137                 while (rs.next()) {
138                     addDeviceIPPortInfoList(deviceIPPortInfoList, rs);
139                 }
140             }
141         } catch (SQLException sqlException) {
142             if (ProfilerLogManager.isErrorEnabled()) {
143                 LOGGER.error("sql Exception ", sqlException);
144             }
145         } finally {
146             close(statement, rs, connection);
147         }
148         return deviceIPPortInfoList;
149     }
150 
151     /**
152      * addDeviceIPPortInfoList
153      *
154      * @param deviceIPPortInfoList List<DeviceIPPortInfo>
155      * @param rs rs
156      * @throws SQLException SQLException
157      */
addDeviceIPPortInfoList(List<DeviceIPPortInfo> deviceIPPortInfoList, ResultSet rs)158     private void addDeviceIPPortInfoList(List<DeviceIPPortInfo> deviceIPPortInfoList, ResultSet rs)
159         throws SQLException {
160         if (ProfilerLogManager.isInfoEnabled()) {
161             LOGGER.info("addDeviceIPPortInfoList");
162         }
163         DeviceIPPortInfo deviceInfo = new DeviceIPPortInfo();
164         String deviceID = rs.getString("deviceID");
165         String deviceName = rs.getString("deviceName");
166         String ip = rs.getString("ip");
167         String deviceType = rs.getString("deviceType");
168         deviceInfo.setDeviceID(deviceID);
169         deviceInfo.setDeviceName(deviceName);
170         deviceInfo.setIp(ip);
171         if (deviceType.equals(FULL_HOS_DEVICE.getCpuAbi())) {
172             deviceInfo.setDeviceType(FULL_HOS_DEVICE);
173         } else {
174             deviceInfo.setDeviceType(LEAN_HOS_DEVICE);
175         }
176         String connectType = rs.getString("connectType");
177         deviceInfo.setConnectType(connectType);
178         int deviceStatus = rs.getInt("deviceStatus");
179         deviceInfo.setDeviceStatus(deviceStatus);
180         int retryNum = rs.getInt("retryNum");
181         deviceInfo.setRetryNum(retryNum);
182         int port = rs.getInt("port");
183         deviceInfo.setPort(port);
184         int forwardPort = rs.getInt("forwardPort");
185         deviceInfo.setOfflineCount(rs.getInt("offlineCount"));
186         deviceInfo.setForwardPort(forwardPort);
187         deviceIPPortInfoList.add(deviceInfo);
188     }
189 
190     /**
191      * deleteExceptDeviceIPPort
192      *
193      * @param deviceIPPortInfo List<DeviceIPPortInfo>
194      */
deleteOfflineDeviceIPPort(DeviceIPPortInfo deviceIPPortInfo)195     public void deleteOfflineDeviceIPPort(DeviceIPPortInfo deviceIPPortInfo) {
196         if (ProfilerLogManager.isInfoEnabled()) {
197             LOGGER.info("deleteOfflineDeviceIPPort");
198         }
199         Optional<Connection> connection = getConnectByTable(DEVICE_TABLE);
200         if (connection.isPresent()) {
201             Connection conn = connection.get();
202             String delSql = "delete from "
203                     + "DeviceIPPortInfo "
204                     + "where "
205                     + "deviceID = "
206                     + "'" + deviceIPPortInfo.getDeviceID() + "'";
207             if (ProfilerLogManager.isInfoEnabled()) {
208                 LOGGER.info("deleteExceptDeviceIPPort = {}", delSql);
209             }
210             execute(conn, delSql);
211         }
212     }
213 
214     /**
215      * updateDeviceIPPortInfo
216      *
217      * @param offlineCount offlineCount
218      * @param deviceId deviceId
219      * @return boolean boolean
220      */
updateDeviceIPPortInfo(int offlineCount, String deviceId)221     public boolean updateDeviceIPPortInfo(int offlineCount, String deviceId) {
222         if (ProfilerLogManager.isInfoEnabled()) {
223             LOGGER.info("updateDeviceIPPortInfo");
224         }
225         StringBuilder sql = new StringBuilder("update DeviceIPPortInfo set ");
226         if (offlineCount >= 0) {
227             sql.append("offlineCount = ").append(offlineCount);
228         }
229         sql.append(" where deviceID = '").append(deviceId).append("'");
230         Statement statement = null;
231         Connection conn = null;
232         try {
233             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
234             if (optionalConnection.isPresent()) {
235                 conn = optionalConnection.get();
236                 statement = conn.createStatement();
237                 int executeUpdate = statement.executeUpdate(sql.toString());
238                 return executeUpdate > 0;
239             }
240         } catch (SQLException sqlException) {
241             if (ProfilerLogManager.isErrorEnabled()) {
242                 LOGGER.error("sql Exception ", sqlException.getMessage());
243             }
244             return false;
245         } finally {
246             close(statement, conn);
247         }
248         return false;
249     }
250 
251     /**
252      * deleteExceptDeviceIPPort
253      *
254      * @param list list
255      */
deleteExceptDeviceIPPort(List<DeviceIPPortInfo> list)256     public void deleteExceptDeviceIPPort(List<DeviceIPPortInfo> list) {
257         if (ProfilerLogManager.isInfoEnabled()) {
258             LOGGER.info("deleteExceptDeviceIPPort");
259         }
260         StringBuilder str = new StringBuilder();
261         Optional<Connection> deviceIPPort = getConnectByTable(DEVICE_TABLE);
262         if (deviceIPPort.isPresent()) {
263             Connection conn = deviceIPPort.get();
264             for (DeviceIPPortInfo info : list) {
265                 str.append(" '").append(info.getDeviceID()).append("',");
266             }
267             str = new StringBuilder(str.substring(0, str.length() - 1));
268             String delSql = "delete from "
269                     + "DeviceIPPortInfo "
270                     + "where "
271                     + "deviceID "
272                     + "not in "
273                     + "(" + str + ");";
274             if (ProfilerLogManager.isInfoEnabled()) {
275                 LOGGER.info("deleteExceptDeviceIPPort = {}", delSql);
276             }
277             execute(conn, delSql);
278         }
279     }
280 
281     /**
282      * updateDeviceIPPortInfo
283      *
284      * @param deviceStatus deviceStatus
285      * @param retryCount retryCount
286      * @param deviceId deviceId
287      * @return boolean
288      */
updateDeviceIPPortInfo(int deviceStatus, int retryCount, String deviceId)289     public boolean updateDeviceIPPortInfo(int deviceStatus, int retryCount, String deviceId) {
290         if (ProfilerLogManager.isInfoEnabled()) {
291             LOGGER.info("updateDeviceIPPortInfo");
292         }
293         StringBuilder sql = new StringBuilder("update DeviceIPPortInfo set ");
294         if (deviceStatus >= 0) {
295             sql.append("deviceStatus = ").append(deviceStatus).append(",");
296         }
297         if (retryCount > -1) {
298             sql.append("retryNum = ").append(retryCount);
299         }
300         sql.append(" where deviceID = '").append(deviceId).append("'");
301         Statement statement = null;
302         Connection conn = null;
303         try {
304             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
305             if (optionalConnection.isPresent()) {
306                 conn = optionalConnection.get();
307                 statement = conn.createStatement();
308                 int executeUpdate = statement.executeUpdate(sql.toString());
309                 return executeUpdate > 0;
310             }
311         } catch (SQLException sqlException) {
312             if (ProfilerLogManager.isErrorEnabled()) {
313                 LOGGER.error("sql Exception ", sqlException.getMessage());
314             }
315             return false;
316         } finally {
317             close(statement, conn);
318         }
319         return false;
320     }
321 
322     /**
323      * updateDeviceInfo
324      *
325      * @param ip ip
326      * @param port port
327      * @param forwardPort forwardPort
328      * @param deviceId deviceId
329      * @return boolean
330      */
updateDeviceInfo(String ip, int port, int forwardPort, String deviceId)331     public boolean updateDeviceInfo(String ip, int port, int forwardPort, String deviceId) {
332         if (ProfilerLogManager.isInfoEnabled()) {
333             LOGGER.info("updateDeviceInfo");
334         }
335         StringBuilder sql =
336             new StringBuilder("update DeviceIPPortInfo set ip = '").append(ip).append("',").append(" port = ")
337                 .append(port).append(",").append("forwardPort = ").append(forwardPort).append(" where deviceID = '")
338                 .append(deviceId).append("'");
339         Statement statement = null;
340         Connection conn = null;
341         try {
342             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
343             if (optionalConnection.isPresent()) {
344                 conn = optionalConnection.get();
345                 statement = conn.createStatement();
346                 int executeUpdate = statement.executeUpdate(sql.toString());
347                 return executeUpdate > 0;
348             }
349         } catch (SQLException sqlException) {
350             if (ProfilerLogManager.isErrorEnabled()) {
351                 LOGGER.error("update DeviceInfo failed ", sqlException.getMessage());
352             }
353             return false;
354         } finally {
355             close(statement, conn);
356         }
357         return false;
358     }
359 
360     /**
361      * Delete all device IP and port number information
362      */
deleteAllDeviceIPPortInfo()363     public void deleteAllDeviceIPPortInfo() {
364         if (ProfilerLogManager.isInfoEnabled()) {
365             LOGGER.info("deleteAllDeviceIPPortInfo");
366         }
367         Optional<Connection> deviceIPPort = getConnectByTable(DEVICE_TABLE);
368         if (deviceIPPort.isPresent()) {
369             Connection conn = deviceIPPort.get();
370             String sql = "delete from DeviceIPPortInfo";
371             execute(conn, sql);
372         }
373     }
374 
375     /**
376      * Is there a device IP and port number
377      *
378      * @param serialNumber serialNumber
379      * @return boolean
380      */
hasDeviceIPPort(String serialNumber)381     public boolean hasDeviceIPPort(String serialNumber) {
382         if (ProfilerLogManager.isInfoEnabled()) {
383             LOGGER.info("hasDeviceIPPort");
384         }
385         Optional<Connection> deviceIPPort = getConnectByTable(DEVICE_TABLE);
386         boolean flag = false;
387         if (deviceIPPort.isPresent()) {
388             Connection conn = deviceIPPort.get();
389             Statement pstmt = null;
390             ResultSet resultSet = null;
391             try {
392                 pstmt = conn.createStatement();
393                 String sql = "select "
394                         + "count(1) "
395                         + "as "
396                         + "hasDevice "
397                         + "from "
398                         + "DeviceIPPortInfo "
399                         + "where "
400                         + "deviceID = "
401                         + "" + "'" + serialNumber + "';";
402                 LOGGER.debug("hasDevice = {}", sql);
403                 resultSet = pstmt.executeQuery(sql);
404                 String hasDevice = "";
405                 while (resultSet.next()) {
406                     hasDevice = resultSet.getString("hasDevice");
407                 }
408                 if (!"".equals(hasDevice) && Integer.parseInt(hasDevice) > 0) {
409                     flag = true;
410                 }
411             } catch (SQLException sqlException) {
412                 if (ProfilerLogManager.isErrorEnabled()) {
413                     LOGGER.error("sqlException error ", sqlException.getMessage());
414                 }
415             } finally {
416                 close(pstmt, resultSet, conn);
417             }
418         }
419         return flag;
420     }
421 
422     /**
423      * get All Device IP Port Info
424      *
425      * @return List <DeviceIPPortInfo>
426      */
getAllDeviceIPPortInfos()427     public List<DeviceIPPortInfo> getAllDeviceIPPortInfos() {
428         if (ProfilerLogManager.isInfoEnabled()) {
429             LOGGER.info("getAllDeviceIPPortInfos");
430         }
431         Connection conn = null;
432         PreparedStatement ps = null;
433         ResultSet rs = null;
434         List<DeviceIPPortInfo> deviceIPPortInfos = new ArrayList<>();
435         try {
436             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
437             if (optionalConnection.isPresent()) {
438                 conn = optionalConnection.get();
439                 String sql = "select * from DeviceIPPortInfo";
440                 ps = conn.prepareStatement(sql);
441                 rs = ps.executeQuery();
442                 DeviceIPPortInfo deviceIPPortInfo;
443                 while (rs.next()) {
444                     deviceIPPortInfo = new DeviceIPPortInfo();
445                     String deviceID = rs.getString("deviceID");
446                     String deviceName = rs.getString("deviceName");
447                     String ip = rs.getString("ip");
448                     String deviceType = rs.getString("deviceType");
449                     deviceIPPortInfo.setDeviceID(deviceID);
450                     deviceIPPortInfo.setDeviceName(deviceName);
451                     deviceIPPortInfo.setIp(ip);
452                     if (deviceType.equals(FULL_HOS_DEVICE.getCpuAbi())) {
453                         deviceIPPortInfo.setDeviceType(FULL_HOS_DEVICE);
454                     } else {
455                         deviceIPPortInfo.setDeviceType(LEAN_HOS_DEVICE);
456                     }
457                     String connectType = rs.getString("connectType");
458                     deviceIPPortInfo.setConnectType(connectType);
459                     int port = rs.getInt("port");
460                     deviceIPPortInfo.setPort(port);
461                     int forwardPort = rs.getInt("forwardPort");
462                     deviceIPPortInfo.setForwardPort(forwardPort);
463                     deviceIPPortInfos.add(deviceIPPortInfo);
464                 }
465                 return deviceIPPortInfos;
466             }
467 
468         } catch (SQLException sqlException) {
469             if (ProfilerLogManager.isErrorEnabled()) {
470                 LOGGER.error("sqlException error ", sqlException.getMessage());
471             }
472         } finally {
473             close(ps, rs, conn);
474         }
475         return deviceIPPortInfos;
476     }
477 
478     /**
479      * get All Device IP Port Info
480      *
481      * @return List <DeviceIPPortInfo>
482      */
getOnlineDeviceInfoList()483     public List<DeviceIPPortInfo> getOnlineDeviceInfoList() {
484         if (ProfilerLogManager.isInfoEnabled()) {
485             LOGGER.info("getOnlineDeviceInfoList");
486         }
487         Connection conn = null;
488         PreparedStatement ps = null;
489         ResultSet rs = null;
490         List<DeviceIPPortInfo> deviceIPPortInfos = new ArrayList<>();
491         try {
492             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
493             if (optionalConnection.isPresent()) {
494                 conn = optionalConnection.get();
495                 String sql = "select "
496                         + "* "
497                         + "from "
498                         + "DeviceIPPortInfo "
499                         + "where "
500                         + "deviceStatus = 1";
501                 ps = conn.prepareStatement(sql);
502                 rs = ps.executeQuery();
503                 DeviceIPPortInfo deviceIPPortInfo;
504                 while (rs.next()) {
505                     deviceIPPortInfo = new DeviceIPPortInfo();
506                     String deviceType = rs.getString("deviceType");
507                     deviceIPPortInfo.setDeviceID(rs.getString("deviceID"));
508                     deviceIPPortInfo.setDeviceName(rs.getString("deviceName"));
509                     deviceIPPortInfo.setIp(rs.getString("ip"));
510                     if (deviceType.equals(FULL_HOS_DEVICE.getCpuAbi())) {
511                         deviceIPPortInfo.setDeviceType(FULL_HOS_DEVICE);
512                     } else {
513                         deviceIPPortInfo.setDeviceType(LEAN_HOS_DEVICE);
514                     }
515                     String connectType = rs.getString("connectType");
516                     deviceIPPortInfo.setConnectType(connectType);
517                     int port = rs.getInt("port");
518                     deviceIPPortInfo.setPort(port);
519                     deviceIPPortInfo.setForwardPort(rs.getInt("forwardPort"));
520                     deviceIPPortInfos.add(deviceIPPortInfo);
521                 }
522                 return deviceIPPortInfos;
523             }
524         } catch (SQLException sqlException) {
525             if (ProfilerLogManager.isErrorEnabled()) {
526                 LOGGER.error("sqlException error ", sqlException.getMessage());
527             }
528         } finally {
529             close(ps, rs, conn);
530         }
531         return deviceIPPortInfos;
532     }
533 
534     /**
535      * getDeviceIPPortInfo
536      *
537      * @param deviceID deviceID
538      * @return Optional <DeviceIPPortInfo>
539      */
getDeviceIPPortInfo(String deviceID)540     public Optional<DeviceIPPortInfo> getDeviceIPPortInfo(String deviceID) {
541         if (ProfilerLogManager.isInfoEnabled()) {
542             LOGGER.info("getDeviceIPPortInfo");
543         }
544         Connection conn = null;
545         Statement statement = null;
546         ResultSet rs = null;
547         try {
548             Optional<Connection> optionalConnection = getConnectByTable(DEVICE_TABLE);
549             if (optionalConnection.isPresent()) {
550                 conn = optionalConnection.get();
551                 String sql = "select "
552                         + "* "
553                         + "from "
554                         + "DeviceIPPortInfo "
555                         + "where "
556                         + "deviceID = "
557                         + "'" + deviceID + "'";
558                 statement = conn.createStatement();
559                 rs = statement.executeQuery(sql);
560                 DeviceIPPortInfo deviceIPPortInfo = null;
561                 while (rs.next()) {
562                     deviceIPPortInfo = new DeviceIPPortInfo();
563                     String deviceType = rs.getString("deviceType");
564                     deviceIPPortInfo.setDeviceID(rs.getString("deviceID"));
565                     deviceIPPortInfo.setDeviceName(rs.getString("deviceName"));
566                     deviceIPPortInfo.setIp(rs.getString("ip"));
567                     if (deviceType.equals(FULL_HOS_DEVICE.getCpuAbi())) {
568                         deviceIPPortInfo.setDeviceType(FULL_HOS_DEVICE);
569                     } else {
570                         deviceIPPortInfo.setDeviceType(LEAN_HOS_DEVICE);
571                     }
572                     deviceIPPortInfo.setConnectType(rs.getString("connectType"));
573                     deviceIPPortInfo.setPort(rs.getInt("port"));
574                     deviceIPPortInfo.setForwardPort(rs.getInt("forwardPort"));
575                     deviceIPPortInfo.setRetryNum(rs.getInt("retryNum"));
576                 }
577                 return Optional.ofNullable(deviceIPPortInfo);
578             }
579         } catch (SQLException sqlException) {
580             if (ProfilerLogManager.isErrorEnabled()) {
581                 LOGGER.error("sqlException error ", sqlException);
582             }
583         } finally {
584             close(statement, rs, conn);
585         }
586         return Optional.empty();
587     }
588 }
589