责任链设计模式 动态绑定demo

package com.meritdata.cloud.multianalysis.customizesearch.handler;

import com.meritdata.cloud.base.entity.QOrganization;
import com.meritdata.cloud.multianalysis.common.enums.*;
import com.meritdata.cloud.multianalysis.common.exception.HandleServiceException;
import com.meritdata.cloud.multianalysis.customizesearch.dto.CustomizeSearchParamDto;
import com.meritdata.cloud.multianalysis.entity.DataSet;
import com.meritdata.cloud.multianalysis.entity.QDataSet;
import com.meritdata.cloud.multianalysis.properties.MultianalyProperties;
import com.meritdata.cloud.utils.MD5;
import com.meritdata.cloud.utils.SessionUtils;
import com.querydsl.jpa.impl.JPAQueryFactory;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;

import java.util.*;


@Service
@Slf4j
public class CheckSqlHandler extends SqlHandler {

    private final Integer orderId = 1;


    @Autowired
    private JPAQueryFactory jpaQueryFactory;

    @Autowired
    private SessionUtils sessionUtils;

    @Autowired
    private MultianalyProperties multianalyProperties;

    @Override
    public void draw(StringBuilder sqlBuilder, CustomizeSearchParamDto.Table table) {
        if(StringUtils.isBlank(sqlBuilder)) {
            List codes = table.getCodes();
            String tableName = "";
            sqlBuilder.append("SELECt ");
            sqlBuilder.append(String.join(",", getColumn(table)));
            sqlBuilder.append(" FROM ");
            if (table.getCode().contains(",")) {
                //拼接多个join on 关联关系
                StringBuilder sb = getJoinSql(table, sqlBuilder);
                sqlBuilder.append(sb);
            } else {
                tableName = getTableName(codes, table.getCode());
                sqlBuilder.append(tableName);
            }
            sqlBuilder.append(this.getFilters(table));
        }else{
            getNext().draw(sqlBuilder,table);
        }
    }

    @Override
    public Integer getOrder() {
        return orderId;
    }

    

    private StringBuilder getJoinSql(CustomizeSearchParamDto.Table table, StringBuilder sqlBuilder) {
        String tableName = null;
        List codes = table.getCodes();
        CustomizeSearchParamDto.Join join = table.getJoin();
        String leftTable = join.getLeftTable();
        tableName = getTableName(codes, leftTable);
        sqlBuilder.append(tableName);
        StringBuilder sb = new StringBuilder();
        for (CustomizeSearchParamDto.Relations relation : join.getRelations()) {
            CustomizeSearchParamDto.On on = relation.getOn();
            String relationKey = RelationsEnum.getByName(relation.getRelation()).getValue();
            tableName = getTableName(codes, relation.getRightTable());
            sb.append(relationKey).append(tableName).append(" on ");
            List relationOfOn = on.getRelation();
            //拼接单个join on  后面可能存在的多个条件
            getConditionOfOn(sb, relationOfOn);
        }
        return sb;
    }

    private String getTableName(List codes, String name) {
        String tableName = name;
        if (!CollectionUtils.isEmpty(codes)) {
            QDataSet qDataSet = QDataSet.dataSet;
            Optional first = codes.stream().filter(s -> s.getTableName().equals(name)).findFirst();
            CustomizeSearchParamDto.Code code = new CustomizeSearchParamDto.Code();
            if (first.isPresent()) {
                code = first.get();
            }
            if ("SQL".equals(code.getType())) {
                DataSet dataSet = jpaQueryFactory.selectFrom(qDataSet).where(qDataSet.id.eq(code.getModelId())).fetchFirst();
                tableName = " (" + dataSet.getCheckSql() + ") " + code.getTableName();
            }
        }
        return tableName;
    }

    
    private void getConditionOfOn(StringBuilder sb, List relationOfOn) {
        List relationOfOnList = new ArrayList<>();
        for (CustomizeSearchParamDto.RelationOfOn ofOn : relationOfOn) {
            StringBuilder sbOfOn = new StringBuilder(" ");
            CustomizeSearchParamDto.Column left = ofOn.getLeft();
            String leftFinallyName = getConcat(left.getTableName(), left.getCode());
            CustomizeSearchParamDto.Column right = ofOn.getRight();
            String rightFinallyName = getConcat(right.getTableName(), right.getCode());
            String relationOFOn = ofOn.getRelation();
            SearchLogicalTypeEnum logicalTypeEnum = SearchLogicalTypeEnum.getByCode(relationOFOn);
            String format = String.format(logicalTypeEnum.getSymbol(), rightFinallyName);
            sbOfOn.append(leftFinallyName).append(format.replace(":", ""));
            relationOfOnList.add(sbOfOn.toString());
        }
        sb.append(String.join(" and ", relationOfOnList));
    }

    
    private StringBuilder getAuth(Map sqlParams, StringBuilder sqlBuilder) {
        if (StringUtils.isBlank(multianalyProperties.getColumn())) {
            return null;
        }
        Boolean rule = multianalyProperties.getRule();
        if (Boolean.TRUE.equals(rule)) {
            sqlParams.put("dLevelCode", sessionUtils.getOrgDlevelCode());
            sqlBuilder.append(" WHERe ").append(multianalyProperties.getColumn()).append(" LIKE :dLevelCode '%'");
        } else {
            QOrganization qOrganization = QOrganization.organization;
            List ids = jpaQueryFactory.select(qOrganization.id).from(qOrganization).where(qOrganization.parentId.eq(sessionUtils.getDeptId())).fetch();
            List deptIds = new ArrayList<>();
            if (ids != null && ids.size() > 0) {
                deptIds.addAll(ids);
                getAuthDeptId(ids, deptIds);
            }
            deptIds.add(sessionUtils.getDeptId());
            sqlParams.put("deptIds", deptIds);
            sqlBuilder.append(" WHERe ").append(multianalyProperties.getColumn()).append(" IN (:deptIds)");
        }
        return sqlBuilder;
    }

    
    private void getAuthDeptId(List id, List deptIds) {
        QOrganization qOrganization = QOrganization.organization;
        List ids = jpaQueryFactory.select(qOrganization.id).from(qOrganization).where(qOrganization.parentId.in(id)).fetch();
        if (ids != null && ids.isEmpty()) {
            deptIds.addAll(ids);
            getAuthDeptId(ids, deptIds);
        }

    }


    
    private List getColumn(CustomizeSearchParamDto.Table table) {
        if (CollectionUtils.isEmpty(table.getColumns())) {
            throw new HandleServiceException("参数错误: 没有选择列");
        }

        List columnList = new ArrayList<>();
        for (CustomizeSearchParamDto.Column column : table.getColumns()) {
            String columnName = null;
            if (StringUtils.isNotBlank(column.getType())) {
                AggregationType type = AggregationType.getByCode(column.getType());
                columnName = AggregationType.getSql(type, getConcat(column.getTableName(), column.getCode()), column.getAlias());
            } else if (StringUtils.isNotBlank(column.getCode())) {
                columnName = getConcat(column.getTableName(), column.getCode()).toUpperCase();

            }
            // 列名不为空  且 没有重复
            if (StringUtils.isNotBlank(columnName) && !columnList.contains(columnName)) {
                if (StringUtils.isNotEmpty(column.getAlias())) {
                    //聚合字段处理
                    if (!columnName.contains("AS")) {
                        columnName = columnName.concat(" as ").concat(column.getAlias());
                    }
                }
                columnList.add(columnName);
            }

        }

        // 如果列为空，则可能是 指标数据，此处查询指标数据
        if (CollectionUtils.isEmpty(columnList) || StringUtils.isNotBlank(table.getCode()) && table.getCode().contains(".")) {
            String tableCode = table.getCode();
            columnList = this.getIndicatorTableAndColumn(tableCode, table);
        }

        if (CollectionUtils.isEmpty(columnList)) {
            throw new HandleServiceException("参数错误: 列为空或者列无效");
        }

        return columnList;
    }

    private String getConcat(String tableName, String column) {
        return tableName.concat(".").concat(column);
    }

    
    private List getGroupBy(CustomizeSearchParamDto.Table table) {
        List groupbyList = new ArrayList<>();
        if (CollectionUtils.isEmpty(table.getColumns())) {
            return groupbyList;
        }

        for (CustomizeSearchParamDto.Column column : table.getColumns()) {
            if (StringUtils.isBlank(column.getType()) && StringUtils.isNotBlank((column.getCode()))) {
                groupbyList.add(getConcat(column.getTableName(), column.getCode()));
            }
        }

        return groupbyList;
    }

    private List getOrderBy(CustomizeSearchParamDto.Table table) {
        List orderByList = new ArrayList<>();
        if (CollectionUtils.isEmpty(table.getColumns())) {
            return orderByList;
        }

        for (CustomizeSearchParamDto.Column column : table.getColumns()) {
            if (StringUtils.isNotBlank(column.getOrder())) {
                OrderTypeEnum orderTypeEnum = OrderTypeEnum.getByName(column.getOrder());

                if (DataApplyTypeEnum.CROSS.getCode().equals(table.getType())) {
                    String orderCol = AggregationType.getAlias(column.getType(), column.getAlias()).concat(orderTypeEnum.getSql());
                    orderByList.add(orderCol.toUpperCase());
                } else {
                    orderByList.add(getConcat(column.getTableName(), column.getCode()).concat(orderTypeEnum.getSql()));
                }
            }
        }
        return orderByList;
    }

    
    private List getIndicatorTableAndColumn(String indName, CustomizeSearchParamDto.Table table) {
//        List indicatorList = indicatorService.findIndicatorByName(this.getSuffix(indName, "\."));
//        if (CollectionUtils.isEmpty(indicatorList)) {
//            return Collections.emptyList();
//        }
//
//        // 只取出第一条
//        Indicator indicator = indicatorList.get(0);
//
//        if (StringUtils.isNotBlank(indicator.getTabId())) {
//            MetaTable metaTable = metaTableService.getMetaTableById(indicator.getTabId());
//            table.setOriginalCode(metaTable.getTableName());
//        }
//
//        List columnList = new ArrayList<>();
//        if (StringUtils.isNotBlank(indicator.getColId())) {
//            MetaColumn metaColumn = metaColumnService.getMetaColumnById(indicator.getColId());
//            columnList.add(metaColumn.getColName());
//        }

//        return columnList;
        return  null;
    }

    private String getSuffix(String str, String character) {
        if (StringUtils.isBlank(str)) {
            return str;
        }

        String[] strArray = str.split(character);

        return strArray[strArray.length - 1];
    }

    
    private StringBuilder getFilters(CustomizeSearchParamDto.Table table) {
        StringBuilder sqlBuilder = new StringBuilder();
        Map sqlParams = new LinkedHashMap<>();
        StringBuilder auth = this.getAuth(sqlParams, sqlBuilder);
        table.setSqlParams(sqlParams);
        if (CollectionUtils.isEmpty(table.getFilter())) {
            return sqlBuilder;
        }


        String sql;
        for (CustomizeSearchParamDto.Filter filter : table.getFilter()) {
            sql = this.getFilter(filter, sqlParams);
            if (StringUtils.isNotBlank(sql)) {
                if (sqlBuilder.length() > 0) {
                    sqlBuilder.append(" AND ");
                }
                sqlBuilder.append(sql);
            }
        }
        table.setSqlParams(sqlParams);
        // 将sql执行的参数保存到查询参数对象中
        if (auth == null) {
            sqlBuilder.insert(0, " WHERe ");
        }

        return sqlBuilder;
    }

    
    private String getFilter(CustomizeSearchParamDto.Filter filter, Map sqlParams) {
        if (filter == null) {
            return null;
        }

        // 获取字段
        String field = filter.getField();
        // 获取逻辑关系符
        String logical = filter.getLogicalType();
        // 获取值类型
        String valType = filter.getValType();
        // 获取值
        String keywords = filter.getKeywords();
        //
        // 获取值
        String tableName = filter.getTableName();

        SearchLogicalTypeEnum logicalTypeEnum = SearchLogicalTypeEnum.getByCode(logical);
        SearchValTypeEnum valTypeEnum = SearchValTypeEnum.getByCode(valType);


        // 转换参数 以及封装 sql片段
        Map condition = this.getCondition(field, logicalTypeEnum, valTypeEnum, keywords, tableName);
        // 获取参数类型 STRING ARRAY RANGE
        SearchValTypeEnum typeEnum = SearchValTypeEnum.getByCode(String.valueOf(condition.get("type")));
        Object value = condition.get("value");
        switch (typeEnum) {
            case ARRAY:
                List