poi、easyexcel、poi-tl升级 ，合并空调中的报表、数据源模块

许鹏程

2023-06-29 914921a6b272b18a436f05422e33f9d963f48577

poi、easyexcel、poi-tl升级 ，合并空调中的报表、数据源模块

 src/main/java/com/product/server/report/util/QuerySqlParseUtil.java

¶Ô±ÈÐÂÎļþ
@@ -0,0 +1,326 @@
package com.product.server.report.util;

import com.alibaba.fastjson.JSONObject;
import com.google.common.collect.Lists;
import com.google.common.collect.Sets;
import com.product.core.dao.BaseDao;
import com.product.core.entity.DataTableEntity;
import com.product.core.entity.FieldSetEntity;
import com.product.core.exception.BaseException;
import com.product.data.config.ErrorCode;
import com.product.datasource.config.DataBaseType;
import com.product.datasource.dao.Dao;
import com.product.server.report.config.CmnConst;
import com.product.server.report.config.SystemCode;
import com.product.util.BaseUtil;
import org.apache.commons.lang3.StringUtils;

import java.util.*;

public class QuerySqlParseUtil {

    /**
     * èŽ·å–与表相关的年表 SQL过滤条件
     *
     * @param tableNames è¡¨åæ•°ç»„
     * @return
     */
    public static String getTableOfYearSQLFilter(Object[] tableNames) {
        StringBuilder tableFilter = new StringBuilder();
        for (int i = 0; i < tableNames.length; i++) {
            String tableName = (String) tableNames[i];
            if (!BaseUtil.strIsNull(tableName)) {
                if (i > 0) {
                    tableFilter.append(" AND ");
                } else {
                    tableFilter.append(" WHERE ");
                }
                tableFilter.append("table_name LIKE ?");
            } else {
                throw new BaseException("", "");
            }
        }
        return tableFilter.toString();
    }

    /**
     * ç”Ÿæˆéœ€æ±‚SQL
     * å¤„理：将所有年表联合，生成临时查询SQL
     * æ¡ˆä¾‹ï¼šWITH tableName1 AS (SELECT field1,field2... FROM tableName1_2022 UNION ALL SELECT field1,field2... FROM tableName1_2023),
     * tableName2 AS (SELECT field3,field4... FROM tableName2_2022 UNION ALL SELECT field3,field4... FROM tableName2_2023)
     *
     * @param tableNames  è¡¨åæ•°ç»„
     * @param dtYearTable è¡¨åä¸Žå¹´è¡¨åé›†
     * @return
     */
    public static String createFinalQuerySQL(Object[] tableNames, DataTableEntity dtYearTable) {
        StringBuilder tableFilter = new StringBuilder();
        JSONObject jsonTableAndYear = convertJson(dtYearTable);
        StringBuilder withSql = new StringBuilder();
        for (int i = 0; i < tableNames.length; i++) {
            String tableName = (String) tableNames[i];
            String tableYear = (String) jsonTableAndYear.get(tableName);
            if (!BaseUtil.strIsNull(tableYear)) {
                if (i == 0) {
                    withSql.append(" WITH ");
                }
                withSql.append(tableName);
                withSql.append(" AS(");
                String[] tableYears = tableYear.split(",");
                if (tableYear.length() > 1) {
                    for (int j = 0; j < tableYears.length; j++) {
                        withSql.append(" SELECT * FROM ");
                        withSql.append(tableYears[j]);
                        if (j < tableYears.length - 1) {
                            withSql.append(" UNION ALL ");
                        }
                    }
                } else {
                    withSql.append(" SELECT * FROM ");
                    withSql.append(tableYears[0]);
                }
                withSql.append("),");
            }
        }
        if (tableFilter.toString().endsWith(",")) {
            tableFilter.toString().substring(0, tableFilter.toString().length() - 1);
        }

        return tableFilter.toString();
    }

    /**
     * DataTableEntity è½¬ JSONObject
     * JSONObject<表名,"表名_2022,表名_2023,表名_2024...">
     *
     * @param dtYearTable
     * @return
     */
    public static JSONObject convertJson(DataTableEntity dtYearTable) {
        JSONObject jsonTableAndYear = new JSONObject();
        for (int i = 0; i < dtYearTable.getRows(); i++) {
            FieldSetEntity fseYearTable = dtYearTable.getFieldSetEntity(i);
            String tableName = fseYearTable.getString(CmnConst.TABLE_NAME);
            String tableYear = fseYearTable.getString("table_year");
            jsonTableAndYear.put(tableName, tableYear);
        }
        return jsonTableAndYear;
    }

    /**
     * èŽ·å–mysql库所有表名，例如，给表名test，获取数据库中所有的test年份分表，test_2019，test_2020...
     *
     * @param dao
     * @param dbName    æ•°æ®åº“名称
     * @param tableName è¡¨å
     * @return
     */
    public static Set<String> getAllTableName(Dao dao, String dbName, String tableName) {
        return getAllTableName(dao, null, dbName, tableName);
    }

    public static Set<String> getAllTableName(BaseDao baseDao, String dbName, String tableName) {
        return getAllTableName(null, baseDao, dbName, tableName);
    }

    private static Set<String> getAllTableName(Dao dao, BaseDao baseDao, String dbName, String tableName) {
        if (dao != null && !DataBaseType.MYSQL.equals(dao.getDataBaseType())) {
            throw new BaseException(ErrorCode.NONSUPPORT_DATABASE_TYPE);
        }
        String sql = "select table_name from information_schema.`TABLES` where table_schema=? and table_name like concat(?,'%') order by table_name";
        DataTableEntity dte = new DataTableEntity();
        if (dao != null) {
            dte = dao.getList(sql, new Object[]{dbName, tableName});
        }
        if (baseDao != null) {
            dte = baseDao.listTable(sql, new Object[]{dbName, tableName});
        }
        Set<String> set = Sets.newLinkedHashSet();
        FieldSetEntity fse;
        String curtableName;
        for (int i = 0; i < dte.getRows(); i++) {
            fse = dte.getFieldSetEntity(i);
            curtableName = StringUtils.isEmpty(fse.getString("TABLE_NAME")) ? fse.getString("table_name") : fse.getString("TABLE_NAME");
            if (curtableName.matches(tableName + "_?\\d{4,6}")) {
                set.add(curtableName);
            }
        }
        return set;
    }

    /**
     * mysql-获取with联合表查询sql
     *
     * @param tableAlias        è¡¨åˆ«åï¼Œwith之后的那个
     * @param tableCollection   åˆ†è¡¨é›†åˆï¼Œä¾‹å¦‚年份分表，就是多个年份表的集合
     * @param fieldCollection   æŸ¥è¯¢å­—段集合，没有可以直接给null，会默认查询所有字段。特殊：若是要查询当前表名，那么字段名为“{#table_name#}”，之后的别名为“_table_name”
     *                          ä¹Ÿå°±æ˜¯è¯´å‡è®¾å­—段为 name,{#table_name#} è¡¨åä¸ºtest_table，那么解析的sql为“select name,'test_table' _table_name from test_table”
     * @param onlyStructureFlag åªæŸ¥è¯¢ç»“构的标识，若为true，则只查询结构，即为with table_alias as (select * from table_name_1 union all select * from table_name_2 limit 1)
     * @param filter            è¿‡æ»¤æ¡ä»¶
     * @param tableAliasAlias   è¡¨åˆ«åçš„别名，当with后存在查询相同的表但是条件不同的时候使用表别名的别名作为with后的别名
     * @return
     */
    public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag, String filter, String tableAliasAlias) {
        StringBuilder sql = new StringBuilder(128);
        if (StringUtils.isEmpty(tableAlias) || tableCollection == null || tableCollection.isEmpty()) {
            return sql;
        }
        StringBuilder content = new StringBuilder(128);
        tableCollection.forEach(tableName -> {
            if (content.length() > 0) {
                content.append("\n    union all");
            }
            String fieldContent = "*";
            if (fieldCollection != null && !fieldCollection.isEmpty()) {
                fieldContent = BaseUtil.collection2String(fieldCollection);
                fieldContent = fieldContent.replace("{#table_name#}", "'" + tableName + "' _table_name");
            }
            content.append("\n    select ").append(fieldContent).append(" from ").append(tableName);
            if (!StringUtils.isEmpty(filter) && !StringUtils.isEmpty(filter.trim())) {
                content.append(" ").append(filter.replaceAll("\\s*\\n\\s*", " "));
            }
        });
        sql.append(tableAliasAlias).append(" as (").append(content);
        if (onlyStructureFlag) {
            sql.append(" limit 1");
        }
        sql.append("\n)");
        return sql;
    }

    public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag, String filter) {
        return getUnionTableSql(tableAlias, tableCollection, fieldCollection, onlyStructureFlag, filter, tableAlias);
    }

    public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection, boolean onlyStructureFlag) {
        return getUnionTableSql(tableAlias, tableCollection, fieldCollection, onlyStructureFlag, null);
    }

    public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection, Collection<String> fieldCollection) {
        return getUnionTableSql(tableAlias, tableCollection, fieldCollection, false);
    }

    /**
     * mysql-获取with联合表查询sql
     *
     * @param tableAlias      è¡¨åˆ«åï¼Œwith之后的那个
     * @param tableCollection åˆ†è¡¨é›†åˆï¼Œä¾‹å¦‚年份分表，就是多个年份表的集合
     * @return
     */
    public static StringBuilder getUnionTableSql(String tableAlias, Collection<String> tableCollection) {
        return getUnionTableSql(tableAlias, tableCollection, null);
    }

    /**
     * è§£æžsql中的分表，将其转化为with语句，其符号内的内容作为表别名使用
     *
     * @param baseDao
     * @param sql               å¾…解析的sql
     * @param year              æŸ¥è¯¢æœ€è¿‘的年数，0或者负数表示所有，按照当前为第一年，只查询当年，则给1；查询当前和去年，则给2；查询当年、去年、前年，则给3，以此类推...
     * @param onlyStructureFlag åªæŸ¥è¯¢ç»“构的标识，若为true，则只查询结构，即为with table_alias as (select * from table_name_1 union all select * from table_name_2 limit 1)
     * @return
     */
    public static String parseSplitTableSql(BaseDao baseDao, String sql, int year, boolean onlyStructureFlag) {
        boolean allTableFlag = year <= 0;
        StringBuilder parseSql = new StringBuilder(128);
        String regexp = "\\[=((?!\\[=).)*=\\]";
        List<String> suitList = BaseUtil.getSuitContent(sql, regexp, true);
        if (suitList.isEmpty()) {
            return sql;
        } else {
            Set<String> suitSet = Sets.newLinkedHashSet();
            suitSet.addAll(suitList);

            // èŽ·å–需要的年份
            List<String> yearList = Lists.newArrayList();
            if (!allTableFlag) {
                Calendar c = Calendar.getInstance();
                for (int i = 0; i < year; i++) {
                    yearList.add(String.valueOf(c.get(Calendar.YEAR)));
                    c.add(Calendar.YEAR, -1);
                }
            }
            // èŽ·å–对应存在表
            String tableInfo;
            String tableAlias;// è¡¨åˆ«åï¼ŒæŸ¥è¯¢ç”¨çš„前缀，通常with后的别名
            String tableAliasAlias;// è¡¨åˆ«åçš„别名，当with后存在查询相同的表但是条件不同的时候使用表别名的别名作为with后的别名
            Set<String> allTableSet;
            Set<String> needTableSet;
            for (String suitContent : suitSet) {
                Set<String> fieldSet = Sets.newLinkedHashSet();
                String filterInfo = null;
                needTableSet = Sets.newLinkedHashSet();
                tableInfo = suitContent.replace("[=", "").replace("=]", "").trim();
                if (tableInfo.contains("{")) {
                    tableAlias = tableInfo.substring(tableInfo.indexOf("{") + 1, tableInfo.indexOf("}"));
                    if (tableAlias.contains(" ")) {
                        tableAliasAlias = tableAlias.substring(tableAlias.lastIndexOf(" ") + 1);
                        tableAlias = tableAlias.substring(0, tableAlias.indexOf(" "));
                    } else {
                        tableAliasAlias = tableAlias;
                    }
                    int fromIndex = tableInfo.indexOf("}") + 1;
                    if (tableInfo.indexOf("{", fromIndex) > -1) {
                        String fieldInfo = tableInfo.substring(tableInfo.indexOf("{", fromIndex) + 1, tableInfo.indexOf("}", fromIndex));
                        if (!StringUtils.isEmpty(fieldInfo)) {
                            fieldSet.addAll(Arrays.asList(fieldInfo.split(",")));
                        }
                        fromIndex = tableInfo.indexOf("}", fromIndex) + 1;
                        if (tableInfo.indexOf("{", fromIndex) > -1) {
                            filterInfo = tableInfo.substring(tableInfo.indexOf("{", fromIndex) + 1, tableInfo.lastIndexOf("}"));
                        }
                    }
                } else {
                    tableAlias = tableInfo;
                    tableAliasAlias = tableAlias;
                }
                sql = sql.replace(suitContent, tableAliasAlias);
                allTableSet = getAllTableName(baseDao, baseDao.getDataBaseName(), tableAlias);
                if (allTableFlag) {
                    needTableSet = allTableSet;
                } else {
                    if (!yearList.isEmpty()) {
                        StringBuilder regexpSb = new StringBuilder(64);
                        for (String yearStr : yearList) {
                            if (regexpSb.length() > 0) {
                                regexpSb.append("|");
                            }
                            regexpSb.append(tableAlias).append("_").append(yearStr).append("\\d{0,2}");
                        }
                        regexp = regexpSb.toString();
                        for (String tableName : allTableSet) {
                            if (tableName.matches(regexp)) {
                                needTableSet.add(tableName);
                            }
                        }
                    }
                }
                // èŽ·å–sql
                StringBuilder contentSb = getUnionTableSql(tableAlias, needTableSet, fieldSet, onlyStructureFlag, filterInfo, tableAliasAlias);
                if (contentSb.length() > 0) {
                    if (parseSql.length() > 0) {
                        parseSql.append(",");
                    }
                    parseSql.append(contentSb);
                } else {
                    throw new BaseException(SystemCode.NO_MATCH_TABLE.getValue(), SystemCode.NO_MATCH_TABLE.getText() + " table_name:" + tableAlias);
                }
            }
            return new StringBuilder().append("with ").append(parseSql).append("\n").append(sql).toString();
        }
    }

    public static String parseSplitTableSql(BaseDao baseDao, String sql, int year) {
        return parseSplitTableSql(baseDao, sql, year, false);
    }

    public static String parseSplitTableSql(BaseDao baseDao, String sql, boolean onlyStructureFlag) {
        return parseSplitTableSql(baseDao, sql, 0, onlyStructureFlag);
    }

    public static String parseSplitTableSql(BaseDao baseDao, String sql) {
        return parseSplitTableSql(baseDao, sql, 0, false);
    }


}