javax.validation + 正则表达式 实现数据导入校验

本例主要示例EasyExecl 数据导入，如何处理数据验证

1：引入pom依赖

<!--easyExecl--> <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.1.4</version> </dependency>

2：easyExecl核心类

import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.context.AnalysisContext; import com.alibaba.excel.event.AnalysisEventListener; import com.alibaba.excel.exception.ExcelAnalysisException; import com.alibaba.excel.util.StringUtils; import com.qingnian.spring.service.ExcelCheckManager; import com.qingnian.spring.utils.EasyExcelValiHelper; import com.qingnian.spring.utils.ExcelCheckErrDto; import com.qingnian.spring.utils.ExcelCheckResult; import lombok.Data; import lombok.EqualsAndHashCode; import java.lang.reflect.Field; import java.util.*; @Data @EqualsAndHashCode(callSuper=false) public class EasyExcelListener <T> extends AnalysisEventListener<T> { //成功结果集：检验通过数据集合 private List<T> successList = new ArrayList<>(); //失败结果集：检验失败数据集合 private List<ExcelCheckErrDto<T>> errList = new ArrayList<>(); //处理逻辑service private ExcelCheckManager<T> excelCheckManager; private List<T> list = new ArrayList<>(); //excel对象的反射类 private Class<T> clazz; public EasyExcelListener(ExcelCheckManager<T> excelCheckManager){ this.excelCheckManager = excelCheckManager; } //构造方法，注入真实的业务处类，Execl模板类 public EasyExcelListener(ExcelCheckManager<T> excelCheckManager,Class<T> clazz){ this.excelCheckManager = excelCheckManager; this.clazz = clazz; } /** * 读取Execl时，每读取一行行数据，会调用一次此方法，用以校验execl每条数据的规范 * @param t * @param analysisContext */ @Override public void invoke(T t, AnalysisContext analysisContext) { String errMsg; try { //根据excel数据实体中的javax.validation + 正则表达式来校验excel数据 (格式校验：字符长度，类型，格式，侏儒时间，手机号，身份证都可进行校验) errMsg = EasyExcelValiHelper.validateEntity(t); } catch (NoSuchFieldException e) { errMsg = "解析数据出错"; e.printStackTrace(); } if (!StringUtils.isEmpty(errMsg)){ //收集验证失败的数据 ExcelCheckErrDto excelCheckErrDto = new ExcelCheckErrDto(t, errMsg); errList.add(excelCheckErrDto); }else{ list.add(t); } //每1000条处理一次 if (list.size() > 1000){ //校验:对数据进行业务校验，再次甄别不满足业务逻辑的数据，具体实现由构造方法注入的业务实现类实现 ExcelCheckResult result = excelCheckManager.checkImportExcel(list); successList.addAll(result.getSuccessDtos()); errList.addAll(result.getErrDtos()); //清空，重新放入 list.clear(); } } //所有数据解析完成了 都会来调用 ：防止最后不足1000条的数据被遗漏 @Override public void doAfterAllAnalysed(AnalysisContext analysisContext) { ExcelCheckResult result = excelCheckManager.checkImportExcel(list); successList.addAll(result.getSuccessDtos()); errList.addAll(result.getErrDtos()); list.clear(); } /** * @description: 校验excel头部格式，必须完全匹配 * @param headMap 传入excel的头部（第一行数据）数据的index,name * @param context * @throws * @date 2019/12/24 19:27 */ @Override public void invokeHeadMap(Map<Integer, String> headMap, AnalysisContext context) { super.invokeHeadMap(headMap, context); if (clazz != null){ try { Map<Integer, String> indexNameMap = getIndexNameMap(clazz); Set<Integer> keySet = indexNameMap.keySet(); for (Integer key : keySet) { if (StringUtils.isEmpty(headMap.get(key))){ throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel"); } if (!headMap.get(key).equals(indexNameMap.get(key))){ throw new ExcelAnalysisException("解析excel出错，请传入正确格式的excel"); } } } catch (NoSuchFieldException e) { e.printStackTrace(); } } } /** * @description: 获取注解里ExcelProperty的value，用作校验excel * @param clazz * @throws * @date 2019/12/24 19:21 */ @SuppressWarnings("rawtypes") public Map<Integer,String> getIndexNameMap(Class clazz) throws NoSuchFieldException { Map<Integer,String> result = new HashMap<>(); Field field; Field[] fields=clazz.getDeclaredFields(); for (int i = 0; i <fields.length ; i++) { field=clazz.getDeclaredField(fields[i].getName()); field.setAccessible(true); ExcelProperty excelProperty=field.getAnnotation(ExcelProperty.class); if(excelProperty!=null){ int index = excelProperty.index(); String[] values = excelProperty.value(); StringBuilder value = new StringBuilder(); for (String v : values) { value.append(v); } result.put(index,value.toString()); } } return result; } }

3： 定义业务验证总接口

import com.qingnian.spring.utils.ExcelCheckResult; import java.util.List; /** * 业务验证总接口 */ public interface ExcelCheckManager<T> { /** * @description: 校验方法 * @param objects * @throws * @return com.cec.moutai.common.easyexcel.ExcelCheckResult * @author zhy * @date 2019/12/24 14:57 */ ExcelCheckResult checkImportExcel(List<T> objects); }

4：定义业务校验的返回类，包含成功集，和失败集

import lombok.Data; import java.util.ArrayList; import java.util.List; /** * 业务校验结果集 * @param <T> */ @Data public class ExcelCheckResult<T> { private List<T> successDtos; private List<ExcelCheckErrDto<T>> errDtos; public ExcelCheckResult(List<T> successDtos, List<ExcelCheckErrDto<T>> errDtos){ this.successDtos =successDtos; this.errDtos = errDtos; } public ExcelCheckResult(List<ExcelCheckErrDto<T>> errDtos){ this.successDtos =new ArrayList<>(); this.errDtos = errDtos; } }

5：业务逻辑校验错误实体类

import lombok.Data; /** * 业务逻辑校验错误实体类->保存单条错误信息 * @param <T> */ @Data public class ExcelCheckErrDto<T> { private T t; private String errMsg; public ExcelCheckErrDto(){} public ExcelCheckErrDto(T t, String errMsg){ this.t = t; this.errMsg = errMsg; } }

=============================================== 接下来我们针对一个表，对其导入的数据做验证

CREATE TABLE `good` ( `id` bigint(20) NOT NULL DEFAULT '0', `name` varchar(10) DEFAULT NULL COMMENT '物品名称', `price` bigint(20) DEFAULT NULL COMMENT '物品价格', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; @Data public class Good { private Long id; private int price; private String name; }

6：数据格式验证类

import com.alibaba.excel.annotation.ExcelProperty; import javax.validation.ConstraintViolation; import javax.validation.Validation; import javax.validation.Validator; import javax.validation.groups.Default; import java.lang.reflect.Field; import java.util.Set; public class EasyExcelValiHelper { private EasyExcelValiHelper(){} private static Validator validator = Validation.buildDefaultValidatorFactory().getValidator(); public static <T> String validateEntity(T obj) throws NoSuchFieldException { StringBuilder result = new StringBuilder(); Set<ConstraintViolation<T>> set = validator.validate(obj, Default.class); if (set != null && !set.isEmpty()) { for (ConstraintViolation<T> cv : set) { Field declaredField = obj.getClass().getDeclaredField(cv.getPropertyPath().toString()); ExcelProperty annotation = declaredField.getAnnotation(ExcelProperty.class); //拼接错误信息，包含当前出错数据的标题名字+错误信息 result.append(annotation.value()[0]+cv.getMessage()).append(";"); } } return result.toString(); } }

7：验证类可验证的Execl模板类说明

import com.alibaba.excel.annotation.ExcelProperty; import com.alibaba.excel.metadata.BaseRowModel; import com.qingnian.spring.utils.ExcelPatternMsg; import lombok.Data; import org.hibernate.validator.constraints.Length; import javax.validation.constraints.NotBlank; import javax.validation.constraints.Pattern; /* 任涛 -----专用 */ @Data public class GoodExecl extends BaseRowModel{ // index 对应execl 表头的下标，顺序由0起 value表示对应下标的表头名 @ExcelProperty(index = 0,value = "id") //自定义正则表达式，不满足验证，验证时会拼接错误信息 @Pattern(regexp = ExcelPatternMsg.NUMBER,message = ExcelPatternMsg.NUMBER_MSG) //定义字段不可为空 @NotBlank(message="主键不能为空") private String id; @ExcelProperty(index = 1,value = "价格") @Pattern(regexp = ExcelPatternMsg.DECIMAL,message = ExcelPatternMsg.DECIMAL_MSG) private String price; @ExcelProperty(index = 2,value = "物品名称") //Length 定义长度范围 min~max之间 @Length(min=0,max = 10) private String name; @ExcelProperty(index = 3,value = "备注信息") //用于错误数据导出时，提供错误信息 private String errMessger; }

正则检验帮助类：

public class ExcelPatternMsg { //只能输入整数或者小数 public static final String DECIMAL = "^[0-9]+\\.{0,1}[0-9]{0,2}$"; public static final String DECIMAL_MSG = "只能输入整数或者小数"; //日期格式 yyyy/MM/dd public static final String DATE1 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\/(((0[13578]|1[02])\\/(0[1-9]|[12][0-9]|3[01]))|"+ "((0[469]|11)\\/(0[1-9]|[12][0-9]|30))|(02\\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+ "((0[48]|[2468][048]|[3579][26])00))\\/02\\/29)$"; public static final String DATE1_MSG = "输入正确的日期格式：yyyy/MM/dd"; //日期格式 yyyy-MM-dd public static final String DATE2 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|"+ "((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+ "((0[48]|[2468][048]|[3579][26])00))-02-29)$"; public static final String DATE2_MSG = "输入正确的日期格式：yyyy-MM-dd"; //日期格式 yyyyMMdd public static final String DATE3 = "(([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+ "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+ "((0[48]|[2468][048]|[3579][26])00))0229)$"; public static final String DATE3_MSG = "输入正确的日期格式：yyyyMMdd"; //日期格式 yyyy-MM-dd HH:mm:ss public static final String DATE_TIME1 = "^((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})-(((0[13578]|1[02])-(0[1-9]|[12][0-9]|3[01]))|" + "((0[469]|11)-(0[1-9]|[12][0-9]|30))|(02-(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))-02-29))\\s+([0-1]?[0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$"; public static final String DATE_TIME1_MSG = "输入正确的日期格式：yyyy-MM-dd HH:mm:ss"; //日期格式 yyyy/MM/dd HH:mm:ss public static final String DATE_TIME2 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})\\/(((0[13578]|1[02])\\/(0[1-9]|[12][0-9]|3[01]))|"+ "((0[469]|11)\\/(0[1-9]|[12][0-9]|30))|(02\\/(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+ "((0[48]|[2468][048]|[3579][26])00))\\/02\\/29))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$"; public static final String DATE_TIME2_MSG = "输入正确的日期格式：yyyy/MM/dd HH:mm:ss"; //日期格式 yyyyMMddHHmmss public static final String DATE_TIME3 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+ "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|((0[48]|[2468][048]|[3579][26])00))0229))"+ "([0-1][0-9]|2[0-3])([0-5][0-9])([0-5][0-9])$"; public static final String DATE_TIME3_MSG = "输入正确的日期格式：yyyyMMddHHmmss"; //日期格式 yyyyMMddHHmmssSSS public static final String DATE_TIME4 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+ "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+ "((0[48]|[2468][048]|[3579][26])00))0229))([0-1][0-9]|2[0-3])([0-5][0-9])([0-5][0-9])([0-9]{3})$"; public static final String DATE_TIME4_MSG = "输入正确的日期格式：yyyyMMddHHmmssSSS"; //日期格式 yyyyMMdd HH:mm:ss public static final String DATE_TIME5 = "((([0-9]{3}[1-9]|[0-9]{2}[1-9][0-9]{1}|[0-9]{1}[1-9][0-9]{2}|[1-9][0-9]{3})(((0[13578]|1[02])(0[1-9]|[12][0-9]|3[01]))|"+ "((0[469]|11)(0[1-9]|[12][0-9]|30))|(02(0[1-9]|[1][0-9]|2[0-8]))))|((([0-9]{2})(0[48]|[2468][048]|[13579][26])|"+ "((0[48]|[2468][048]|[3579][26])00))0229))\\s([0-1][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$"; public static final String DATE_TIME5_MSG = "输入正确的日期格式：yyyyMMdd HH:mm:ss"; //数字和字母 public static final String NUMBER_LETTER = "^[a-z0-9A-Z]+$"; public static final String NUMBER_LETTER_MSG = "只能输入数字和字母"; //数字 public static final String NUMBER = "^[0-9]*$"; public static final String NUMBER_MSG = "只能输入数字"; }

8：业务接口继承业务校验总接口

import com.qingnian.spring.domain.Good; import com.qingnian.spring.domain.GoodExecl; public interface GoodService extends ExcelCheckManager<GoodExecl>{ Good selectOne(Long id); }

9：业务实现类加以实现

import com.qingnian.spring.dao.GoodDao; import com.qingnian.spring.domain.Good; import com.qingnian.spring.domain.GoodExecl; import com.qingnian.spring.service.GoodService; import com.qingnian.spring.utils.ExcelCheckErrDto; import com.qingnian.spring.utils.ExcelCheckResult; import lombok.extern.slf4j.Slf4j; import org.apache.commons.lang3.StringUtils; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.stereotype.Service; import java.util.ArrayList; import java.util.List; /* 任涛 -----专用 */ @Service @Slf4j public class GoodServiceImpl implements GoodService { //不合法名字 public static final String ERR_NAME = "史珍香"; @Autowired private GoodDao goodDao; @Override public Good selectOne(Long id) { return goodDao.selectOne(id); } @Override public ExcelCheckResult checkImportExcel(List<GoodExecl> objects) { //成功结果集 List<GoodExecl> successList = new ArrayList<>(); //错误数组 List<ExcelCheckErrDto<GoodExecl>> errList = new ArrayList<>(); for (GoodExecl GoodExecl : objects) { //错误信息 StringBuilder errMsg = new StringBuilder(); //根据自己的业务去做判断 if (ERR_NAME.equals(GoodExecl.getName())) errMsg.append("请输入正确的名字").append(";"); if (StringUtils.isEmpty(errMsg.toString())){ //这里有两个选择，1、一个返回成功的对象信息， successList.add(GoodExecl); //2、进行持久化操作 }else{ //添加错误信息 ，返回 errList.add(new ExcelCheckErrDto(GoodExecl,errMsg.toString())); } } return new ExcelCheckResult(successList,errList); } }

10：控制层调用

/** * 导入结果集获取 * @param file * @return * @throws IOException */ @PostMapping("/importExcel") public Result importExcel(@RequestParam MultipartFile file) throws IOException { Result result = new Result(); EasyExcelListener easyExcelListener = new EasyExcelListener(goodService,GoodExecl.class); EasyExcelFactory.read(file.getInputStream(),GoodExecl.class,easyExcelListener).sheet().doRead(); List<ExcelCheckErrDto<GoodExecl>> errList = easyExcelListener.getErrList(); List successList = easyExcelListener.getSuccessList(); int successCount=null==successList?0:successList.size(); int errCount=null==errList?0:errList.size(); int total=successCount+errCount; result.setMessage("验证通过"+successCount+"条，失败"+errCount+"条"); return result; } /** * 导入失败文件获取 * @param response * @param request * @param file * @throws IOException */ @PostMapping("/importExcel/errExecl") public void importExcelErrExecl(HttpServletResponse response,HttpServletRequest request, @RequestParam MultipartFile file) throws IOException { EasyExcelListener easyExcelListener = new EasyExcelListener(goodService,GoodExecl.class); EasyExcelFactory.read(file.getInputStream(),GoodExecl.class,easyExcelListener).sheet().doRead(); List<ExcelCheckErrDto<GoodExecl>> errList = easyExcelListener.getErrList(); if (!errList.isEmpty()){//如果包含错误信息就导出错误信息 List<GoodExecl> excelErrDtos = errList.stream().map(excelCheckErrDto -> { GoodExeclErr goodExeclErr = JSON.parseObject(JSON.toJSONString(excelCheckErrDto.getT()), GoodExeclErr.class); goodExeclErr.setErrMsg(excelCheckErrDto.getErrMsg()); return goodExeclErr; }).collect(Collectors.toList()); ExcelExportUtil.export(excelErrDtos,"商品表", "good",new GoodExecl(),response,request); } }