Java开发笔记--通用基础数据校验的设计

最近在开发一个功能，对排水管网的基础数据(包括管井、管道、泵站，雨水口，雨水口线，泵站，污水处理厂，排口等)的导入进行校验。

以字段为纬度，考虑二个方面的校验：数据库唯一，数据是否正确，以及对错误数据的处理。考虑到逻辑处理基本一致，准备设计一个通用的校验、转换的接口。

1.规则的设计

本来考虑做页面来进行基础数据表和字段的校验规则配置，考虑到时间问题，就直接将规则的设计放到字典当中，规则内容以json格式存储。

每个基础数据设计三个字典，如下图

残缺数据

"field"的"expno" 为表字段名称，值域代表，如果值是"from"则替换成"to"中的内容

重复数据

这个简单，就是代表数据库不重复

错误数据

判断数据是否正确，我这里设计了几个类型

table(来源于表)、dictionary(来源于字典)、enum(来源于枚举)、range(来源于范围)

2.程序设计

考虑到规则格式一致，代码基本相同。每个基础数据的校验没必要都去写一份逻辑。于是利用java反射的特性设计了通用的校验方法。

通用方法

如下：

实现源码

如下：

@Slf4j
@Service
public class BaseCommonServiceImpl implements BaseCommonService {@Autowiredprivate ApplicationContext applicationContext;@Resourceprivate SubareaMapper subareaMapper;public static Map<String,String> mappersMap=new HashMap<>();static {mappersMap.put("Manhole", "com.mapper.ManholeMapper");mappersMap.put("Comb", "com.mapper.CombMapper");mappersMap.put("Combline", "com.mapper.ComblineMapper");mappersMap.put("Household", "com.mapper.HouseholdMapper");mappersMap.put("Outfall", "com.mapper.OutfallMapper");mappersMap.put("Pipe", "com.mapper.PipeMapper");mappersMap.put("Pumpstation", "com.mapper.PumpstationMapper");mappersMap.put("SewageTp", "com.mapper.SewageTpMapper");}@Resourceprivate DictionaryDataMapper dictionaryDataMapper;@Overridepublic String checkBaseImportData(List baseData) {String dictionaryTypeId="";List<String> ruleTypeNames=new ArrayList<>();Class clazz = null;//判断是什么基础数据Object sampleObject = baseData.get(0);String type="";if (sampleObject instanceof ManholeEntity) {//正式环境dictionaryTypeId="fc4409aa9ca94a10bcb35a127a2661b3";//测试环境//dictionaryTypeId="93649a9eaf9741a480c7e36556ba3cf2";clazz=ManholeEntity.class;ruleTypeNames.add("检查井残缺数据(非空)");ruleTypeNames.add("检查井重复数据");ruleTypeNames.add("检查井错误数据");type="Manhole";} else if (sampleObject instanceof PipeEntity) {dictionaryTypeId="856b3232b4fc4fa996fbc6a76bc39254";ruleTypeNames.add("管道残缺数据(非空)");ruleTypeNames.add("管道重复数据");ruleTypeNames.add("管道错误数据");type="Pipe";clazz=PipeEntity.class;} else if(sampleObject instanceof PumpstationEntity){dictionaryTypeId="3575be7c0160438f8709a6dcc2f960a1";ruleTypeNames.add("泵站残缺数据(非空)");ruleTypeNames.add("泵站重复数据");ruleTypeNames.add("泵站错误数据");type="Pumpstation";clazz=PumpstationEntity.class;}else if(sampleObject instanceof CombEntity){dictionaryTypeId="3f7ee56d47c140f6b27dc7632bc4fc3d";ruleTypeNames.add("雨水口残缺数据(非空)");ruleTypeNames.add("雨水口重复数据");ruleTypeNames.add("雨水口错误数据");type="Comb";clazz=CombEntity.class;}else if(sampleObject instanceof ComblineEntity){dictionaryTypeId="1a9dc3a7ac1f4cb6b1241d98b52a6d15";ruleTypeNames.add("雨水口长度残缺数据(非空)");ruleTypeNames.add("雨水口长度重复数据");ruleTypeNames.add("雨水口长度错误数据");type="Combline";clazz=ComblineEntity.class;}else if(sampleObject instanceof HouseholdEntity){dictionaryTypeId="ae25497f0ee04770ab5cb9b27e95f036";ruleTypeNames.add("排水户残缺数据(非空)");ruleTypeNames.add("排水户重复数据");ruleTypeNames.add("排水户错误数据");type="Household";clazz=HouseholdEntity.class;}else if(sampleObject instanceof OutfallEntity){dictionaryTypeId="fac6e7b6fea64bb0bfde9af4e0e29b1c";ruleTypeNames.add("排口残缺数据(非空)");ruleTypeNames.add("排口重复数据");ruleTypeNames.add("排口错误数据");type="Outfall";clazz=OutfallEntity.class;}else if(sampleObject instanceof SewageTpEntity){dictionaryTypeId="1fcaead741f04fc7b9d395f176723a49";ruleTypeNames.add("污水处理厂残缺数据(非空)");ruleTypeNames.add("污水处理厂重复数据");ruleTypeNames.add("污水处理厂错误数据");type="SewageTp";clazz=SewageTpEntity.class;}else{return "暂不支持此类型的数据校验";}StringBuilder errorMsg = new StringBuilder();//查询 基础数据清洗校验规则QueryWrapper<DictionaryDataEntity> qw = new QueryWrapper<>();qw.eq("F_DictionaryTypeId", dictionaryTypeId);List<DictionaryDataEntity> commonRules = dictionaryDataMapper.selectList(qw);//查询出所有街道 用于后续内存比较Map<String, String> addressDictMap = new HashMap<>();QueryWrapper<DictionaryDataEntity> qwAddress = new QueryWrapper<>();qwAddress.eq("F_DictionaryTypeId", "2a7d260c72ba4ab5bc6e31bb12425ed1");List<DictionaryDataEntity> addressDictList = dictionaryDataMapper.selectList(qwAddress);for (DictionaryDataEntity dict : addressDictList) {addressDictMap.put(dict.getFullName(), dict.getId());}//查询出所有雨水分区 用于后续内存比较Map<String, String> stormSystemMap = new HashMap<>();List<SubareaEntity> stormSystemList = subareaMapper.selectList(null);for (SubareaEntity area : stormSystemList) {stormSystemMap.put(area.getSubareanm(), area.getId());}//用于判断遍历到了哪一行int count = 0;//遍历excel 数据for(Object record:baseData){count++;//遍历规则for (DictionaryDataEntity rule : commonRules) {//规则名称String ruleType = rule.getFullName();//规则内容  json数组 格式String ruleJson = rule.getDescription();if (StringUtil.isEmpty(ruleJson)) {continue;}//解析规则内容为数组/*** 示例* [{"field":"stormsystemid","type":"table","value":"ps_subarea"},*  {"field":"address","type":"dictionary","value":"2a7d260c72ba4ab5bc6e31bb12425ed1"},*  {"field":"type","type":"enum","value":[1,2,3,4]},*  {"field":"elevation","type":"range","defaultmin":0,"defaultmax":110}]*/JSONArray ruleArray;try {ruleArray = JSONArray.parseArray(ruleJson);} catch (Exception e) {log.error("规则转换成json异常", e);continue;}if (ruleArray.size() == 0) {continue;}//遍历规则-关联到字段for (Object ruleObj : ruleArray) {JSONObject jsonObject = (JSONObject) ruleObj;//获得要校验的字段String field = jsonObject.getString("field");//获得excel 字段值Object fieldValue;try {fieldValue = MyReflectionUtils.getColumnValue(clazz, record, field);} catch (NoSuchFieldException | IllegalAccessException e) {log.error("无此字段", e);continue;}//判断规则进行哪一种规则处理：校验非空、校验重复、校验错误if (ruleTypeNames.get(0).equals(ruleType)) {//校验非空 配置默认值JSONArray jsonArray = jsonObject.getJSONArray("valueparse");if (null != jsonArray && jsonArray.size() > 0) {//根据规则处理残缺数据for (Object obj : jsonArray) {JSONObject columnObj = (JSONObject) obj;String from = columnObj.getString("from");String to = columnObj.getString("to");try {if (fieldValue == null && StringUtil.isEmpty(from)) {MyReflectionUtils.setColumnValue(clazz, record, field, to);} else if (fieldValue.equals(from)) {MyReflectionUtils.setColumnValue(clazz, record, field, to);}} catch (NoSuchFieldException | IllegalAccessException e) {}}}try {fieldValue = MyReflectionUtils.getColumnValue(clazz, record, field);} catch (NoSuchFieldException | IllegalAccessException e) {}if (null == fieldValue || StringUtil.isEmpty(fieldValue.toString())) {String annotationVal = MyReflectionUtils.getColumnAnnotationVal(clazz, field);errorMsg.append("第" + count + "行," + annotationVal + "字段不能为空;");}}else if (ruleTypeNames.get(1).equals(ruleType)) {//校验重复//[{"field":"expno"}]if(StringUtil.isNotEmpty(field)){QueryWrapper queryWrapper = new QueryWrapper();queryWrapper.eq(field,fieldValue);int countRepeat=this.commonSelectCount(type,queryWrapper);if(countRepeat>0){String annotationVal = MyReflectionUtils.getColumnAnnotationVal(clazz, field);errorMsg.append("第" + count + "行," + annotationVal + "字段数据库重复;");}}} else if (ruleTypeNames.get(2).equals(ruleType)) {//校验错误//[{"field":"stormsystemid","type":"table","value":"ps_subarea"},// {"field":"address","type":"dictionary","value":"2a7d260c72ba4ab5bc6e31bb12425ed1"},// {"field":"type","type":"enum","value":[1,2,3,4]},// {"field":"elevation","type":"range","defaultmin":0,"defaultmax":110}]String errorFieldType = jsonObject.getString("type");String errorFieldValue = jsonObject.getString("value");//校验错误分为四中类型：table(来源于表)、dictionary(来源于字典)、enum(来源于枚举)、range(来源于范围)if("table".equals(errorFieldType) && "ps_subarea".equals(errorFieldValue)){//验证雨水分区Boolean checkFlag= stormSystemMap.containsKey(fieldValue);if (!checkFlag) {errorMsg.append("第" + count + "行, 错误的雨水分区值;");}}else if("dictionary".equals(errorFieldType) && "2a7d260c72ba4ab5bc6e31bb12425ed1".equals(errorFieldValue)){//验证地址Boolean checkFlag= addressDictMap.containsKey(fieldValue);if (!checkFlag) {errorMsg.append("第" + count + "行, 错误的地址值;");}}else if("enum".equals(errorFieldType)){//验证类型Boolean checkFlag=errorFieldValue.indexOf(fieldValue.toString()) > 0;if (!checkFlag) {String annotationVal = MyReflectionUtils.getColumnAnnotationVal(clazz, field);errorMsg.append("第" + count + "行," + annotationVal + "字段数据有误;");}}else if("range".equals(errorFieldType)){String defaultmin = jsonObject.getString("defaultmin");String defaultmax = jsonObject.getString("defaultmax");if(Double.parseDouble(fieldValue.toString())<Double.parseDouble(defaultmin)){String annotationVal = MyReflectionUtils.getColumnAnnotationVal(clazz, field);errorMsg.append("第" + count + "行," + annotationVal + "字段数据有误;");}if(Double.parseDouble(fieldValue.toString())>Double.parseDouble(defaultmax)){String annotationVal = MyReflectionUtils.getColumnAnnotationVal(clazz, field);errorMsg.append("第" + count + "行," + annotationVal + "字段数据有误;");}}}}}}return errorMsg.toString();}private int commonSelectCount(String type,QueryWrapper queryWrapper) {Object obj=null;try {Class<?> clazz = Class.forName(mappersMap.get(type));Object proxyObject = applicationContext.getBean(clazz);Method method = getMethod(proxyObject.getClass(), "selectCount");obj=method.invoke(proxyObject,queryWrapper);} catch (Exception e) {e.printStackTrace();}return (int)obj;}private Method getMethod(Class proxyObject, String methodStr) {Method[] methods = proxyObject.getMethods();for(Method method : methods) {if(method.getName().equalsIgnoreCase(methodStr)) {return method;}}return null;}}