解决方案：Java 导入数据校验工具类

不念不忘少年蓝@ 2023-06-18 11:00 9阅读 0赞

### 1、Excel 数据导入 ###

系统对导入的数据进行各种校验，比如：判空校验，日期格式要求，数值格式限定等，这些校验我们可以考虑做成工具类进行，避免逐个字段进行逻辑判断和校验，代码量较大，编写代码手误等不利条件导致工作量和验证耗时较长。

所以，考虑了一种统一判断的方案，对所有个字段设定格式要求，代码自动校验。

说明：因个人开发周期短，尚未对方案进行优化，不足支持，请多指正。代码仅供参考。

### 2、工具类代码示例 ###

package com.myfutech.unif.insure.util;
    
    import com.myfutech.insure.service.api.enums.InsureGenderEnum;
    import com.myfutech.insure.service.api.enums.InsureOrderStatusEnum;
    import com.myfutech.insure.service.api.enums.OrderTypeEnum;
    import com.myfutech.insure.service.api.enums.huagui.SocialSecurityEnum;
    import com.myfutech.unif.insure.vo.request.OrderImportRulesVO;
    import org.apache.commons.collections4.CollectionUtils;
    import org.apache.commons.lang3.StringUtils;
    
    import java.text.SimpleDateFormat;
    import java.util.HashMap;
    import java.util.List;
    import java.util.Map;
    import java.util.concurrent.atomic.AtomicInteger;
    
    /**
     * @author liuxd
     * @version 1.0
     * @date 2019-11-26 15:39
     */
    public class ImportOrderRulesUtils {
    
        private static Map<Integer, OrderImportRulesVO> rulesMap = new HashMap<>();
    
        static {
            rulesMap.put(1, new OrderImportRulesVO(1, "保单类型", true, false, false));
            rulesMap.put(2, new OrderImportRulesVO(2, "员工编号", true, false, true));
            rulesMap.put(3, new OrderImportRulesVO(3, "保单号", true, false, false));
            rulesMap.put(4, new OrderImportRulesVO(4, "产品编码", true, false, false));
            rulesMap.put(5, new OrderImportRulesVO(5, "渠道编码", false, false, false));
            rulesMap.put(6, new OrderImportRulesVO(6, "份数", false, false, true));
            rulesMap.put(7, new OrderImportRulesVO(7, "社保标记", false, false, false));
            rulesMap.put(8, new OrderImportRulesVO(8, "保险期间", true, false, false));
            rulesMap.put(9, new OrderImportRulesVO(9, "交费年期", true, false, false));
            rulesMap.put(10, new OrderImportRulesVO(10, "保单生效日期", true, true, false));
            rulesMap.put(11, new OrderImportRulesVO(11, "保单终止日期", true, true, false));
            rulesMap.put(12, new OrderImportRulesVO(12, "保额（元）", true, false, true));
            rulesMap.put(13, new OrderImportRulesVO(13, "保费（元）", true, false, true));
            rulesMap.put(14, new OrderImportRulesVO(14, "出单时间", true, true, false));
            rulesMap.put(15, new OrderImportRulesVO(15, "支付时间", false, true, false));
            rulesMap.put(16, new OrderImportRulesVO(16, "保单状态", true, false, false));
            rulesMap.put(17, new OrderImportRulesVO(17, "投保人", true, false, false));
            rulesMap.put(18, new OrderImportRulesVO(18, "投保人性别", false, false, false));
            rulesMap.put(19, new OrderImportRulesVO(19, "投保人出生日期", false, true, false));
            rulesMap.put(20, new OrderImportRulesVO(20, "投保人证件类型", false, false, false));
            rulesMap.put(21, new OrderImportRulesVO(21, "投保人证件号码", true, false, false));
            rulesMap.put(22, new OrderImportRulesVO(22, "投保人手机号", false, false, false));
            rulesMap.put(23, new OrderImportRulesVO(23, "投保人邮箱", false, false, false));
            rulesMap.put(24, new OrderImportRulesVO(24, "投保人家庭住址", false, false, false));
            rulesMap.put(25, new OrderImportRulesVO(25, "被保人与投保人关系", false, false, false));
            rulesMap.put(26, new OrderImportRulesVO(26, "被保人", true, false, false));
            rulesMap.put(27, new OrderImportRulesVO(27, "被保人性别", false, false, false));
            rulesMap.put(28, new OrderImportRulesVO(28, "被保人生日", false, true, false));
            rulesMap.put(29, new OrderImportRulesVO(29, "被保人证件类型", false, false, false));
            rulesMap.put(30, new OrderImportRulesVO(30, "被保人证件号码", true, false, false));
            rulesMap.put(31, new OrderImportRulesVO(31, "被保人手机号", false, false, false));
            rulesMap.put(32, new OrderImportRulesVO(32, "被保人家庭住址", false, false, false));
    
        }
    
        public static String validDataBasic(List<List<Object>> listArray, AtomicInteger countNo) {
            StringBuilder errorMsg = new StringBuilder();
            int length = 32;
            int rowNum = 0;
            for (List<Object> array : listArray) {
                rowNum++;
                if (ImportOrderRulesUtils.isEmpty(array)) {
                    continue;
                }
                int actLength = array.size();
                for (int i = 0; i < length; i++) {
                    Object object = null;
                    if (i < actLength) {
                        object = array.get(i);
                    }
                    if (null != object && object.toString().trim().equals("*标题")) {
                        break;
                    }
                    OrderImportRulesVO rulesVO = rulesMap.get(i + 1);
                    String msg = rules(rulesVO, object, countNo, rowNum);
                    errorMsg.append(msg);
    
                    if (StringUtils.isNotBlank(msg)) {
                        continue;
                    }
    
                    //手写校验
                    if (i == 0 && null != object && null == OrderTypeEnum.getCodeByValue(object.toString())) {
                        msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "值不在规定范围内");
                    } else if (i == 6 && null != object && null == SocialSecurityEnum.getCodeByValue(object.toString())) {
                        msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "值不在规定范围内");
                    } else if (i == 15 && null != object && null == InsureOrderStatusEnum.getCodeByValue(object.toString())) {
                        msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "值不在规定范围内");
                    } else if (i == 17 && null != object && null == InsureGenderEnum.getCodeByValue(object.toString())) {
                        msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "值不在规定范围内");
                    } else if (i == 26 && null != object && null == InsureGenderEnum.getCodeByValue(object.toString())) {
                        msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "值不在规定范围内");
                    } else if (i == 21 && null != object) {
                        String v = object.toString().trim();
                        if (!v.startsWith("1") || v.length() != 11) {
                            msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "格式有误");
                        }
                    } else if (i == 30 && null != object) {
                        String v = object.toString().trim();
                        if (!v.startsWith("1") || v.length() != 11) {
                            msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "格式有误");
                        }
                    }
    
                    errorMsg.append(msg);
    
                }
    
            }
    
            return errorMsg.toString();
    
        }
    
        public static String rules(OrderImportRulesVO rulesVO, Object object, AtomicInteger countNo, Integer rowNum) {
            String msg = "";
            if (null == object) {
                if (rulesVO.getNotNullFlag()) {
                    msg = getNotNullMsg(null, countNo.incrementAndGet(), rowNum, rulesVO.getFiledName());
    
                }
            } else if (rulesVO.getDateFlag()) {
                boolean flag = validTime(object.toString());
                if (!flag) {
                    msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "格式错误");
                }
            } else if (rulesVO.getNumberFlag()) {
                if (!StringUtils.isNumeric(object.toString())) {
                    msg = getUnValidMsg(countNo.incrementAndGet(), rowNum, rulesVO.getFiledName(), "格式错误");
                }
            }
    
    
            return msg;
    
        }
    
        public static boolean validTime(String timeStr) {
            boolean flag1 = true;
            boolean flag2 = true;
            SimpleDateFormat df1 = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat df2 = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            df1.setLenient(false);
            df2.setLenient(false);
            try {
                df1.parse(timeStr);
            } catch (Exception e) {
                flag1 = false;
            }
    
            try {
                df2.parse(timeStr);
            } catch (Exception e) {
                flag2 = false;
            }
    
            return flag1 || flag2;
    
        }
    
        public static boolean rowValid(List<Object> array) {
            if (isEmpty(array)) {
                return true;
            }
            for (Object object : array) {
                if (null == object) {
                    continue;
                }
                if (object.toString().trim().contains("*标题")
                        || object.toString().trim().contains("*要求")) {
                    return false;
                }
            }
            return true;
        }
    
        public static String getValue(int expectLength, List<Object> array) {
            if (array.size() <= expectLength) {
                return "";
            }
            String value = null == array.get(expectLength) ? "" : array.get(expectLength - 1).toString().trim();
            return value;
        }
    
        public static String getNotNullMsg(String value, Integer countNo, Integer rowNum, String filedName) {
            if (StringUtils.isNotBlank(value)) {
                return "";
            }
            StringBuilder msg = new StringBuilder();
            msg.append(countNo + ".第" + rowNum + "条" + filedName + "不能空;\n");
            return msg.toString();
        }
    
        public static String getUnValidMsg(Integer countNo, Integer rowNum, String filedName, String errorMsg) {
            StringBuilder msg = new StringBuilder();
            msg.append(countNo + ".第" + rowNum + "条" + filedName + errorMsg + ";\n");
            return msg.toString();
        }
    
        public static boolean isEmpty(List<Object> array) {
            if (CollectionUtils.isEmpty(array)) {
                return true;
            }
            boolean emptyFlag = true;
            for (Object object : array) {
                if (null != object && StringUtils.isNotBlank(object.toString())) {
                    emptyFlag = false;
                    break;
                }
            }
            return emptyFlag;
        }
    
    }