package com.product.data.center.utils;
|
|
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.center.config.CmnConst;
|
import com.product.data.center.config.ErrorCode;
|
import com.product.datasource.config.DataBaseType;
|
import com.product.datasource.dao.Dao;
|
import com.product.util.BaseUtil;
|
import net.minidev.json.JSONObject;
|
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(ErrorCode.NO_MATCH_TABLE.getValue(), ErrorCode.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);
|
}
|
|
|
}
|
