导入excel使用hibernate validator进行字段校验

最新推荐文章于 2023-03-03 21:28:04 发布

踏南天，碎凌霄

最新推荐文章于 2023-03-03 21:28:04 发布

阅读量1k

点赞数

本文链接：https://blog.csdn.net/qq_41834408/article/details/113970817

版权

导入excel使用hibernate validator进行字段校验

背景：平时项目中一般都会有excel导入的功能，那么就需要校验excel中的字段是否符合要求，保证功能的健壮性。

进入主题，我们使用hibernate validator 进行excel字段的校验。Hibernate Validator 是 Bean Validation 的参考实现 , Hibernate Validator 提供了 JSR 303 规范中所有内置 constraint 的实现，除此之外还有一些附加的 constraint。

Hibernate Validator 的使用

1. 引入jar包

可以单独引入 hibernate validator 的maven依赖，也可以使用springboot 的方式引入spring-boot-starter-web，该starter包里边包含hibernate-validator 包

2. Java对象添加约束注解

我们将excel导入系统中，使用Java对象进行接收，在Java对象属性添加约束注解。

例子：

@NotBlank

@NotNull

@Max(10)

@Min(0)

@Pattern(regexp=”[A-Z][a-z][0-9]”)

这样的基本注解有很多，详情可以查看hibernate-validator官网

有时候框架提供的校验注解（constraint）并不能满足我们的需求，比如我们需要验证一个字段存不存在数据库中；这时就需要用到自定义constraint。

编写自定义constraint

1）注解上必须有 @Constraint(validatedBy = {******.class}) 注解标注，validateBy 的值就是校验逻辑的实现类，实现类必须实现接口ConstraintValidator

2）自定义注解必须包含 message ,groups,payload 属性。

详情请看附件ChannelNbrIsExist.java注解类

3. 封装工具类在代码中校验

封装一个validationUtil 工具类，详情请看validationUtil.java

4. Service 中使用validationUtil工具类进行Java对象的校验，如果校验失败，会返回错误信息，开发者可以将错误信息返回前台，提示用户错误信息，重新上传excel文件。

package com.lc.walrus.channelcenter.util;

import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import javax.validation.ConstraintViolation;
import javax.validation.Validation;
import javax.validation.Validator;

import org.hibernate.validator.HibernateValidator;

import lombok.Data;

/**
 * 校验工具类
 * 
 * @author li
 */
public class ValidationUtil {

	/**
	 * 	快速结束模式 failFast(true)
	 */
	private static Validator validator = Validation.byProvider(HibernateValidator.class).configure().failFast(true)
			.buildValidatorFactory().getValidator();
	
	/**
	 *   校验对象
	 * @param t bean
	 * @param groups 校验组
	 * @return ValidResult
	 */
	public static <T> ValidResult validateBean(T t,Class<?>...groups) {
		ValidResult result = new ValidationUtil().new ValidResult();
		Set<ConstraintViolation<T>> violationSet = validator.validate(t, groups);
		boolean hasError = violationSet != null && violationSet.size() > 0;
		result.setHasErrors(hasError);
		if (hasError) {
            for (ConstraintViolation<T> violation : violationSet) {
                result.addError(violation.getPropertyPath().toString(), violation.getMessage());
            }
        }
        return result;
	}
	
	/**
	 * 	校验bean的某一个属性
	 * @param obj			bean
	 * @param propertyName  属性名称
	 * @return ValidResult
	 */
	public static <T> ValidResult validateProperty(T obj, String propertyName) {
        ValidResult result = new ValidationUtil().new ValidResult();
        Set<ConstraintViolation<T>> violationSet = validator.validateProperty(obj, propertyName);
        boolean hasError = violationSet != null && violationSet.size() > 0;
        result.setHasErrors(hasError);
        if (hasError) {
            for (ConstraintViolation<T> violation : violationSet) {
                result.addError(propertyName, violation.getMessage());
            }
        }
        return result;
    }

	/**
	 * 校验结果类
	 */
	@Data
	public class ValidResult {

		/**
		 * 是否有错误
		 */
		private boolean hasErrors;
		/**
		 * 错误信息
		 */
		private List<ErrorMessage> errors;
		
	    public ValidResult() {
            this.errors = new ArrayList<>();
        }
        public boolean hasErrors() {
            return hasErrors;
        }

        public void setHasErrors(boolean hasErrors) {
            this.hasErrors = hasErrors;
        }
        /**
         * 	获取所有验证信息
         * @return 集合形式
         */
        public List<ErrorMessage> getAllErrors() {
            return errors;
        }
        /**
         * 	获取所有验证信息
         * @return 字符串形式
         */
        public String getErrors(){
            StringBuilder sb = new StringBuilder();
            for (ErrorMessage error : errors) {
               // sb.append(error.getPropertyPath()).append(":").append(error.getMessage()).append(" ");
            	sb.append(error.getMessage());
            }
            return sb.toString();
        }

        public void addError(String propertyName, String message) {
            this.errors.add(new ErrorMessage(propertyName, message));
        }
	}

	@Data
	public class ErrorMessage {

		private String propertyPath;

		private String message;

		public ErrorMessage() {
		}

		public ErrorMessage(String propertyPath, String message) {
			this.propertyPath = propertyPath;
			this.message = message;
		}
	}

}

package com.lc.walrus.channelcenter.busi.resources.service.impl;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.InputStream;
import java.time.LocalDateTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.lang.StringUtils;
import org.jxls.reader.ReaderBuilder;
import org.jxls.reader.XLSReadStatus;
import org.jxls.reader.XLSReader;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import com.baomidou.mybatisplus.plugins.Page;
import com.lc.walrus.channelcenter.busi.base.Const;
import com.lc.walrus.channelcenter.busi.resources.constant.Constant;
import com.lc.walrus.channelcenter.busi.resources.dto.QryResourceDTO;
import com.lc.walrus.channelcenter.busi.resources.dto.ResourceDTO;
import com.lc.walrus.channelcenter.busi.resources.dto.ResourcesDTO;
import com.lc.walrus.channelcenter.busi.resources.entity.ChannelResourcesRel;
import com.lc.walrus.channelcenter.busi.resources.entity.ResourcesUsedInfo;
import com.lc.walrus.channelcenter.busi.resources.service.IChannelResourcesRelService;
import com.lc.walrus.channelcenter.busi.resources.service.IResourcesService;
import com.lc.walrus.channelcenter.busi.resources.vo.ResourceUsedInfoVO;
import com.lc.walrus.channelcenter.config.ChnviewConfig;
import com.lc.walrus.channelcenter.mapper.combusi.ComBusiMapper;
import com.lc.walrus.channelcenter.mapper.resources.ResourcesMapper;
import com.lc.walrus.channelcenter.mapper.resources.ResourcesUsedInfoMapper;
import com.lc.walrus.channelcenter.util.JxlsUtil;
import com.lc.walrus.channelcenter.util.ValidationUtil;
import com.lc.walrus.channelcenter.util.ValidationUtil.ValidResult;

import lombok.extern.slf4j.Slf4j;

/**
 * @Auther: 
 * @Date: 2019/7/22
 * @Description: 
 */
@Service
@Transactional
@Slf4j
public class ResourcesServiceImpl implements IResourcesService {

	private static final String TEMPLATE_FILE_NAME_XML = "ChannelResourcesRel.xml";
	private static final String TEMPLATE_FILE_NAME = "resourcesUsed.xls";
	private static final String DOWNLOAD_FILE_NAME = "资源领用导入模板.xls";

	@Autowired
	private IChannelResourcesRelService channelResourcesRelService;

	@Autowired
	private ResourcesMapper resourcesMapper;

	@Autowired
	private ResourcesUsedInfoMapper resourcesUsedInfoMapper;

	@Autowired
	private ComBusiMapper comBusiMapper;

	@Override
	public Page<Map<String, Object>> qryResUsedPageList(Page<Map<String, Object>> page, QryResourceDTO qryResourceDTO) {
		if (null == page) {
			page = new Page<Map<String, Object>>(Const.DEF_CURRENT_PAGE, Const.DEF_PAGE_SIZE);
		}
		return page.setRecords(resourcesMapper.qryResUsedPageList(page, qryResourceDTO));
	}

	@Override
	public List<Map<String, Object>> queryResourceType(String resourceType) {
		return resourcesMapper.queryResourceType(resourceType);
	}

	@Override
	public List<Map<String, Object>> qryResourcesByType(ResourcesDTO resources) {
		return resourcesMapper.qryResourcesByType(resources);
	}

	@Override
	public List<Map<String, Object>> qryTempResources(ResourcesDTO resources) {
		return resourcesMapper.qryTempResources(resources);
	}

	@Override
	public Long saveResourceAndGoPay(ResourcesDTO resources) throws Exception {
		// 生成资源领用单主键 和 关联主键
		Long resourcesUsedInfoId = comBusiMapper.getCommonBusiSeqId(new HashMap());
		String relCode = comBusiMapper.getCommonBusiSeqId(new HashMap()) + "";
		ResourcesUsedInfo info = new ResourcesUsedInfo();
		info.setId(resourcesUsedInfoId);
		info.setRelCode(relCode);
		info.setTypesName(getResourcesName(resources.getSelectResources()));
		info.setStatusCd(Constant.TO_PAY + "");
		BeanUtils.copyProperties(info, resources);
		resourcesUsedInfoMapper.insert(info);

		List<ChannelResourcesRel> list = new ArrayList<>();
		ChannelResourcesRel channelResourcesRel;
		List<ResourceDTO> resourceSelList = resources.getResourceSelList();
		if (resourceSelList != null && resourceSelList.size() > 0) {
			for (ResourceDTO resource : resourceSelList) {
				Long channelResourcesRelId = comBusiMapper.getCommonBusiSeqId(new HashMap());
				channelResourcesRel = new ChannelResourcesRel();
				channelResourcesRel.setId(channelResourcesRelId);
				channelResourcesRel.setRelCode(relCode);
				channelResourcesRel.setChannelNbr(resources.getChannelNbr());
				channelResourcesRel.setResourceId(resource.getResourceId());
				channelResourcesRel.setResourcePriceReal(resource.getResourcePrice());
				channelResourcesRel.setResourceNumReal(resource.getResourceNumReal());
				channelResourcesRel.setResourceTotal(resource.getResourceTotal());
				channelResourcesRel.setCreateStaff(resources.getCreateStaff());
				channelResourcesRel.setUpdateStaff(resources.getUpdateStaff());
				list.add(channelResourcesRel);
			}
		}
		channelResourcesRelService.insertBatch(list);

		return resourcesUsedInfoId;
	}

	@Override
	public void useResources(String resourceUsedId) {
		ResourcesUsedInfo info = resourcesUsedInfoMapper.selectById(resourceUsedId);
		info.setStatusCd(Constant.USEING + "");
		info.setStatusDate(LocalDateTime.now());
		resourcesUsedInfoMapper.updateById(info);
	}

	private String getResourcesName(String[] selectResources) {
		List<String> names = resourcesMapper.qryResourcesNames(selectResources);
		return StringUtils.join(names, ",");
	}

	@Override
	public ResourceUsedInfoVO qryResUsedDetail(String resourceUsedId) {
		return resourcesMapper.qryResUsedDetail(resourceUsedId);
	}

	@Override
	public void doPayResource(String resourceUsedId) {
		ResourcesUsedInfo info = resourcesUsedInfoMapper.selectById(resourceUsedId);
		info.setPayTime(LocalDateTime.now());
		info.setStatusCd(Constant.TO_USE + "");
		resourcesUsedInfoMapper.updateById(info);
	}

	@Override
	public Long retResources(ResourcesDTO resources) {
		String resourceUsedId = resources.getResourceUsedId();
		ResourcesUsedInfo info = resourcesUsedInfoMapper.selectById(resourceUsedId);
		info.setReturnNum(resources.getResourceNum());
		info.setReturnMoney(resources.getReturnMoney());
		info.setStatusCd(Constant.TO_REFUND + "");
		// 如果是赊付,则更新为 回退完成 状态
		if (info.getIsCredit().equals(Constant.CREDIT_Y + "")) {
			info.setStatusCd(Constant.RETURND + "");
		}
		info.setStatusDate(LocalDateTime.now());
		info.setUpdateDate(LocalDateTime.now());
		info.setUpdateStaff(resources.getUpdateStaff());
		resourcesUsedInfoMapper.updateById(info);

		List<ResourceDTO> resourcesList = resources.getResources();
		List<ChannelResourcesRel> list = new ArrayList<>();
		for (ResourceDTO res : resourcesList) {
			ChannelResourcesRel channelResourcesRel = channelResourcesRelService
					.selectById(res.getChannelResourcesRelId());
			channelResourcesRel.setReturnNum(res.getReturnNum());
			channelResourcesRel.setReturnMoney(res.getReturnMoney());
			channelResourcesRel.setReturnDate(LocalDateTime.now());
			channelResourcesRel.setUpdateDate(LocalDateTime.now());
			channelResourcesRel.setUpdateStaff(resources.getUpdateStaff());
			list.add(channelResourcesRel);
		}
		channelResourcesRelService.updateBatchById(list);
		return info.getId();
	}

	@Override
	public Long refundResources(ResourcesDTO resources) {

		String resourceUsedId = resources.getResourceUsedId();
		ResourcesUsedInfo info = resourcesUsedInfoMapper.selectById(resourceUsedId);
		// 减去回退的资源数量
		info.setResourceNum(info.getResourceNum() - info.getReturnNum());
		// 减去回退的款项
		info.setResourceTotal(info.getResourceTotal().subtract(info.getReturnMoney()));
		// 状态 回退完成
		info.setStatusCd(Constant.RETURND + "");
		// 状态时间
		info.setStatusDate(LocalDateTime.now());
		// update date
		info.setUpdateDate(LocalDateTime.now());
		// update staff
		info.setUpdateStaff(resources.getUpdateStaff());
		resourcesUsedInfoMapper.updateById(info);

		List<ResourceDTO> resourcesList = resources.getResources();
		List<ChannelResourcesRel> list = new ArrayList<>();
		for (ResourceDTO res : resourcesList) {
			ChannelResourcesRel channelResourcesRel = channelResourcesRelService
					.selectById(res.getChannelResourcesRelId());
			channelResourcesRel.setResourceNumReal(res.getResourceNumReal() - res.getReturnNum());
			channelResourcesRel.setResourceTotal(res.getResourceTotal().subtract(res.getReturnMoney()));
			channelResourcesRel.setStatusCd(Constant.RETURND + "");
			channelResourcesRel.setStatusDate(LocalDateTime.now());
			channelResourcesRel.setUpdateDate(LocalDateTime.now());
			channelResourcesRel.setUpdateStaff(resources.getUpdateStaff());
			list.add(channelResourcesRel);
		}
		channelResourcesRelService.updateBatchById(list);
		return info.getId();
	}

	@Override
	public void downloadExcel() {
		Page<Map<String, Object>> page = new Page<>(1, Integer.MAX_VALUE);
		List<Map<String, Object>> records = resourcesMapper.queryResourcesByPage(page);
		JxlsUtil.exportListDataA(records, TEMPLATE_FILE_NAME, DOWNLOAD_FILE_NAME);
	}

	@Override
	public List<ChannelResourcesRel> getExcelData(MultipartFile excelFile) {
		List<ChannelResourcesRel> channelResourcesRelList = new ArrayList<ChannelResourcesRel>();
		try {
			InputStream inputXML = JxlsUtil.class
					.getResourceAsStream(ChnviewConfig.jxlsWorkSheetXMLPath + File.separator + TEMPLATE_FILE_NAME_XML);
			XLSReader mainReader = ReaderBuilder.buildFromXML(inputXML);
			InputStream inputXLS = new BufferedInputStream(excelFile.getInputStream());
			ChannelResourcesRel channelResourcesRel = new ChannelResourcesRel();
			Map<String, Object> beans = new HashMap<String, Object>();
			beans.put("channelResourcesRel", channelResourcesRel);
			beans.put("channelResourcesRelList", channelResourcesRelList);
			XLSReadStatus readStatus = mainReader.read(inputXLS, beans);
			if (readStatus.isStatusOK()) {
				System.out.println("jxls读取Excel成功！");
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return channelResourcesRelList;
	}
	
	

	@Override
	public Map<String, Object> checkData(List<ChannelResourcesRel> channelResourcesRelList) {
		
		Map<String, Object> resultMap = new HashMap<String, Object>();
		if(channelResourcesRelList == null || channelResourcesRelList.size() == 0) {
			resultMap.put("error", "导入的excel为空");
			return resultMap;
		}
		for (int i = 0 ; i < channelResourcesRelList.size() ; i ++) {
			ChannelResourcesRel rel = channelResourcesRelList.get(i);
			ValidResult result = ValidationUtil.validateBean(rel);
			
			if(result.hasErrors()) {
				String error = "第" + (i + 3) + "行" + result.getErrors();
				resultMap.put("error", error);
				System.out.println(error);
				return resultMap;
			}
		}
		
		return null;
	}

	@Override
	public Map<String, Object> fetchTempResources(List<ChannelResourcesRel> channelResourcesRelList) {
		Map<String, Object> result = new HashMap<String, Object>();
		// 提取出 渠道信息
		ChannelResourcesRel rel = channelResourcesRelList.get(0);
		Map<String, Object> channelInfo = resourcesMapper.queryChannelInfoByChannelNbr(rel.getChannelNbr());
		result.put("channelInfo", channelInfo);
		
		//提取出资源列表
		Set<ChannelResourcesRel> set = new HashSet<ChannelResourcesRel>();
		for (ChannelResourcesRel channelResourcesRel : channelResourcesRelList) {
			set.add(channelResourcesRel);
		}
		List<Map<String, Object>> tempResources = resourcesMapper.qryTempResourcesBySet(set);
		result.put("resources", tempResources);
		return result;
	}


}

package com.lc.walrus.channelcenter.busi.resources.validation;

import javax.annotation.PostConstruct;
import javax.validation.ConstraintValidator;
import javax.validation.ConstraintValidatorContext;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import com.walrus.channelcenter.mapper.resources.ResourcesMapper;

/**
 * 校验渠道编码是否存在
 * @author l
 */
@Component
public class ChannelNbrValidator implements ConstraintValidator<ChannelNbrIsExist, String>{

	@Autowired
	private ResourcesMapper resourcesMapper;
	
	private static ResourcesMapper sResourcesMapper;
	
	/**
	 * 校验逻辑
	 */
	@Override
	public boolean isValid(String value, ConstraintValidatorContext context) {
		Integer result = sResourcesMapper.checkChannelNbrIsExists(value);
		return result > 0;
	}

	@PostConstruct
	public void init() {
		sResourcesMapper = resourcesMapper;
	}

}

package com.walrus.channelcenter.busi.resources.validation;

import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;

import javax.validation.Constraint;
import javax.validation.Payload;

/**
 * 检验excel 中 ChannelNbr是否存在数据库
 * @author 
 */
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Constraint(validatedBy = ChannelNbrValidator.class)
public @interface ChannelNbrIsExist {
	
	String message() default "渠道编码不存在";
	/**
     * 必须的属性
     * 用于分组校验
     */
    Class<?>[] groups() default {};

    Class<? extends Payload>[] payload() default {};
}