03/29 使用 海康SDK 对接时使用的 MysqlUtils

embedded/2025/2/11 21:06:47/

前言

最近朋友的需求, 是需要使用 海康sdk 连接海康设备, 进行数据的获取, 比如 进出车辆, 进出人员 

这一部分是 资源比较贫瘠时的一个 Mysql 工具类 

 

 

测试用例

public class MysqlUtils {public static String MYSQL_HOST = "192.168.31.9";public static int MYSQL_PORT = 3306;public static String MYSQL_DB = "20240811_vehicle_stats";public static String MYSQL_USERNAME = "root";public static String MYSQL_PASSWORD = "postgres";public static String COLUMN_CATM = "catm";public static String COLUMN_UPTM = "uptm";public static String SQL_DUMMY_SQL = "select 1;";public static boolean DEFAULT_ADD_CATM_UPTM = false;public static SingleStringColumnExtractor SINGLE_STRING_COLUMN_EXTRACTOR = new SingleStringColumnExtractor();public static GenericMapExtractor GENERIC_MAP_EXTRACTOR = new GenericMapExtractor();public static MyStatsVehicleFlowLogExtractor MY_STATISTICS_VEHICLE_FLOW_LOG_EXTRACTOR = new MyStatsVehicleFlowLogExtractor();public static MyStatsPeopleFlowLogExtractor MY_STATISTICS_PEOPLE_FLOW_LOG_EXTRACTOR = new MyStatsPeopleFlowLogExtractor();// disable constructorprivate MysqlUtils() {System.err.println("can't instantiate !");}// mysql jdbcDriverstatic {try {Class.forName("com.mysql.jdbc.Driver");} catch (ClassNotFoundException e) {System.err.println("can't found jdbcDriver !");}}public static void init() {MYSQL_HOST = ConfigUtils.getString("MYSQL_HOST", MYSQL_HOST);MYSQL_PORT = ConfigUtils.getInt("MYSQL_PORT", MYSQL_PORT);MYSQL_DB = ConfigUtils.getString("MYSQL_DB", MYSQL_DB);MYSQL_USERNAME = ConfigUtils.getString("MYSQL_USERNAME", MYSQL_USERNAME);MYSQL_PASSWORD = ConfigUtils.getString("MYSQL_PASSWORD", MYSQL_PASSWORD);DEFAULT_ADD_CATM_UPTM = ConfigUtils.getString("DEFAULT_ADD_CATM_UPTM", String.valueOf(DEFAULT_ADD_CATM_UPTM)).equalsIgnoreCase("true");}// 获取 jdbc 链接public static Connection getConnection(String ip, int port, String dbName, String userName, String password) {Connection con = null;try {con = DriverManager.getConnection(String.format("jdbc:mysql://%s:%d/%s?useUnicode=true&characterEncoding=UTF8", ip, port, dbName), userName, password);} catch (SQLException se) {se.printStackTrace();System.err.println("error while try to get an connection !");}return con;}public static Connection getConnection() {return MysqlUtils.getConnection(MYSQL_HOST, MYSQL_PORT, MYSQL_DB, MYSQL_USERNAME, MYSQL_PASSWORD);}// 执行 jdbc 查询public static <T> List<T> executeQuery(String sql, Function<ResultSet, T> recordExtractor) {Connection con = MysqlUtils.getConnection(MYSQL_HOST, MYSQL_PORT, MYSQL_DB, MYSQL_USERNAME, MYSQL_PASSWORD);PreparedStatement stat = null;ResultSet rs = null;List<T> result = new ArrayList<>();try {stat = con.prepareStatement(sql);rs = stat.executeQuery();while (rs.next()) {result.add(recordExtractor.apply(rs));}} catch (SQLException e) {e.printStackTrace();} finally {try {if(stat != null) {stat.close();}if(rs != null) {rs.close();}if (con != null) {con.close();}} catch (SQLException e) {e.printStackTrace();}}return result;}public static List<Map<String, Object>> executeQuery(String sql) {return executeQuery(sql, GENERIC_MAP_EXTRACTOR);}// 执行 jdbc 更新public static int executeUpdate(String sql) {Connection con = MysqlUtils.getConnection(MYSQL_HOST, MYSQL_PORT, MYSQL_DB, MYSQL_USERNAME, MYSQL_PASSWORD);PreparedStatement stat = null;int updated = -1;try {stat = con.prepareStatement(sql);updated = stat.executeUpdate();} catch (SQLException e) {e.printStackTrace();} finally {try {if(stat != null) {stat.close();}if (con != null) {con.close();}} catch (SQLException e) {e.printStackTrace();}}return updated;}public static String assembleInsertSql(String tableName, Map<String, Object> entity, boolean addCommonFields) {String insertSqlTemplate = " insert into %s (%s) values (%s); ";List<String> fieldNames = new ArrayList<>(), fieldValues = new ArrayList<>();for (String fieldName : entity.keySet()) {Object originalFieldValue = entity.get(fieldName);String fieldValue = resolveFieldValue(entity, fieldName, originalFieldValue);fieldNames.add(String.format("`%s`", fieldName));fieldValues.add(transferFieldValueIfNecessary(fieldValue));}if (addCommonFields) {Long currentTs = System.currentTimeMillis();addFixedFieldNames(fieldNames, currentTs, true);addFixedFieldValues(fieldValues, currentTs, true);}String sql = String.format(insertSqlTemplate, tableName,join(fieldNames, ", "),join(fieldValues, ", "));return sql;}public static String assembleInsertSql(String tableName, Map<String, Object> entity) {return assembleInsertSql(tableName, entity, DEFAULT_ADD_CATM_UPTM);}public static String assembleBatchInsertSql(String tableName, List<Map<String, Object>> entityList, boolean addCommonFields) {String insertSqlTemplate = " insert into %s (%s) values %s; ";List<String> insertFieldNames = new ArrayList<>(), outerFieldValues = new ArrayList<>();Set<String> fieldNames = new LinkedHashSet<>();Long currentTs = System.currentTimeMillis();for (Map<String, Object> entity : entityList) {fieldNames.addAll(entity.keySet());}for (String fieldName : fieldNames) {insertFieldNames.add(String.format("`%s`", fieldName));}if (addCommonFields) {addFixedFieldNames(insertFieldNames, currentTs, true);}for (Map<String, Object> entity : entityList) {List<String> fieldValues = new ArrayList<>();for (String fieldName : fieldNames) {Object originalFieldValue = entity.get(fieldName);String fieldValue = resolveFieldValue(entity, fieldName, originalFieldValue);fieldValues.add(transferFieldValueIfNecessary(fieldValue));}if (addCommonFields) {addFixedFieldValues(fieldValues, currentTs, true);}outerFieldValues.add(String.format("(%s)", join(fieldValues, ", ")));}String sql = String.format(insertSqlTemplate, tableName,join(insertFieldNames, ", "),join(outerFieldValues, ", "));return sql;}public static String assembleBatchInsertSql(String tableName, List<Map<String, Object>> entityList) {return assembleBatchInsertSql(tableName, entityList, DEFAULT_ADD_CATM_UPTM);}public static String assembleUpdateSql(String tableName, String idFieldName, Map<String, Object> entity, boolean addCommonFields) {String updateSqlTemplate = " update %s set %s %s; ";List<String> fieldNames = new ArrayList<>(), fieldValues = new ArrayList<>();for (String fieldName : entity.keySet()) {Object originalFieldValue = entity.get(fieldName);String fieldValue = resolveFieldValue(entity, fieldName, originalFieldValue);fieldNames.add(String.format("`%s`", fieldName));fieldValues.add(transferFieldValueIfNecessary(fieldValue));}if (addCommonFields) {Long currentTs = System.currentTimeMillis();addFixedFieldNames(fieldNames, currentTs, false);addFixedFieldValues(fieldValues, currentTs, false);}List<String> setClauseList = new ArrayList<>();for (int i = 0; i < fieldNames.size(); i++) {setClauseList.add(String.format(" %s = %s ", fieldNames.get(i), fieldValues.get(i)));}String setClause = join(setClauseList, ", ");String idValue = String.valueOf(entity.get(idFieldName));String whereCond = String.format(" where %s = %s ", idFieldName, transferFieldValueIfNecessary(idValue));String sql = String.format(updateSqlTemplate, tableName, setClause, whereCond);return sql;}public static String assembleUpdateSql(String tableName, String idFieldName, Map<String, Object> entity) {return assembleUpdateSql(tableName, idFieldName, entity, DEFAULT_ADD_CATM_UPTM);}public static List<String> assembleBatchSaveSql(String tableName, String idFieldName,List<Map<String, Object>> entityList, Function<ResultSet, String> idExtractor,boolean addCommonFields) {List<String> idList = entityList.stream().map(ele -> String.valueOf(ele.get(idFieldName))).collect(Collectors.toList());List<String> existsIdList = selectExistsById(tableName, idFieldName, idList, idExtractor);Map<String, Map<String, Object>> toInsertById = new LinkedHashMap<>(), toUpdateById = new LinkedHashMap<>();for (Map<String, Object> entity : entityList) {String idValue = String.valueOf(entity.get(idFieldName));Map<String, Map<String, Object>> entityByIdTmp = toInsertById;if (existsIdList.contains(idValue)) {entityByIdTmp = toUpdateById;}entityByIdTmp.put(idValue, entity);}List<String> result = new ArrayList<>();String insertSql = SQL_DUMMY_SQL;List<Map<String, Object>> toInsertList = new ArrayList<>(toInsertById.values());if (!isEmpty(toInsertList)) {insertSql = assembleBatchInsertSql(tableName, toInsertList, addCommonFields);}result.add(insertSql);List<Map<String, Object>> toUpdateList = new ArrayList<>(toUpdateById.values());for (Map<String, Object> toUpdate : toUpdateList) {String updateSql = assembleUpdateSql(tableName, idFieldName, toUpdate, addCommonFields);result.add(updateSql);}return result;}public static <T> List<String> assembleBatchSaveSql(String tableName, String idFieldName,List<Map<String, Object>> entityList, Function<ResultSet, String> recordExtractor) {return assembleBatchSaveSql(tableName, idFieldName, entityList, recordExtractor, true);}public static List<String> selectExistsById(String tableName, String idFieldName, List<String> idList, Function<ResultSet, String> recordExtractor) {if (isEmpty(idList)) {return Collections.emptyList();}String querySqlTemplate = " select %s as id from %s %s; ";String idInSnippet = join(idList.stream().map(MysqlUtils::transferFieldValueIfNecessary).collect(Collectors.toList()), ", ");String whereCond = String.format(" where %s in (%s) ", idFieldName, idInSnippet);String querySql = String.format(querySqlTemplate, idFieldName, tableName, whereCond);return executeQuery(querySql, recordExtractor);}public static String generateQuerySql(String tableName, String whereCond) {String querySql = String.format(" select * from %s ", tableName);if (isNotBlank(whereCond)) {querySql = String.format(" %s where %s ", querySql, whereCond);}return querySql;}public static String generateDeleteSql(String tableName, String whereCond) {String querySql = String.format(" delete from %s ", tableName);if (isNotBlank(whereCond)) {querySql = String.format(" %s where %s ", querySql, whereCond);}return querySql;}public static String resolveFieldValue(Map<String, Object> entity, String fieldName, Object fieldValue) {if (fieldValue == null) {return null;}if (fieldValue instanceof Date) {return DateFormatUtils.format((Date) fieldValue);}if (fieldValue instanceof LocalDateTime) {LocalDateTime dateTime = ((LocalDateTime) fieldValue);return String.format("%s-%s-%s %s:%s:%s",String.format("%04d", dateTime.getYear()),String.format("%02d", dateTime.getMonthValue()),String.format("%02d", dateTime.getDayOfMonth()),String.format("%02d", dateTime.getHour()),String.format("%02d", dateTime.getMinute()),String.format("%02d", dateTime.getSecond()));}return String.valueOf(fieldValue);}public static void addFixedFieldNames(List<String> fieldNames, Long currentTs, boolean addCatm) {if (addCatm) {fieldNames.add(COLUMN_CATM);}fieldNames.add(COLUMN_UPTM);}public static void addFixedFieldValues(List<String> fieldValues, Long currentTs, boolean addCatm) {if (addCatm) {fieldValues.add(transferFieldValueIfNecessary(String.valueOf(currentTs)));}fieldValues.add(transferFieldValueIfNecessary(String.valueOf(currentTs)));}public static String transferFieldValueIfNecessary(String fieldValue) {if (fieldValue == null) {return "NULL";}if (fieldValue.contains("\"")) {fieldValue = fieldValue.replace("\"", "\\\"");}return String.format("\"%s\"", fieldValue);}public static String transferSingleQuoteFieldValueIfNecessary(String fieldValue) {if (fieldValue == null) {return "NULL";}if (fieldValue.contains("'")) {fieldValue = fieldValue.replace("'", "\\'");}return String.format("'%s'", fieldValue);}public static void fillOrTrimToFieldNames(Map<String, Object> entity, List<String> fieldNames, String defaultValue) {List<String> field2Remove = new ArrayList<>();for (Map.Entry<String, Object> entry : entity.entrySet()) {String fieldName = entry.getKey();if (!fieldNames.contains(fieldName)) {field2Remove.add(fieldName);}}for (String fieldName : field2Remove) {entity.remove(fieldName);}for (String fieldName : fieldNames) {if (!entity.containsKey(fieldName)) {entity.put(fieldName, defaultValue);}}}public static void fillOrTrimToFieldNames(Map<String, Object> entity, List<String> fieldNames) {fillOrTrimToFieldNames(entity, fieldNames, "");}public static String wrapSqlIn(List<String> list) {if (isEmpty(list)) {return "";}return String.format("\"%s\"", join(list, "\", \""));}public static boolean isBlank(String str) {return str == null || str.trim().length() == 0;}public static boolean isNotBlank(String str) {return !isBlank(str);}public static <T> boolean isEmpty(Collection<T> list) {return list == null || (list.size() == 0);}public static <T> String join(Collection<T> list, String seprator) {StringBuffer result = new StringBuffer();for (Iterator ite = list.iterator(); ite.hasNext(); result.append((String) ite.next())) {if (result.length() != 0) {result.append(seprator);}}return result.toString();}}

 

 

GenericMapExtractor
public class GenericMapExtractor implements Function<ResultSet, Map<String, Object>> {@Overridepublic Map<String, Object> apply(ResultSet resultSet) {try {Map<String, Object> result = new LinkedHashMap<>();int columnCount = resultSet.getMetaData().getColumnCount();for (int i = 1; i <= columnCount; i++) {String columnName = resultSet.getMetaData().getColumnName(i);result.put(columnName, resultSet.getObject(columnName));}return result;} catch (Exception e) {e.printStackTrace();return null;}}}

 

 

MyStatsPeopleFlowLogExtractor
public class MyStatsPeopleFlowLogExtractor implements Function<ResultSet, StatsPeopleFlowLog> {@Overridepublic StatsPeopleFlowLog apply(ResultSet resultSet) {try {Map<String, Object> entityMap = MysqlUtils.GENERIC_MAP_EXTRACTOR.apply(resultSet);JSONObject entityJson = (JSONObject) JSON.toJSON(entityMap);return StatsPeopleFlowLog.fromJSON(entityJson);} catch (Exception e) {e.printStackTrace();return null;}}
}

 

 

部分截图

 

 

完 

 

 

 

 

 


http://www.ppmy.cn/embedded/161421.html

相关文章

【ESP32cam人脸识别开门及服务器端实战源码】

【ESP32cam人脸识别开门及服务器端实战源码】

本项目实现了一个基于ESP32-CAM的实时人脸识别系统&#xff0c;能够通过WiFi进行视频流传输&#xff0c;并在检测到人脸时触发开门指令。系统由两个主要部分组成&#xff1a;video.py&#xff08;后端服务器&#xff09;和 ESP32-CAM.ino&#xff08;ESP32-CAM固件&#xff09;…
阅读更多...
保姆级教程Docker部署Zookeeper镜像

保姆级教程Docker部署Zookeeper镜像

目录 一、安装Docker及可视化工具 二、创建Zookeeper网络 三、镜像选择 四、单节点部署 1、创建挂载目录 2、命令运行容器 3、Compose运行容器 4、查看运行状态 5、验证是否正常运行 一、安装Docker及可视化工具 Docker及可视化工具的安装可参考&#xff1a;Ubuntu上…
阅读更多...
机器学习 - 理解偏差-方差分解

机器学习 - 理解偏差-方差分解

为了避免过拟合&#xff0c;我们经常会在模型的拟合能力和复杂度之间进行权衡。拟合能力强的模型一般复杂度会比较高&#xff0c;容易导致过拟合。相反&#xff0c;如果限制模型的复杂度&#xff0c;降低其拟合能力&#xff0c;又可能会导致欠拟合。因此&#xff0c;如何在模型…
阅读更多...
在spring boot 项目中远程调用时，如果使用subList()方法报错原因分析

在spring boot 项目中远程调用时，如果使用subList()方法报错原因分析

在spring boot 项目调式中&#xff0c;接口一直报错&#xff0c;后来才发现是因为在微服务中&#xff0c;远程调用接口时&#xff0c;如果方法中包含 ArrayList.subList()方法会导致异常。 在 Spring Boot 项目中&#xff0c;远程调用&#xff08;如通过 REST API 或 RPC&…
阅读更多...
手机向电脑传输文件方法有哪些？

手机向电脑传输文件方法有哪些？

手机和电脑已经成为我们日常生活和工作中不可或缺的工具&#xff0c;而它们之间的文件传输需求也日益增加。为了帮助大家更高效地完成这一任务&#xff0c;本文将介绍三种常用的手机向电脑传输文件方法&#xff0c;方便您根据不同场景选择合适的方式。 方法1.数据线 当您有数…
阅读更多...
Auto-go 环境配置

Auto-go 环境配置

go环境配置 1.下载 Go 安装包 从 Go 官方网站&#xff08;https://golang.org/dl/&#xff09;下载适合你操作系统的 Go 安装包。不过由于网络原因&#xff0c;可能访问官方网站不太方便可以用我这里的链接Go安装包下载地址点击自动下载 2.下载ide这里使用GoLand 官方网站 …
阅读更多...
使用 mkcert 本地部署启动了 TLS/SSL 加密通讯的 MongoDB 副本集和分片集群

使用 mkcert 本地部署启动了 TLS/SSL 加密通讯的 MongoDB 副本集和分片集群

MongoDB 是支持客户端与 MongoDB 服务器之间启用 TLS/SSL 进行加密通讯的, 对于 MongoDB 副本集和分片集群内部的通讯, 也可以开启 TLS/SSL 认证. 本文会使用 mkcert 创建 TLS/SSL 证书, 基于创建的证书, 介绍 MongoDB 副本集、分片集群中启动 TLS/SSL 通讯的方法. 我们将会在…
阅读更多...
通过 SQLAlchemy 实现多表映射

通过 SQLAlchemy 实现多表映射

在使用 SQLAlchemy 进行多表映射时&#xff0c;我们可以使用 ORM&#xff08;对象关系映射&#xff09; 的方式将多个表与 Python 类进行映射。SQLAlchemy 提供了功能强大的机制&#xff0c;能够轻松地将数据库表和 Python 对象之间的关系建立起来。 1、问题背景 假设我们有一…
阅读更多...
最新文章

Copyright @ 2022~2023