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

前言

最近朋友的需求, 是需要使用 海康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;}}
}

 

 

部分截图

 

 

完