Easy-Trans反向翻译+Excel导入最佳实践

1、概述

实现用户excel上传、解析、对于用户输入的中文翻译为字典码或者id，实现用户输入的参数校验，最后入库。如果用户输入的参数有问题，返回校验结果给前端。

excel解析使用My-Excel组件，校验使用hibernate-validator，反向翻译组件使用easy-trans。

2、maven

不一定使用我指定的版本，也可以使用其他的替代组件，本文主要是给大家一个思路。

<!--请注意，如果用的新版本要用org.dromara的groupId untrans-driver需要和easy-trans主版本保持一致 -->
<dependency>
            <groupId>com.fhs-opensource</groupId>
            <artifactId>easy-trans-untrans-driver</artifactId>
            <version>2.2.15</version>
</dependency>

<!--解析excel的插件，也可以使用easy-excel -->
 <dependency>
            <groupId>com.github.liaochong</groupId>
            <artifactId>myexcel</artifactId>
            <version>4.4.2</version>
 </dependency>

<!--参数校验插件 -->
<dependency>
      <groupId>org.hibernate.validator</groupId>
      <artifactId>hibernate-validator</artifactId>
      <version>6.2.5.Final</version>
</dependency>

3、校验插件代码

首先自定义2个分组，第一个分组是对用户的必填和格式校验，第二个分组是对用户填写的数据反向翻译后，判断是否正常的校验(比如字典有男女他给个TS肯定翻译不到value，也要报错)。

public class ValidationGroups {
    public ValidationGroups() {
    }
   
    /**
     * excel导入第一遍监察
     */
    public @interface excelImportFirst {
    }
    /**
     * excel导入第二遍监察
     */
    public @interface excelImportSecond {
    }

}

接着自定义参数校验工具类

public class ValidateUtil {
    private static final Validator validator =
            Validation.buildDefaultValidatorFactory().getValidator();

    /**
     * 通过组来校验实体类
     */
    public static <T> String validate(T t, Class<?>... groups) {
        Set<ConstraintViolation<T>> constraintViolations = validator.validate(t, groups);
        if (constraintViolations.size() > 0) {
            StringBuilder validateError = new StringBuilder();
            for (ConstraintViolation<T> constraintViolation : constraintViolations) {
                validateError.append(constraintViolation.getMessage()).append(";");
            }
            return validateError.toString();
        }
        return null;
    }
    /**
     * 通过组来校验实体类
     */
    public static <T> String validate(List<T> objs, Class<?>... groups) {
        StringBuilder validateError = new StringBuilder();
        boolean hasError = false;
        for (int i = 0; i < objs.size(); i++) {
            String result = validate(objs.get(i),  groups);
            if(result!=null){
                validateError.append("第" + (i+1) + "行:" + result);
                hasError = true;
            }
        }
        return hasError ? validateError.toString() : null;
    }


}

4、easy-trans的yml配置

主要配置上db-type 支持mysql和postgresql

easy-trans:
  #启用redis缓存 如果不用redis请设置为false
  is-enable-redis: true
  #启用全局翻译(拦截所有responseBody进行自动翻译)，如果对于性能要求很高可关闭此配置
  is-enable-global: true
  #启用平铺模式
  is-enable-tile: true
  #字典缓存放到redis 微服务模式请开启
  dict-use-redis: true      
  #数据库类型指定，反向翻译使用      
  db-type: mysql

5、新增pojo，用于接收excel的数据

支持组合唯一键，比如财务部的王磊财务部是org表的name 王磊是user表的name。

比如：下面类里面的 domainId

支持字典反向翻译，比如男女之类的，下面类里面的modelType

当然也支持表里面的唯一键，比如身份证号码，手机号等。下面类里面的layeringId

package com.xhb.data.center.dgp.api.excel;

import com.fhs.core.trans.anno.UnTrans;
import com.fhs.core.trans.constant.UnTransType;
import com.github.liaochong.myexcel.core.annotation.ExcelColumn;
import com.xhb.data.center.api.validate.ValidationGroups;
import lombok.Data;
import org.hibernate.validator.constraints.Length;

import javax.validation.constraints.NotBlank;
import java.util.ArrayList;
import java.util.List;

/**
 * 模型表excel导入POJO
 */
@Data
public class ModelingLogicalTableImport {

    @NotBlank(
            message = "业务主题名称不能为空",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @ExcelColumn(title = "主题域(*)")
    private String subjectName;

    @NotBlank(
            message = "主题域名称不能为空",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @ExcelColumn(title = "业务主题(*)")
    private String domainName;

    @NotBlank(
            message = "数仓分层名称不能为空",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @ExcelColumn(title = "数仓分层名称(*)")
    private String layeringName;

    @NotBlank(
            message = "doris集群名称不能为空",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @ExcelColumn(title = "集群名称(*)")
    private String clusterName;

    @NotBlank(
            message = "doris库名称不能为空",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @ExcelColumn(title = "所在库(*)")
    private String databaseName;

    @NotBlank(
            message = "模型中文名称不能为空",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @Length(
            max = 64,
            message = "模型中文名称不能超过{max}位",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @ExcelColumn(title = "中文名称(*)")
    private String chName;

    @NotBlank(
            message = "模型英文名称不能为空",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @Length(
            max = 64,
            message = "模型英文名称不能超过{max}位",
            groups = {ValidationGroups.excelImportFirst.class}
    )
    @ExcelColumn(title = "英文名称(*)")
    private String enName;

    @ExcelColumn(title = "数据模型(*)")
    private String modelTypeName;

    @ExcelColumn(title = "副本数(*)")
    private Integer replicationNum;

    @ExcelColumn(title = "事实表类型(*)")
    private String factTableTypeName;

    @ExcelColumn(title = "业务过程中文名称(*)")
    private String processName;

    @ExcelColumn(title = "模型描述")
    private String description;

    @NotBlank(
            message = "集群匹配不到数据",
            groups = {ValidationGroups.excelImportSecond.class}
    )
    @UnTrans(type = UnTransType.SIMPLE,
            refs = {"clusterName", "databaseName"},
            tableName = "dgp_warehouse_doris_cluster c join dgp_warehouse_doris_database d on c.id=d.cluster_id",
            columns = {"c.name", "d.database_name"},
            uniqueColumn = "d.id"
    )
    private String clusterDatabaseId;


    @NotBlank(
            message = "主题域匹配不到数据",
            groups = {ValidationGroups.excelImportSecond.class}
    )
    @UnTrans(type = UnTransType.SIMPLE,
            refs = {"subjectName", "domainName"},
            tableName = "dgp_warehouse_plan_subject s join dgp_warehouse_plan_domain d on s.id=d.subject_id",
            columns = {"s.name", "d.name"},
            uniqueColumn = "d.id"
    )
    private String domainId;

    @NotBlank(
            message = "数据模型匹配不到数据",
            groups = {ValidationGroups.excelImportSecond.class}
    )
    @UnTrans(refs = "modelTypeName", type = UnTransType.DICTIONARY, dict = "dgp_modeling_model_type")
    private String modelType;

    @NotBlank(
            message = "数仓分层匹配不到数据",
            groups = {ValidationGroups.excelImportSecond.class}
    )
    @UnTrans(type = UnTransType.SIMPLE,
            refs = {"layeringName"},
            tableName = "dgp_warehouse_plan_layering",
            columns = {"name"}
    )
    private String layeringId;

    @UnTrans(type = UnTransType.SIMPLE,
            refs = {"subjectName", "domainName", "processName"},
            tableName = "dgp_warehouse_plan_subject s join dgp_warehouse_plan_domain d on s.id=d.subject_id join dgp_modeling_process p",
            columns = {"s.name", "d.name", "p.ch_name"}, uniqueColumn = "p.id"
    )
    private String processId;

    @UnTrans(type = UnTransType.DICTIONARY,
            refs = {"factTableTypeName"},
            dict = "dgp_modeling_fact_table_type"
    )
    private String factTableType;


    private List<ModelingLogicalColumnImport> columns = new ArrayList<>();
}