策略模式-实际应用案例二

策略接口类：DataSourceStrategy.java

package cn.getech.data.manager.service.strategy;

import cn.getech.data.manager.model.dto.QualityRuleFieldDto;
import cn.getech.data.manager.model.dto.QueryDto;
import cn.getech.data.manager.model.vo.RuleDataRowVO;
import cn.getech.data.manager.model.vo.RuleFieldVO;

import java.math.BigDecimal;
import java.util.List;

public interface DataSourceStrategy {


    /**
     * @Description 获取数据库名字
     * @Author  chengweiping
     * @Date   2020/10/19 17:46
     */
    List<String> getDbNameList(Integer connectId)throws Exception;


    /**
    * @Description 根据数据源与数据库名称获取表名列表
    * @Author  chengweiping
    * @Date   2020/10/10 14:11
    */
    public List<String>  getTableNameList(Integer connectId, String databaseName, String tableName) throws Exception;


    /**
     * @Description 获取规则字段列表
     * @Author  chengweiping
     * @Date   2020/10/13 17:46
     */
    public List<RuleFieldVO> getFieldList(QualityRuleFieldDto qualityRuleFieldDto)  throws  Exception;


    /**
     * @Description 获取表大小
     * @Author  chengweiping
     * @Date   2020/10/13 23:02
     */
    BigDecimal getTotalTableSize(QueryDto queryDto) throws Exception;


    /**
    * @Description 获取表行数
    * @Author  chengweiping
    * @Date   2020/10/10 16:15
     * @param query
    */
    public Long  getTableRowNum(QueryDto query) throws Exception;






    /**
    * @Description 获取字段的空值数据
    * @Author  chengweiping
    * @Date   2020/10/14 15:16
    */
    public Long getFieldNullNum(QueryDto queryDto) throws Exception;


    /**
    * @Description 获取重复值个数
    * @Author  chengweiping
    * @Date   2020/10/14 15:20
    */
    public Long getFieldRepeatNum(QueryDto queryDto) throws Exception;

    /**
    * @Description 获取唯一值个数
    * @Author  chengweiping
    * @Date   2020/10/14 15:32
    */
    Long getFieldUniqueNum(QueryDto queryDto) throws Exception;

    /**
    * @Description 获取字段汇总值
    * @Author  chengweiping
    * @Date   2020/10/14 15:58
    */
    BigDecimal getFieldSumNum(QueryDto queryDto) throws Exception;

    /**
    * @Description 获取字段的最小值
    * @Author  chengweiping
    * @Date   2020/10/14 16:05
    */
    BigDecimal getFieldMinNum(QueryDto queryDto) throws Exception;

    /**
    * @Description 获取字段的最大值
    * @Author  chengweiping
    * @Date   2020/10/14 16:05
    */
    BigDecimal getFieldMaxNum(QueryDto queryDto) throws Exception;

    /**
    * @Description 获取字段平均值
    * @Author  chengweiping
    * @Date   2020/10/14 16:07
    */
    BigDecimal getFieldAvgNum(QueryDto queryDto) throws Exception;


   /**
   * @Description 获取自定义sql结果
   * @Author  chengweiping
   * @Date   2020/10/19 23:03
   */
    BigDecimal getCustomeSqlResult(Integer connectId, String dbName, String tableName, String sql) throws Exception;

    /**
     * @Description 获取自定义sql结果
     * @Author  chengweiping
     * @Date   2020/10/19 23:03
     */
    String getCustomeSqlField(Integer connectId, String dbName, String tableName, String sql) throws Exception;


    /**
    * @Description 获取表规则行数
    * @Author  chengweiping
    * @Date   2020/10/20 15:42
    */
    List<RuleDataRowVO> getTableNullRowList(QueryDto queryDto) throws Exception;

    /**
    * @Description 获取唯一值行数
    * @Author  chengweiping
    * @Date   2020/10/20 18:11
    */
    List<RuleDataRowVO> getTableUniqueRowList(QueryDto query)throws Exception;

    /**
    * @Description 获取重复值得数据行
    * @Author  chengweiping
    * @Date   2020/10/20 18:15
    */
    List<RuleDataRowVO> getTableRepeatRowList(QueryDto query) throws Exception;


    /**
    * @Description  获取表的分区字段列表
    * @Author  chengweiping
    * @Date   2020/10/22 10:33
    */
    List<String> getTablePartitionList(QueryDto query) throws Exception;


}

 

策略工厂类：

DataSourceStrategyFactory.java

package cn.getech.data.manager.service.factory;

import cn.getech.data.manager.service.strategy.DataSourceStrategy;

import java.util.concurrent.ConcurrentHashMap;

/**
 * @ClassName DataSourceStrategyFactory
 * @Description   表明服务工厂
 * @Author Getech
 * @Date 2020/10/10 14:06
 */
public class DataSourceStrategyFactory {

    private static ConcurrentHashMap<Integer, DataSourceStrategy>  serviceMap=new ConcurrentHashMap<>();




    public static DataSourceStrategy getServiceByType(Integer typeId){
        return serviceMap.get(typeId);
    }

    public static void setService(Integer typeId, DataSourceStrategy tableNameStrategy){
            serviceMap.put(typeId, tableNameStrategy);
    }
}

 

枚举类：DataSourceTypeEnum.java

package cn.getech.data.manager.constant;

/**
 * @ClassName DataSourceTypeEnum
 * @Description TODO
 * @Author Getech
 * @Date 2020/10/10 14:22
 */
public enum  DataSourceTypeEnum {


    HIVE(0,"hive"),

    MYSQL(1,"mysql"),

    ORACLE(2,"oracle"),

    SQLSERVER(3,"sqlserver"),

    POSTGRESQL(4,"postgresql");


    private Integer typeId;

    private String name;


    DataSourceTypeEnum(Integer typeId, String name) {
        this.typeId = typeId;
        this.name = name;
    }

    public Integer getTypeId() {
        return typeId;
    }

    public void setTypeId(Integer typeId) {
        this.typeId = typeId;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

 

策略实现类：DataSourceMysqlStrategy.java

package cn.getech.data.manager.service.strategy.impl;

import cn.getech.data.development.entity.ConfConnect;
import cn.getech.data.development.service.ConfConnectService;
import cn.getech.data.development.utils.MysqlTableUtil;
import cn.getech.data.manager.constant.DataSourceTypeEnum;
import cn.getech.data.manager.model.dto.QualityRuleFieldDto;
import cn.getech.data.manager.model.dto.QueryDto;
import cn.getech.data.manager.model.vo.RuleDataRowVO;
import cn.getech.data.manager.model.vo.RuleFieldVO;
import cn.getech.data.manager.service.factory.DataSourceStrategyFactory;
import cn.getech.data.manager.service.strategy.DataSourceStrategy;
import cn.getech.data.manager.service.strategy.MySqlTemplate;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * @ClassName TableNameHiveService
 * @Description 获取表名hive 实现
 * @Author Getech
 * @Date 2020/10/10 14:16
 */
@Service
@Slf4j
public class DataSourceMysqlStrategy implements DataSourceStrategy, InitializingBean {



    @Autowired
    private ConfConnectService  confConnectService;

    @Autowired
    private MySqlTemplate mysqlTemplate;


    @Override
    public List<String> getTableNameList(Integer connectId, String database,String tableName) throws Exception {
        String sql="SELECT table_name FROM information_schema.tables WHERE table_schema='%s' AND table_type='base table'";
        String tableNameSql=String.format(sql,database);
        List<String> tableNameList=new ArrayList<>();
        MysqlTableUtil mysqlTableUtil =null;
        try{
            QueryDto query=new QueryDto();
            query.setConnectId(connectId);
            query.setDbName(database);
            query.setTableName(tableName);
          //创建jdbc连接
            mysqlTableUtil = mysqlTemplate.getMySqlTableUtil(query);
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("数据库连接失败");
        }
        ResultSet result=null;
        try{
            //执行统计sql，进行汇总
            result=mysqlTableUtil.getResultSet(tableNameSql);
            while (result.next()) {
                if (result != null) {
                    Object object= result.getObject(1);
                    if(object!=null){
                        String tableNameDB=result.getString(1);
                        tableNameList.add(tableNameDB);
                    }

                }
            }

        }catch (Exception e){
            e.printStackTrace();
            throw new Exception(e.getMessage());
        }finally {
            if(result!=null){
                result.close();
            }
            if(mysqlTableUtil!=null){
                mysqlTableUtil.close();
            }
        }

        return tableNameList;
    }

    @Override
    public List<String> getDbNameList(Integer connectId) throws Exception {
        ConfConnect confConnect= confConnectService.getById(connectId);
        List<String> dbNameList=new ArrayList<>();
        if(confConnect!=null){
            dbNameList.add(confConnect.getDbname());
        }
        return dbNameList;
    }


    /**
    * @Description 获取表行数
    * @Author  chengweiping
    * @Date   2020/10/13 22:58
    */
    @Override
    public Long getTableRowNum(QueryDto query) throws Exception {
        //统计表总数的sql
        String sql = "select count(1) as totalCount  from %s ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql="select count(1) as totalCount  from %s where 1=1 and  " + query.getSqlWhere();
        }
        //统计表总数的sql
        String formatSql = String.format(sql, query.getTableName());
        query.setSql(formatSql);
        Long value= mysqlTemplate.getLongValue(query);
        return value;
    }


    /**
    * @Description  获取表字段列表
    * @Author  chengweiping
    * @Date   2020/10/13 22:58
    */
    @Override
    public List<RuleFieldVO> getFieldList(QualityRuleFieldDto qualityRuleFieldDto) throws  Exception {
        Integer connectId= qualityRuleFieldDto.getConnectId();
        String sql= "select column_name as columnName,data_type as dataType from information_schema.columns where   table_schema='%s' and table_name='%s' ";

        List<RuleFieldVO>  ruleFieldVOList =new ArrayList<>();
        MysqlTableUtil mysqlTableUtil =null;
        String tableFieldNameSql=null;
        try{

            String database=qualityRuleFieldDto.getDatabaseName();
            String tableName=qualityRuleFieldDto.getTableName();
            tableFieldNameSql=String.format(sql,database,tableName);
            System.out.println("获取表字段的sql:"+tableFieldNameSql);
            QueryDto queryDto=new QueryDto();
            queryDto.setConnectId(connectId);
            queryDto.setDbName(database);
            mysqlTableUtil = mysqlTemplate.getMySqlTableUtil(queryDto);
        }catch (Exception e){
            e.printStackTrace();
            throw new Exception("数据库连接失败");
        }
        ResultSet result=null;
        try{
            //执行统计sql，进行汇总
            result=mysqlTableUtil.getResultSet(tableFieldNameSql);
            while (result.next()) {
                if (result != null) {
                    Object fieldNameObject=result.getObject(1);
                    if(fieldNameObject!=null){
                        String fieldName=result.getString(1);
                        String fieldType=result.getString(2);
                        RuleFieldVO ruleFieldVO=new RuleFieldVO();
                        ruleFieldVO.setFieldName(fieldName);
                        ruleFieldVO.setFieldType(fieldType.toLowerCase());
                        ruleFieldVOList.add(ruleFieldVO);
                    }

                }
            }

        }catch (Exception e){
            e.printStackTrace();
            throw new Exception(e.getMessage());
        }finally {
            if(result!=null){
                result.close();
            }
            if(mysqlTableUtil!=null){
                mysqlTableUtil.close();
            }
        }

        return ruleFieldVOList;
    }


    /**
    * @Description 获取表大小
    * @Author  chengweiping
    * @Date   2020/10/13 22:59
    */
    @Override
    public BigDecimal getTotalTableSize(QueryDto query) throws Exception {
        String sql=" SELECT TRUNCATE(data_length,0) AS dataSize FROM information_schema.tables WHERE table_schema='%s' AND table_name='%s' ";
        String formatSql = String.format(sql, query.getDbName(),query.getTableName());
        query.setSql(formatSql);
        BigDecimal value= mysqlTemplate.getBigDecimalValue(query);
        return value;

    }

    @Override
    public Long getFieldNullNum(QueryDto query) throws Exception {
        String sql=" select count(1) as totalCount  from %s where  %s is null or %s='' ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=sql+"  and  "+ query.getSqlWhere();
        }
        String formatSql = String.format(sql, query.getTableName(),query.getFieldName(),query.getFieldName());
        query.setSql(formatSql);
        Long value= mysqlTemplate.getLongValue(query);
        return value;

    }

    @Override
    public Long getFieldRepeatNum(QueryDto query) throws Exception {
        String sql="select count(1) as totalCount from ( select %s,count(*) as  toatlCountTemp from %s where  %s is not null and %s!=''  group by  %s having toatlCountTemp > 1) t ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql="select count(1) as totalCount from ( select %s,count(*) as  toatlCountTemp from %s where  %s is not null and %s!='' and  " + query.getSqlWhere()+ " group by  %s having toatlCountTemp > 1) t  ";
        }
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName,fieldName,fieldName);
        query.setSql(formatSql);
        Long value= mysqlTemplate.getLongValue(query);
        return value;
    }


    @Override
    public Long getFieldUniqueNum(QueryDto query) throws Exception {
        String sql="select count(1) as totalCount from ( select %s,count(*) as  toatlCountTemp from %s where  %s is not null and %s!='' group by  %s having toatlCountTemp =1) t ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql="select count(1) as totalCount from ( select %s,count(*) as  toatlCountTemp from %s where  %s is not null and %s!='' and " + query.getSqlWhere()+ " group by  %s having toatlCountTemp =1) t  ";
        }
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName,fieldName,fieldName);
        query.setSql(formatSql);
        Long value= mysqlTemplate.getLongValue(query);
        return value;
    }


    @Override
    public BigDecimal getFieldSumNum(QueryDto query) throws Exception {
        String sql=" select sum(%s) as totalCount  from %s  ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql="select sum(%s) as totalCount  from %s where  " + query.getSqlWhere();
        }
        //统计表sum值sql
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName);
        query.setSql(formatSql);
        BigDecimal result= mysqlTemplate.getBigDecimalValue(query);
        BigDecimal value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        return value;
    }


    @Override
    public BigDecimal getFieldMinNum(QueryDto query) throws Exception {
        String sql=" select min(%s) as minValue  from %s  ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=" select min(%s) as minValue  from %s  where " + query.getSqlWhere();
        }
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName);
        query.setSql(formatSql);
        BigDecimal result= mysqlTemplate.getBigDecimalValue(query);
        BigDecimal value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        return value;
    }

    @Override
    public BigDecimal getFieldMaxNum(QueryDto query) throws Exception {
        String sql=" select max(%s) as maxValue  from %s  ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=" select max(%s) as maxValue  from %s   where " + query.getSqlWhere();
        }
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName);
        query.setSql(formatSql);
        BigDecimal result= mysqlTemplate.getBigDecimalValue(query);
        BigDecimal value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        return value;
    }


    @Override
    public BigDecimal getFieldAvgNum(QueryDto query) throws Exception {
        String sql=" select avg(%s) as avgCount  from %s  ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=" select avg(%s) as avgCount  from %s where " + query.getSqlWhere();
        }
        //计算sql
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName);
        query.setSql(formatSql);
        BigDecimal result= mysqlTemplate.getBigDecimalValue(query);
        BigDecimal value=new BigDecimal(0);
        if(result!=null){
            value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        }

        return value;
    }




    @Override
    public BigDecimal getCustomeSqlResult(Integer connectId, String dbName, String tableName, String sql) throws Exception {
        QueryDto query=new QueryDto();
        query.setConnectId(connectId);
        query.setDbName(dbName);
        query.setTableName(tableName);
        query.setSql(sql);
        BigDecimal result= mysqlTemplate.getBigDecimalValue(query);
        BigDecimal value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        System.out.println("自定义统计sql值:"+value);
        return value;
    }

    @Override
    public String getCustomeSqlField(Integer connectId, String dbName, String tableName, String sql) throws Exception {
        QueryDto query=new QueryDto();
        query.setConnectId(connectId);
        query.setDbName(dbName);
        query.setTableName(tableName);
        query.setSql(sql);
        String result= mysqlTemplate.getFirstFieldMetaValue(query);
        System.out.println("mysql自定义统计sql字段:"+result);
        return result;
    }

    @Override
    public List<RuleDataRowVO> getTableNullRowList(QueryDto query) throws Exception {
        List<RuleDataRowVO> rowList=new ArrayList<>();
        String sql=" select *  from %s where  %s is null or %s='' ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=" select *  from %s where  %s is null or %s=''  and  " + query.getSqlWhere();
        }
        String field=query.getFieldName();
        String tableName=query.getTableName();
        //空值sql
        String  formatSql=String.format(sql,tableName,field,field);
        query.setSql(formatSql);
        rowList=mysqlTemplate.getRuleRowList(query);

        return rowList;
    }



    @Override
    public List<RuleDataRowVO> getTableUniqueRowList(QueryDto query) throws Exception {
        List<RuleDataRowVO> rowList=new ArrayList<>();
        String sql="select *  from  %s where %s in ( select %s from %s  where  %s is not null and %s!='' group by  %s having count(%s) =1) ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=" select *  from  %s where %s in ( select %s from %s  where  %s is not null and %s!='' and " + query.getSqlWhere() +"  group by  %s having count(%s) =1) ";
        }
        String field=query.getFieldName();
        String tableName=query.getTableName();
        //统计表唯一值sql
        String  formatSql=String.format(sql,tableName,field,field,tableName,field,field,field,field);
        query.setSql(formatSql);
        rowList=mysqlTemplate.getRuleRowList(query);
        return rowList;
    }

    @Override
    public List<RuleDataRowVO> getTableRepeatRowList(QueryDto query) throws Exception {
        List<RuleDataRowVO> rowList=new ArrayList<>();
        String sql="select *  from  %s where %s in ( select %s from %s  where  %s is not null and %s!=''  group by  %s having count(%s) > 1) ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=" select *  from  %s where %s in ( select %s from %s  where  %s is not null and %s!=''   and  " + query.getSqlWhere() +" group by  %s having count(%s) > 1  ";
        }
        String field=query.getFieldName();
        String tableName=query.getTableName();
        //统计表唯一值sql
        String  formatSql=String.format(sql,tableName,field,field,tableName,field,field,field,field);
        query.setSql(formatSql);
        rowList=mysqlTemplate.getRuleRowList(query);
        return rowList;
    }

    @Override
    public List<String> getTablePartitionList(QueryDto query) throws Exception {
        return null;
    }





    @Override
    public void afterPropertiesSet() throws Exception {
        DataSourceStrategyFactory.setService(DataSourceTypeEnum.MYSQL.getTypeId(),this);
    }
}

 

策略实现类：DataSourceHiveStrategy.java

package cn.getech.data.manager.service.strategy.impl;

import cn.getech.data.development.config.properties.BdpJobConfig;
import cn.getech.data.development.config.properties.DataDevelopmentConfig;
import cn.getech.data.development.constant.PermissionTypeEnum;
import cn.getech.data.development.entity.TableFieldInfo;
import cn.getech.data.development.entity.TableInfo;
import cn.getech.data.development.mapper.TableInfoMapper;
import cn.getech.data.development.service.TableFieldInfoService;
import cn.getech.data.development.service.TableInfoService;
import cn.getech.data.development.utils.DataPermissionUtil;
import cn.getech.data.development.utils.HdfsUtil;
import cn.getech.data.intelligence.common.utils.Query;
import cn.getech.data.manager.constant.DBTypeEnum;
import cn.getech.data.manager.constant.DataSourceTypeEnum;
import cn.getech.data.manager.entity.DataQualityRulePartitionField;
import cn.getech.data.manager.model.dto.QualityRuleFieldDto;
import cn.getech.data.manager.model.dto.QueryDto;
import cn.getech.data.manager.model.vo.RuleDataRowVO;
import cn.getech.data.manager.model.vo.RuleFieldVO;
import cn.getech.data.manager.service.factory.DataSourceStrategyFactory;
import cn.getech.data.manager.service.strategy.DataSourceStrategy;
import cn.getech.data.manager.service.strategy.ManagerHiveTemplate;
import cn.getech.system.center.utils.ShiroUtils;
import cn.hutool.core.collection.CollectionUtil;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.InitializingBean;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.math.BigDecimal;
import java.util.*;
import java.util.stream.Collectors;

import static org.codehaus.groovy.runtime.DefaultGroovyMethods.collect;

/**
 * @ClassName TableNameHiveService
 * @Description 获取表名hive 实现
 * @Author Getech
 * @Date 2020/10/10 14:16
 */
@Service
@Slf4j
public class DataSourceHiveStrategy implements DataSourceStrategy, InitializingBean {

    @Autowired
    private TableInfoService tableInfoService;


    @Autowired
    private TableFieldInfoService tableFieldInfoService;


    @Autowired
    private ManagerHiveTemplate managerHiveTemplate;


    @Autowired
    private BdpJobConfig bdpJobConfig;

    @Autowired
    private TableInfoMapper tableInfoMapper;


    @Autowired
    private DataDevelopmentConfig developmentConfig;



    @Override
    public List<String> getTableNameList(Integer connectId, String databaseName,String tableName)throws Exception {
        Map<String, Object> params=new HashMap<>();
        params.put("db_name",databaseName);
        params.put("table_name",tableName);
        Long userId = ShiroUtils.getUserId();
        params.put("create_per", userId);
        params.put("appUser", userId);
        params.put("sidx", "id");
        params.put("order", "desc");
        params.put("page", "1");
        params.put("limit", "3000");
        DataPermissionUtil.putDataFilter(params,
                PermissionTypeEnum.STORE.getType(),PermissionTypeEnum.QUERY.getType());
        IPage<TableInfo> page = this.tableInfoMapper.listNoRelation((Page<TableInfo>) new Query<TableInfo>().getPage(params), params);
        List<TableInfo> tableInfoList= page.getRecords()!=null? page.getRecords():new ArrayList<>();
        return tableInfoList.stream().map(e->e.getTableName()).collect(Collectors.toList());
    }

    /**
    * @Description 获取数据库名字
    * @Author  chengweiping
    * @Date   2020/10/19 17:46
    */
    @Override
    public List<String> getDbNameList(Integer connectId)throws Exception {
        List<String> dbNameList=new ArrayList<>();
        for(DBTypeEnum dbTypeEnum: DBTypeEnum.values()){
            dbNameList.add(dbTypeEnum.getName());
        }
      return dbNameList;
    }



    /**
    * @Description 获取表行数
    * @Author  chengweiping
    * @Date   2020/10/13 22:58
     * @param query
    */
    @Override
    public Long getTableRowNum(QueryDto query) throws Exception {
        //统计表总数的sql
        String sql = "select count(1) as totalCount  from %s ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql="select count(1) as totalCount  from %s where 1=1 and  " + query.getSqlWhere();
        }
        //统计表总数的sql
        String formatSql = String.format(sql, query.getTableName());
        query.setSql(formatSql);
        Long value= managerHiveTemplate.getLongValue(query);
        return value;
    }



    /**
    * @Description  获取表字段列表
    * @Author  chengweiping
    * @Date   2020/10/13 22:58
    */
    @Override
    public List<RuleFieldVO> getFieldList(QualityRuleFieldDto qualityRuleFieldDto) throws  Exception {

        QueryWrapper queryWrapper=new QueryWrapper();
        queryWrapper.eq("db_name",qualityRuleFieldDto.getDatabaseName());
        queryWrapper.eq("table_name",qualityRuleFieldDto.getTableName());
        TableInfo tableInfo=tableInfoService.getOne(queryWrapper);
        List<RuleFieldVO> list=new ArrayList<>();
        if(tableInfo!=null){

            QueryWrapper queryWrapper2=new QueryWrapper();
            queryWrapper2.eq("table_id",tableInfo.getId());
            List<TableFieldInfo> tableFieldInfoList= tableFieldInfoService.list(queryWrapper2);
            list= tableFieldInfoList.stream().map(e-> {
                RuleFieldVO ruleFieldVO=new RuleFieldVO();
                ruleFieldVO.setFieldName(e.getFieldName());
                ruleFieldVO.setFieldType(e.getFieldType()!=null?e.getFieldType().toLowerCase():"");
                ruleFieldVO.setIsPartition(e.getIsPartition());
                return ruleFieldVO;
            }).collect(Collectors.toList());
        }
        return list;
    }


    /**
    * @Description 获取表大小
    * @Author  chengweiping
    * @Date   2020/10/13 22:59
    */
    @Override
    public BigDecimal getTotalTableSize(QueryDto query) throws Exception {
       // String dirHome = "/user/hive/warehouse_ranger/";
        String dirHome= developmentConfig.getWarehouseDir();
        if(org.apache.commons.lang.StringUtils.isNotBlank(dirHome)){
            //去除前后空格
            dirHome=dirHome.trim();
        }
        HdfsUtil hdfsUtil = null;
        BigDecimal tableSize=new BigDecimal(0);
        try {
            hdfsUtil = new HdfsUtil(bdpJobConfig);
            String dbUrl = dirHome + query.getDbName() + ".db";
            Long tableSizeLong = hdfsUtil.getTableOrDbSize(dbUrl + "/" + query.getTableName());
            if(tableSizeLong!=null){
                //转换为MB
                tableSize=new BigDecimal(tableSizeLong);
            }


        } catch (Exception e) {
            e.printStackTrace();
        }
        return tableSize;

    }

    @Override
    public Long getFieldNullNum(QueryDto query) throws Exception {
            String sql1=" select count(1) as totalCount  from %s where  ( %s is null  or  %s=''  ) ";
            String sql2="  ";
            String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
            String formatSql = String.format(sql, query.getTableName(),query.getFieldName(),query.getFieldName());
            query.setSql(formatSql);
            Long value= managerHiveTemplate.getLongValue(query);
            return value;
    }

    @Override
    public Long getFieldRepeatNum(QueryDto query) throws Exception {
        String sql1="select count(1) as totalCount from ( select %s,count(*) as  toatlCountTemp from %s where  %s is not null  ";
        String sql2="  group by  %s having toatlCountTemp > 1) t ";
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName,fieldName);
        query.setSql(formatSql);
        Long value= managerHiveTemplate.getLongValue(query);
        return value;
    }


    @Override
    public Long getFieldUniqueNum(QueryDto query) throws Exception {
        String sql1="select count(1) as totalCount from ( select %s,count(*) as  toatlCountTemp from %s where  %s is not null  ";
        String sql2="  group by  %s having toatlCountTemp =1) t ";
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName,fieldName);
        query.setSql(formatSql);
        Long value= managerHiveTemplate.getLongValue(query);
        return value;
    }



    @Override
    public BigDecimal getFieldSumNum(QueryDto query) throws Exception {

        String sql1=" select sum(%s) as totalCount  from %s  where  %s is not null  ";
        String sql2="    ";
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        //统计表sum值sql
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName);
        query.setSql(formatSql);
        BigDecimal result= managerHiveTemplate.getBigDecimalValue(query);
        BigDecimal value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        return value;

    }


    @Override
    public BigDecimal getFieldMinNum(QueryDto query) throws Exception {

        String sql1=" select min(%s) as minValue  from %s  where  %s is not null  ";
        String sql2="    ";
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName);
        query.setSql(formatSql);
        BigDecimal result= managerHiveTemplate.getBigDecimalValue(query);
        BigDecimal value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        return value;
    }

    @Override
    public BigDecimal getFieldMaxNum(QueryDto query) throws Exception {
        String sql1="  select max(%s) as maxValue  from %s  where  %s is not null  ";
        String sql2="    ";
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName);
        query.setSql(formatSql);
        BigDecimal result= managerHiveTemplate.getBigDecimalValue(query);
        BigDecimal value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        return value;

    }


    @Override
    public BigDecimal getFieldAvgNum(QueryDto query) throws Exception {
        String sql1="  select avg(%s) as avgCount  from  %s  where  %s is not null  ";
        String sql2="    ";
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        //计算sql
        String tableName=query.getTableName();
        String fieldName=query.getFieldName();
        String formatSql= String.format(sql, fieldName,tableName,fieldName);
        query.setSql(formatSql);
        BigDecimal result= managerHiveTemplate.getBigDecimalValue(query);
        BigDecimal value=new BigDecimal(0);
        if(result!=null){
            value=result.setScale(2, BigDecimal.ROUND_HALF_UP);
        }

        return value;
    }


    @Override
    public BigDecimal getCustomeSqlResult(Integer connectId,String dbName,String tableName,String sql) throws Exception {
        QueryDto query=new QueryDto();
        query.setConnectId(connectId);
        query.setDbName(dbName);
        query.setTableName(tableName);
        String newSql=sql.toLowerCase().replace(";","").trim();
        query.setSql(newSql);
        BigDecimal result= managerHiveTemplate.getBigDecimalValue(query);
        System.out.println("自定义统计sql值:"+result);
        return result;
    }

    @Override
    public String getCustomeSqlField(Integer connectId, String dbName, String tableName, String sql) throws Exception {
        QueryDto query=new QueryDto();
        query.setConnectId(connectId);
        query.setDbName(dbName);
        query.setTableName(tableName);
        query.setSql(sql);
        String result= managerHiveTemplate.getFirstFieldMetaValue(query);
        System.out.println("自定义统计sql字段:"+result);
        return result;
    }

    @Override
    public List<RuleDataRowVO> getTableNullRowList(QueryDto query)throws Exception {
        List<RuleDataRowVO> rowList=new ArrayList<>();
        String sql1="  select *  from  %s where  %s is null  ";
        String sql2="    ";
        List<DataQualityRulePartitionField> partitionFieldList= query.getPartitionFieldList();
        String partitionFieldStr= parsePartitionStr(partitionFieldList);
        if(StringUtils.isNotBlank(partitionFieldStr)){
            sql2=sql2+ " and   "+ "( "+partitionFieldStr+" ) ";
        }
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        String field=query.getFieldName();
        String tableName=query.getTableName();
        //空值sql
        String  formatSql=String.format(sql,tableName,field);
        query.setSql(formatSql);
        rowList=managerHiveTemplate.getRuleRowList(query);

        return rowList;
    }

    /**
    * @Description 获取唯一行数据
    * @Author  chengweiping
    * @Date   2020/10/20 17:35
    */
    @Override
    public List<RuleDataRowVO> getTableUniqueRowList(QueryDto query)throws Exception {
        List<RuleDataRowVO> rowList=new ArrayList<>();
        String sql1=" select *  from  %s t  where %s in ( select %s from %s where  %s is not null   ";
        String sql2="   group by  %s having count(%s) =1)   ";
        List<DataQualityRulePartitionField> partitionFieldList= query.getPartitionFieldList();
        String partitionFieldStr= parsePartitionStr(partitionFieldList);
        if(StringUtils.isNotBlank(partitionFieldStr)){
            sql2=sql2+ " and   "+ "( "+partitionFieldStr+" ) ";
        }
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        String field=query.getFieldName();
        String tableName=query.getTableName();
        //统计表唯一值sql
        String  formatSql=String.format(sql,tableName,field,field,tableName,field,field,field);
        query.setSql(formatSql);
        rowList=managerHiveTemplate.getRuleRowList(query);
        return rowList;
    }




    @Override
    public List<RuleDataRowVO> getTableRepeatRowList(QueryDto query) throws Exception{
        List<RuleDataRowVO> rowList=new ArrayList<>();
        String sql1=" select *  from  %s t where %s in ( select %s from %s where  %s is not null     ";
        String sql2="  group by  %s having count(%s) > 1)   ";
        List<DataQualityRulePartitionField> partitionFieldList= query.getPartitionFieldList();
        String partitionFieldStr= parsePartitionStr(partitionFieldList);
        if(StringUtils.isNotBlank(partitionFieldStr)){
             sql2=sql2+ " and   "+ "( "+partitionFieldStr+" ) ";
        }
        String sql=parseSqlForWhereAndPartition(query,sql1,sql2);
        String field=query.getFieldName();
        String tableName=query.getTableName();
       //统计表唯一值sql
        String  formatSql=String.format(sql,tableName,field,field,tableName,field,field,field);
        query.setSql(formatSql);
        rowList=managerHiveTemplate.getRuleRowList(query);
        return rowList;
    }


    /**
    * @Description
    * @Author  chengweiping
    * @Date   2020/10/22 10:47
    */
    @Override
    public List<String> getTablePartitionList(QueryDto query) throws Exception {
        String sql=" show partitions %s  ";
        //计算sql
        String tableName=query.getTableName();
        String formatSql= String.format(sql, tableName);
        query.setSql(formatSql);
        List<String> list= managerHiveTemplate.getFieldValueList(query);
        return list;
    }

    /**
     * @Description 解析sql，拼接where与partition条件
     * @Author  chengweiping
     * @Date   2020/10/29 12:43
     */
    private String  parseSqlForWhereAndPartition(QueryDto query,String sql1,String sql2){
        List<DataQualityRulePartitionField> partitionFieldList= query.getPartitionFieldList();
        //有分区字段时，加上分区字段限定值
        String sqlPartitionCondition="";
        if(CollectionUtil.isNotEmpty(partitionFieldList)){
            sqlPartitionCondition=parsePartitionStr(partitionFieldList);
        }

        String sql=sql1+sql2;
        String sqlAnd=" and ";
        if(StringUtils.isNotBlank(query.getSqlWhere())){
            sql=sql1 +sqlAnd + query.getSqlWhere()+ sql2;
            if(StringUtils.isNotBlank(sqlPartitionCondition)){
                sql=sql1 +sqlAnd + query.getSqlWhere()+  sqlAnd+  " ( " + sqlPartitionCondition+ "  ) " + sql2;
            }
        }else{
            if(StringUtils.isNotBlank(sqlPartitionCondition)){
                sql=sql1 +sqlAnd + " ( " + sqlPartitionCondition+ " ) "+sql2;
            }
        }
        return sql;
    }


    /**
    * @Description 解析分区条件
    * @Author  chengweiping
    * @Date   2020/10/30 11:50
    */
    public String parsePartitionStr(List<DataQualityRulePartitionField> partitionFieldList){
        if(CollectionUtil.isEmpty(partitionFieldList)){
            return "";
        }else{
            String sqlPartitionCondition="";
            sqlPartitionCondition= partitionFieldList.stream()
                    .filter(e->e.getFieldName()!=null)
                    .map(e->e.getFieldName()+"="+"'"+ (e.getFieldValue()!=null?e.getFieldValue():"")+"'")
                    .collect(Collectors.joining("  or  "));
            return sqlPartitionCondition;
        }


    }


    @Override
    public void afterPropertiesSet() throws Exception {
        DataSourceStrategyFactory.setService(DataSourceTypeEnum.HIVE.getTypeId(),this);
    }
}