smartsearch/src/main/java/com/bowintek/practice/services/impl/GenSqlStringServiceImpl.java

package com.bowintek.practice.services.impl;

import com.bowintek.practice.mapper.SrSaerchtempMapper;
import com.bowintek.practice.mapper.SrSubjectMapper;
import com.bowintek.practice.mapper.SrSubjectfieldMapper;
import com.bowintek.practice.mapper.cquery.SubjectCQuery;
import com.bowintek.practice.mapper.cquery.TempCQuery;
import com.bowintek.practice.model.*;
import com.bowintek.practice.services.service.GenSqlStringService;
import com.bowintek.practice.services.service.TempService;
import com.bowintek.practice.util.multipdb.DBTypeEnum;
import com.bowintek.practice.util.multipdb.SwitchDataSource;
import com.bowintek.practice.vo.temp.SqlColumnModel;
import com.bowintek.practice.vo.temp.SqlFieldModel;
import com.bowintek.practice.vo.temp.TempObjectModel;
import com.bowintek.practice.vo.temp.TempSaveResult;
import org.apache.ibatis.jdbc.SqlRunner;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;

import java.sql.Connection;
import java.sql.DriverManager;
import java.text.MessageFormat;
import java.util.*;
import java.util.concurrent.RecursiveTask;
import java.util.stream.Collectors;

@Component
public class GenSqlStringServiceImpl implements GenSqlStringService {
    @Autowired
    SrSubjectMapper srSubjectMapper;
    @Autowired
    SrSubjectfieldMapper srSubjectfieldMapper;
    @Autowired
    TempCQuery tempCQuery;

    @Override
    @SwitchDataSource(DBTypeEnum.POSTGRE)
    public List<Map<String,Object>> getListBySqlString(String sqlString){
        //return tempCQuery.getListBySqlString(sqlString);
        try {
            Connection connection = DriverManager.getConnection(
                    "jdbc:mysql://office.bowintek.com:3306/practicedb?useUnicode=true&characterEncoding=utf-8&useSSL=false&serverTimezone=Asia/Shanghai",
                    "root", "bowin123");
            SqlRunner sqlRunner = new SqlRunner(connection);
            return sqlRunner.selectAll(sqlString);
        }
        catch (Exception ex){
            ex.printStackTrace();
            return null;
        }
    }

    //查询语句生成，要素点：查询主表、关联子表、查询字段、查询条件
    //查询主表：FROM (sr_subject.execSql) AS T
    //查询字段-维度：sr_tempdimension.fieldAlias
    //查询字段-度量：sr_tempmeasure.fieldAlias + 函数名称/表达式； SUM({$FIELD$}) / COUNT(*) 替换中间字段名称
    //查询条件：检测出 日期型、字符型、数字型，在针对性生成条件语句
    //输出字段 AS：ReFieldName0,1,2...；依据维度度量排序来
    //表名 AS：T 主表，T0,1,2...；子表，依据维度度量子表排序来
    @Override
    public String Generation(TempObjectModel data, String userID){
        //取得字段原本定义
        data.sub = srSubjectMapper.selectByPrimaryKey(data.temp.getSubId());
        List<SrSubjectfield> fileds = getSubjectfieldsBySubId(data.temp.getSubId());
        //循环分析出关联表
        //isForeignKey leftFieldId rightFieldId referencesTab displayColumn
        int tabIndex = 0;
        HashMap<String, SqlColumnModel> aliasMap = new HashMap<>();
        List<SqlFieldModel> models = new ArrayList<>();
        data.bases.forEach(it->{ models.add(SqlFieldModel.GenModel(it.getFieldId(),it.getTempFeildId()));});
        data.measures.forEach(it->{ models.add(SqlFieldModel.GenModel(it.getFieldId(),it.getTempFeildId()));});

        //生成主要的字段、关联表、表T名称、字段T名称对照表
        String leftTableString = "";
        for(int i=0;i<models.size();i++){
            SrSubjectfield fd = findFieldByFieldId(fileds, models.get(i).getFieldId());
            SqlColumnModel cm = getSqlColumn(fd.getIsForeignKey(), fd.getFieldAlias(), fd.getFieldCode(), fd.getForeignKey(),
                    fd.getReferencesTab(), fd.getDisplayColumn(), tabIndex);
            if(cm.getIsForeignKey()) {
                tabIndex++;
                leftTableString+=cm.getLeftJoinString();
            }
            aliasMap.put(models.get(i).getTempFieldId(), cm);
        }

        int fieldIndex = 0;
        String colString = "", groupString = "";
        //维度生成，Group生成
        for(int i=0;i<data.bases.size();i++){
            SrTempdimension dim = data.bases.get(i);
            SqlColumnModel cm = aliasMap.get(dim.getTempFeildId());

            colString += MessageFormat.format("{0} {1} AS {2}\n\t",
                    getCommaString(fieldIndex), cm.getDisplayColumn(), getFieldName(fieldIndex));
            groupString += MessageFormat.format("{0} {1}\n\t",getCommaString(fieldIndex), cm.getDisplayColumn());
            fieldIndex++;
        }

        //度量生成
        for(int i=0;i<data.measures.size();i++){
            SrTempmeasure msr = data.measures.get(i);
            SqlColumnModel cm = aliasMap.get(msr.getTempFeildId());

            colString += MessageFormat.format("{0} {1} AS {2}\n\t",
                    getCommaString(fieldIndex),
                    getMeasurString(msr.getOperation(), cm.getDisplayColumn()),
                    getFieldName(fieldIndex));
            fieldIndex++;
        }

        //查询条件的生成
        String whereString = "";
        for(int i=0;i<data.wheres.size();i++){
            SrTempfield stf = data.wheres.get(i);
            int dataType = getDataType(stf.getDataType());
            if(dataType==0) whereString += genWhereString(stf);
            else if(dataType==1) whereString += genWhereNumber(stf);
            else if(dataType==2) whereString += genWhereDate(stf);
        }

        //生成排序字段
        String orderByString = "";
        if(IsNullEmpty(data.orderBy) || !aliasMap.containsKey(data.orderBy)) data.orderBy = aliasMap.keySet().stream().findFirst().get();
        System.out.println("data.orderBy:"+data.orderBy);

        if(aliasMap.containsKey(data.orderBy)){
            if(IsNullEmpty(data.orderByString)) data.orderByString = "asc";
            SqlColumnModel cmOrder = aliasMap.get(data.orderBy);
            orderByString = MessageFormat.format("ORDER BY {0} {1}", cmOrder.getDisplayColumn(), data.orderByString);
        }

        String mainSqlStrimg = MessageFormat.format("SELECT {0} \nFROM {1} AS T {2} \nWHERE 1=1 {3} \nGROUP BY {4} \n\n{5}",
                colString, data.sub.getExecSql(), leftTableString, whereString, groupString, orderByString);
        System.out.println(mainSqlStrimg);
        return mainSqlStrimg;
    }



    public int getDataType(String typeString){
        List<String> strings = Arrays.asList(new String[]{"char","varchar","text"});
        List<String> numbers = Arrays.asList(new String[]{"int","bigint","decimal"});
        List<String> dates = Arrays.asList(new String[]{"date"});
        if(strings.indexOf(typeString.toLowerCase(Locale.ROOT))>=0) return 0;
        if(numbers.indexOf(typeString.toLowerCase(Locale.ROOT))>=0) return 1;
        if(dates.indexOf(typeString.toLowerCase(Locale.ROOT))>=0) return 2;
        return -1;
    }

    public static boolean IsNullEmpty(String value) {
        return value == null || value.isEmpty();
    }
    public String genWhereString(SrTempfield stf){
        if(IsNullEmpty(stf.getOperation()) || IsNullEmpty(stf.getValue1()))
            return genRemark("genWhereString", stf.getTempFeildId());

        if(stf.getOperation().equals("="))
            return MessageFormat.format("\n\tAND T.{0}='{1}'", stf.getFieldAlias(),
                    "'"+stf.getValue1()+"'");
        else if(stf.getOperation().equals("like"))
            return MessageFormat.format("\n\tAND T.{0} like {1}", stf.getFieldAlias(),
                    "'%"+stf.getValue1()+"%'");
        else
            return MessageFormat.format("\n\tAND T.{0} {1}", stf.getFieldAlias(), stf.getOperation());
    }

    public String genWhereNumber(SrTempfield stf){
        if(IsNullEmpty(stf.getOperation()) || IsNullEmpty(stf.getValue1()) ||
                (stf.getOperation().equals("limit") && IsNullEmpty(stf.getValue2())))
            return genRemark("genWhereNumber", stf.getTempFeildId());

        if(stf.getOperation().equals("limit"))
            return MessageFormat.format("\n\tAND T.{0} >= {1} AND T.{2}<={3}",
                    stf.getFieldAlias(), stf.getValue1(), stf.getFieldAlias(), stf.getValue2());
        else
            return MessageFormat.format("\n\tAND T.{0} {1} {2}", stf.getFieldAlias(), stf.getOperation(), stf.getValue1());
    }

    public String genWhereDate(SrTempfield stf){
        if(IsNullEmpty(stf.getOperation())) return genRemark("genWhereDate", stf.getTempFeildId());

        String where = "";
        if(!IsNullEmpty(stf.getValue1()))
            where += MessageFormat.format("\n\tAND T.{0} >= {1}", stf.getFieldAlias(),
                    "'"+stf.getValue1()+"'");
        if(!IsNullEmpty(stf.getValue2()))
            where += MessageFormat.format("\n\tAND T.{0} <= {1}", stf.getFieldAlias(),
                    "'"+stf.getValue2()+"'");

        return IsNullEmpty(where)?genRemark("genWhereDate", stf.getTempFeildId()) : where;
    }

    public String genRemark(String fun,String key) {
        //return "\n/*" + fun + " " + key + " is gen err*/";
        return "";
    }

    public SrSubjectfield findFieldByFieldId(List<SrSubjectfield> fields, String fieldId){
        return fields.stream().filter(t->t.getFieldId().equals(fieldId)).findAny().orElse(null);
    }

    public List<SrSubjectfield> getSubjectfieldsBySubId(String subId){
        SrSubjectfieldExample example = new SrSubjectfieldExample();
        SrSubjectfieldExample.Criteria criteria = example.or();
        criteria.andSubIdIn(Arrays.asList(new String[]{subId}));
        List<SrSubjectfield> fileds = srSubjectfieldMapper.selectByExample(example);
        System.out.println("getSubjectfieldsBySubId Size:"+fileds.size());
        return fileds;
    }

    public SqlColumnModel getSqlColumn(int isForeignKey, String filedAlias, String leftFieldId, String rightFieldId,
                                       String referencesTab, String displayColumn, int index){
        SqlColumnModel model = new SqlColumnModel();
        if(isForeignKey==0 || IsNullEmpty(leftFieldId) || IsNullEmpty(rightFieldId) || IsNullEmpty(referencesTab) || IsNullEmpty(displayColumn)){
            model.setIsForeignKey(false);
            model.setTableAlias("T");
            model.setDisplayColumn(MessageFormat.format("{0}.{1}","T", filedAlias));
            return model;
        }

        model.setIsForeignKey(true);
        model.setTableAlias("T"+index);
        String leftJoinString = MessageFormat.format("\nLEFT JOIN {0} AS {1} on T.{2}={3}.{4}",
                referencesTab, model.getTableAlias(), leftFieldId, model.getTableAlias(), rightFieldId);
        model.setLeftJoinString(leftJoinString);
        model.setDisplayColumn(MessageFormat.format("{0}.{1}",model.getTableAlias(), displayColumn));

        return model;
    }

    public String getFieldName(int index){
        return "ReFieldName"+index;
    }

    public String getCommaString(boolean is){
        return is?",":"";
    }

    public String getCommaString(int index){
        return getCommaString(index>0);
    }

    public String getMeasurString(String oprationStr, String fieldName){
        return oprationStr.replaceAll("\\{\\$FIELD\\$\\}", fieldName);
    }
}