package com.ruoyi.quartz.util;
|
|
import com.ruoyi.system.service.ISysConfigService;
|
import com.smartor.mapper.PatMedOuthospMapper;
|
import com.smartor.service.IPatMedOuthospService;
|
import lombok.extern.slf4j.Slf4j;
|
import org.springframework.beans.factory.annotation.Autowired;
|
import org.springframework.stereotype.Component;
|
|
import javax.sql.DataSource;
|
import java.sql.Connection;
|
import java.sql.Statement;
|
import java.time.LocalDate;
|
import java.util.*;
|
|
/**
|
* 创建数据库表
|
*/
|
@Slf4j
|
@Component
|
public class CreateDataTable {
|
@Autowired
|
private ISysConfigService configService;
|
|
@Autowired
|
IPatMedOuthospService iPatMedOuthospService;
|
|
@Autowired
|
PatMedOuthospMapper patMedOuthospMapper;
|
|
@Autowired
|
private DataSource dataSource;
|
|
|
public void createOuthospQuarterTable() {
|
log.info("【createOuthospQuarterTable】开始执行门诊分表任务");
|
try {
|
// 从 sys_config 查询分表模式， key = outhosp.split.mode
|
String splitMode = configService.selectConfigByKey("outhosp.split.mode");
|
if (org.apache.commons.lang3.StringUtils.isBlank(splitMode)) {
|
log.info("【createOuthospQuarterTable】未配置 outhosp.split.mode，不执行分表");
|
return;
|
}
|
splitMode = splitMode.trim();
|
if (!"季度".equals(splitMode) && !"年".equals(splitMode) && !"月".equals(splitMode)) {
|
log.warn("【createOuthospQuarterTable】分表模式配置非法：{}，应为 quarter/year/month，不执行分表", splitMode);
|
return;
|
}
|
String newTable = createPartitionTableAndRefreshView(splitMode);
|
if (newTable == null) {
|
log.info("【createOuthospQuarterTable】当前周期表已存在，无需操作");
|
} else {
|
log.info("【createOuthospQuarterTable】新建分表成功：{}，已刷新视图 v_pat_med_outhosp", newTable);
|
}
|
} catch (Exception e) {
|
log.error("【createOuthospQuarterTable】建表异常", e);
|
}
|
}
|
|
|
/**
|
* 按指定模式建门诊分表并更新动态视图
|
* splitMode: 季度 / 年 / 月 / 不分表
|
* 表名：
|
* quarter -> pat_med_outhosp_20260103
|
* year -> pat_med_outhosp_2026
|
* month -> pat_med_outhosp_202601
|
*/
|
public String createPartitionTableAndRefreshView(String splitMode) {
|
LocalDate today = LocalDate.now();
|
int year = today.getYear();
|
int month = today.getMonthValue();
|
|
// 1. 根据模式计算新表名和模板表名
|
String newTableName;
|
String templateName;
|
|
if ("年".equals(splitMode)) {
|
// 按年：pat_med_outhosp_2026
|
newTableName = "pat_med_outhosp_" + year;
|
// 模板表：优先上一年分表
|
String prevYearTable = "pat_med_outhosp_" + (year - 1);
|
templateName = patMedOuthospMapper.tableExists(prevYearTable) > 0 ? prevYearTable : "pat_med_outhosp";
|
|
} else if ("月".equals(splitMode)) {
|
// 按月：pat_med_outhosp_202601
|
newTableName = String.format("pat_med_outhosp_%d%02d", year, month);
|
// 模板表：优先上一月分表
|
LocalDate prevMonth = today.minusMonths(1);
|
String prevMonthTable = String.format("pat_med_outhosp_%d%02d", prevMonth.getYear(), prevMonth.getMonthValue());
|
templateName = patMedOuthospMapper.tableExists(prevMonthTable) > 0 ? prevMonthTable : "pat_med_outhosp";
|
|
} else {
|
// 默认按季度：pat_med_outhosp_20260103
|
String quarterSuffix;
|
if (month <= 3) {
|
quarterSuffix = "0103";
|
} else if (month <= 6) {
|
quarterSuffix = "0406";
|
} else if (month <= 9) {
|
quarterSuffix = "0709";
|
} else {
|
quarterSuffix = "1012";
|
}
|
newTableName = "pat_med_outhosp_" + year + quarterSuffix;
|
templateName = resolveQuarterTemplateName(year, quarterSuffix);
|
}
|
|
// 2. 检查表是否已存在
|
if (patMedOuthospMapper.tableExists(newTableName) > 0) {
|
log.info("[建表] 表 {} 已存在，无需重复建表，但仍然尝试刷新存储过程（确保分表已纳入查询）", newTableName);
|
refreshOuthospView();
|
return null;
|
}
|
|
log.info("[建表] 模式={}，使用模板表 {} 创建 {}", splitMode, templateName, newTableName);
|
|
// 3. 创建新表（LIKE 模板表，只复制结构+索引，不含数据）
|
patMedOuthospMapper.createPatMedOuthosp(templateName, newTableName);
|
// 4. 设置自增主键
|
patMedOuthospMapper.createPatMedOuthospAutoAdd(newTableName);
|
|
// 5. 重建动态视图
|
refreshOuthospView();
|
|
log.info("[建表] 建表并刷新视图完成，新表：{}", newTableName);
|
return newTableName;
|
}
|
|
/**
|
* 季度分表模板确定：优先上一季度分表，没有则用主表
|
*/
|
private String resolveQuarterTemplateName(int year, String currentQuarterSuffix) {
|
String[][] quarters = {{"0103", "1012"}, {"0406", "0103"}, {"0709", "0406"}, {"1012", "0709"}};
|
String prevSuffix = null;
|
int prevYear = year;
|
for (String[] q : quarters) {
|
if (q[0].equals(currentQuarterSuffix)) {
|
prevSuffix = q[1];
|
if (currentQuarterSuffix.equals("0103")) {
|
prevYear = year - 1;
|
}
|
break;
|
}
|
}
|
if (prevSuffix != null) {
|
String prevTableName = "pat_med_outhosp_" + prevYear + prevSuffix;
|
if (patMedOuthospMapper.tableExists(prevTableName) > 0) {
|
return prevTableName;
|
}
|
}
|
return "pat_med_outhosp";
|
}
|
|
/**
|
* 刷新存储过程 sp_query_outhosp
|
* 逻辑：
|
* 1. 从 information_schema 动态获取 pat_med_outhosp 字段列表
|
* 2. 查出所有分表（后缀为4-8位纯数字）拼 UNION ALL
|
* 3. 根据字段是否存在动态决定存储过程参数和 WHERE 条件
|
* 4. 用原生 JDBC 执行（CREATE PROCEDURE 内含分号，MyBatis 无法处理）
|
*/
|
private void refreshOuthospView() {
|
// 1. 获取所有分表
|
List<String> allTables = patMedOuthospMapper.getAllOuthospTableNames();
|
List<String> partTables = allTables.stream().filter(t -> t.matches("pat_med_outhosp_[0-9]{4,8}")).sorted().collect(java.util.stream.Collectors.toList());
|
|
// 2. 动态获取主表字段列表
|
List<String> columns = patMedOuthospMapper.getTableColumns("pat_med_outhosp");
|
Set<String> cs = new HashSet<>(columns);
|
String colList = String.join(",", columns);
|
|
// 3. 拼 UNION ALL（del_flag 内嵌入 SET @sql='...' 中，用 ''0'' 转义）
|
StringBuilder union = new StringBuilder();
|
for (String tbl : partTables) {
|
union.append("SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0'' UNION ALL ");
|
}
|
union.append("SELECT ").append(colList).append(" FROM pat_med_outhosp WHERE del_flag=''0''");
|
|
// 4. 从 DB 元数据动态构建参数列表
|
// 排除无业务意义的管理字段
|
Set<String> excludeCols = new HashSet<>(java.util.Arrays.asList("id", "del_flag", "create_by", "create_time", "update_by", "update_time", "remark"));
|
// 强制精确匹配的字段（字段名不符合 eqPattern 规则但业务上需精确查询）
|
Set<String> forceEqCols = new HashSet<>(java.util.Arrays.asList("orgid", "guid", "campusid"));
|
// 精确匹配字段的关键词（含 no/code/flag/status/state/num/type/level/grade/sex/gender/phone/tel/id_）
|
java.util.regex.Pattern eqPattern = java.util.regex.Pattern.compile("(no|code|flag|status|state|num|type|level|grade|sex|gender|phone|tel|id_.*|.*_id)$", java.util.regex.Pattern.CASE_INSENSITIVE);
|
|
List<Map<String, String>> colMetaList = patMedOuthospMapper.getTableColumnMeta("pat_med_outhosp");
|
// [paramName, colName, kind, sqlType]
|
List<Object[]> activeParams = new ArrayList<>();
|
Set<String> addedDateCols = new HashSet<>();
|
for (Map<String, String> meta : colMetaList) {
|
String colName = meta.get("column_name");
|
String dataType = meta.get("data_type").toLowerCase();
|
String colType = meta.get("column_type").toUpperCase();
|
if (excludeCols.contains(colName)) continue;
|
switch (dataType) {
|
case "tinyint":
|
case "smallint":
|
case "int":
|
case "bigint":
|
case "integer":
|
case "decimal":
|
case "float":
|
case "double":
|
activeParams.add(new Object[]{"p_" + colName, colName, "num", colType});
|
break;
|
case "date":
|
case "datetime":
|
case "timestamp":
|
// 日期列生成区间参数，防止重复添加
|
if (addedDateCols.add(colName)) {
|
if (colName.equals("admitdate")) {
|
activeParams.add(new Object[]{"p_begin_" + colName, colName, "date_ge", "DATE"});
|
activeParams.add(new Object[]{"p_end_" + colName, colName, "date_le", "DATE"});
|
}
|
}
|
break;
|
default:
|
// varchar / char / text 等字符串类型
|
if (dataType.contains("char") || dataType.contains("text") || dataType.contains("enum")) {
|
if (colName.equals("deptcode")) {
|
activeParams.add(new Object[]{"p_" + colName, colName, "in", colType});
|
break;
|
}
|
if (colName.equals("hospitaldistrictcode")) {
|
activeParams.add(new Object[]{"p_" + colName, colName, "in", colType});
|
break;
|
}
|
String kind = forceEqCols.contains(colName) ? "eq" : (eqPattern.matcher(colName).find() ? "eq" : "like");
|
activeParams.add(new Object[]{"p_" + colName, colName, kind, colType});
|
}
|
break;
|
}
|
}
|
|
// 6. 拼参数定义（去除重复参数名，如 p_begin_xxx/p_end_xxx 同列）
|
StringBuilder paramDef = new StringBuilder("CREATE PROCEDURE sp_query_outhosp(");
|
Set<String> addedParams = new LinkedHashSet<>();
|
for (Object[] m : activeParams) {
|
String pName = (String) m[0];
|
String dtype = (String) m[3];
|
if (addedParams.add(pName)) {
|
paramDef.append("IN ").append(pName).append(" ").append(dtype).append(",");
|
}
|
}
|
paramDef.append("IN p_page_size INT,IN p_page_num INT)");
|
|
// 7. 拼存储过程主体
|
// 优化策略：用 @cond 变量收集所有过滤条件，然后注入到每个子查询的 WHERE 中
|
// 这样 MySQL 可以对每张分表直接走索引，避免全表扫描后外层再过滤
|
List<String> body = new ArrayList<>();
|
body.add(paramDef.toString());
|
body.add("BEGIN");
|
body.add(" SET @cond='';");
|
|
// 7a. 动态拼接 @cond 条件片段
|
for (Object[] m : activeParams) {
|
String pName = (String) m[0];
|
String col = (String) m[1];
|
String kind = (String) m[2];
|
String line;
|
switch (kind) {
|
case "num":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "='," + pName + "); END IF;";
|
break;
|
case "eq":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "=',QUOTE(" + pName + ")); END IF;";
|
break;
|
case "like":
|
// MySQL 存储过程内 '' 表示一个单引号
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + " LIKE CONCAT(''%''," + pName + ",''%'')'); END IF;";
|
break;
|
case "date_ge":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + ">=',QUOTE(" + pName + ")); END IF;";
|
break;
|
case "date_le":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "<=',QUOTE(" + pName + ")); END IF;";
|
break;
|
case "in":
|
// 参数为逗号分隔字符串，用 FIND_IN_SET 匹配字段在集合中
|
// 生成 SQL： FIND_IN_SET(col, 'val1,val2,...')
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND FIND_IN_SET(" + col + ",',QUOTE(" + pName + "),')'); END IF;";
|
break;
|
default:
|
line = "";
|
}
|
if (!line.isEmpty()) body.add(line);
|
}
|
|
// 7b. 构建 UNION ALL SQL：每个子查询的 WHERE 内直接注入 @cond
|
// 最终存储过程里执行的 SQL 类似：
|
// SELECT cols FROM tbl1 WHERE del_flag='0' AND orgid=? AND ...
|
// UNION ALL SELECT cols FROM tbl2 WHERE del_flag='0' AND orgid=? AND ...
|
// ORDER BY update_time DESC LIMIT ? OFFSET ?
|
//
|
// 实现方式：用 CONCAT(单引号字符串, @cond, ...) 拼接
|
// del_flag=''0'' 在存储过程内单引号字符串中表示一个 '0'
|
List<String> allQueryTables = new ArrayList<>(partTables);
|
allQueryTables.add("pat_med_outhosp");
|
|
StringBuilder concatArgs = new StringBuilder();
|
for (int i = 0; i < allQueryTables.size(); i++) {
|
String tbl = allQueryTables.get(i);
|
if (i > 0) {
|
concatArgs.append(",' UNION ALL SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
|
} else {
|
concatArgs.append("'SELECT ").append(colList).append(" FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
|
}
|
}
|
|
String orderCol = cs.contains("update_time") ? "update_time" : "id";
|
body.add(" SET @sql=CONCAT(" + concatArgs + ",' ORDER BY " + orderCol + " DESC');");
|
body.add(" IF p_page_size IS NOT NULL AND p_page_num IS NOT NULL THEN");
|
body.add(" SET @sql=CONCAT(@sql,' LIMIT ',p_page_size,' OFFSET ',p_page_num);");
|
body.add(" END IF;");
|
body.add(" PREPARE stmt FROM @sql;");
|
body.add(" EXECUTE stmt;");
|
body.add(" DEALLOCATE PREPARE stmt;");
|
body.add("END");
|
|
String procSql = String.join("\n", body);
|
log.debug("[刷新存储过程] DDL=\n{}", procSql);
|
|
// 8. 通过原生 JDBC 执行（CREATE PROCEDURE 含分号，MyBatis 不支持）
|
// 策略：
|
// - 存储过程不存在 → 全量创建
|
// - 存储过程已存在 → 只把 DB 中还没有的分表以 UNION ALL 形式追加进 SET @sql=CONCAT(...) 里，
|
// 其余所有条件逻辑（人工改过的模糊/区间等）原样保留，绝不覆盖
|
try (Connection conn = dataSource.getConnection(); Statement jdbcStmt = conn.createStatement()) {
|
|
// 先查 DB 中是否存在该存储过程及其完整 DDL
|
String showCreateDdl = null;
|
try (java.sql.ResultSet rs = jdbcStmt.executeQuery("SHOW CREATE PROCEDURE sp_query_outhosp")) {
|
if (rs.next()) {
|
showCreateDdl = rs.getString("Create Procedure");
|
}
|
} catch (Exception ignored) {
|
// 存储过程不存在时 SHOW CREATE 会抛异常，忽略
|
}
|
|
if (showCreateDdl == null) {
|
// 存储过程不存在，全量创建
|
jdbcStmt.execute(procSql);
|
log.info("[刷新存储过程] sp_query_outhosp 首次创建，分表数量：{}", partTables.size());
|
} else {
|
// 存储过程已存在：找出 DB 里已有哪些分表，把新增的追加进去
|
// 找出当前 DDL 里已出现的所有 pat_med_outhosp_xxx 表名
|
java.util.regex.Matcher tblMatcher = java.util.regex.Pattern.compile("pat_med_outhosp_([0-9]{4,8})").matcher(showCreateDdl);
|
Set<String> existingTables = new java.util.LinkedHashSet<>();
|
while (tblMatcher.find()) {
|
existingTables.add(tblMatcher.group(0));
|
}
|
|
// 找出本次新建后还没进入存储过程的分表
|
List<String> newTables = partTables.stream().filter(t -> !existingTables.contains(t)).collect(java.util.stream.Collectors.toList());
|
|
if (newTables.isEmpty()) {
|
log.info("[刷新存储过程] 无新增分表，存储过程无需更新");
|
} else {
|
// 直接从 DDL 里提取字段列表（避免与 Java 动态查表字段不一致）
|
// DDL 里字段列表就在： SELECT 后面到 FROM 前面
|
java.util.regex.Matcher colMatcher = java.util.regex.Pattern.compile("SELECT\\s+(.+?)\\s+FROM\\s+pat_med_outhosp", java.util.regex.Pattern.CASE_INSENSITIVE).matcher(showCreateDdl);
|
String existingColList = colMatcher.find() ? colMatcher.group(1).trim() : colList;
|
|
// 精确锚点：',@cond,' UNION ALL SELECT cols FROM pat_med_outhosp WHERE
|
String mainTableAnchor = "',@cond,' UNION ALL SELECT " + existingColList + " FROM pat_med_outhosp WHERE ";
|
int mainAnchorIdx = showCreateDdl.indexOf(mainTableAnchor);
|
|
// 字段列表匹配不到时，简化定位
|
if (mainAnchorIdx < 0) {
|
// 找 ',@cond,' UNION ALL SELECT ... FROM pat_med_outhosp WHERE
|
// 先找 FROM pat_med_outhosp WHERE，再往前找 ',@cond,'
|
int fromIdx = showCreateDdl.indexOf(" FROM pat_med_outhosp WHERE ");
|
if (fromIdx >= 0) {
|
String before = showCreateDdl.substring(0, fromIdx);
|
int condIdx = before.lastIndexOf("',@cond,'");
|
if (condIdx >= 0) mainAnchorIdx = condIdx;
|
}
|
}
|
|
if (mainAnchorIdx < 0) {
|
log.warn("[刷新存储过程] 未找到主表锚点，跳过追加");
|
} else {
|
// 新表片段：每张新表追加一段 ',@cond,' UNION ALL SELECT cols FROM 新表 WHERE del_flag=''0''
|
// 插在主表锚点前面，主表锚点本身（',@cond,' UNION ALL ...主表...）保持不动
|
StringBuilder newTablesFragment = new StringBuilder();
|
for (String nt : newTables) {
|
newTablesFragment.append("',@cond,' UNION ALL SELECT ").append(existingColList).append(" FROM ").append(nt).append(" WHERE del_flag=''0''");
|
}
|
|
// 插入到主表 ',@cond,' 片段前面
|
String updatedDdl = showCreateDdl.substring(0, mainAnchorIdx) + newTablesFragment + showCreateDdl.substring(mainAnchorIdx);
|
|
log.debug("[刷新存储过程] 更新后 DDL=\n{}", updatedDdl);
|
|
// 安全保护：DROP 前先保存原 DDL，CREATE 失败则立刻用原 DDL 恢复
|
jdbcStmt.execute("DROP PROCEDURE IF EXISTS sp_query_outhosp");
|
try {
|
jdbcStmt.execute(updatedDdl);
|
log.info("[刷新存储过程] sp_query_outhosp 追加新分表 {}，原有条件保持不变", newTables);
|
} catch (Exception createEx) {
|
log.error("[刷新存储过程] 新 DDL 执行失败，正在用原 DDL 恢复, updatedDdl=\n{}", updatedDdl, createEx);
|
try {
|
jdbcStmt.execute(showCreateDdl);
|
log.info("[刷新存储过程] sp_query_outhosp 已用原 DDL 恢复成功");
|
} catch (Exception rollbackEx) {
|
log.error("[刷新存储过程] 原 DDL 恢复也失败！存储过程丢失！请手动修复, 原 DDL=\n{}", showCreateDdl, rollbackEx);
|
}
|
}
|
}
|
}
|
}
|
} catch (Exception e) {
|
log.error("[刷新存储过程] sp_query_outhosp 更新失败, DDL=\n{}", procSql, e);
|
}
|
|
// 9. 同步刷新 count 存储过程
|
refreshOuthospCountProc(partTables, activeParams, colList);
|
}
|
|
/**
|
* 刷新计数存储过程 sp_count_outhosp
|
* 与 sp_query_outhosp 参数完全一致，只返回总条数，不分页
|
*/
|
private void refreshOuthospCountProc(List<String> partTables, List<Object[]> activeParams, String colList) {
|
// 拼参数定义（去掉 p_page_size/p_page_num）
|
StringBuilder paramDef = new StringBuilder("CREATE PROCEDURE sp_count_outhosp(");
|
Set<String> addedParams = new LinkedHashSet<>();
|
for (Object[] m : activeParams) {
|
String pName = (String) m[0];
|
String dtype = (String) m[3];
|
if (addedParams.add(pName)) {
|
paramDef.append("IN ").append(pName).append(" ").append(dtype).append(",");
|
}
|
}
|
// 去掉最后的多余逗号
|
String paramDefStr = paramDef.toString();
|
if (paramDefStr.endsWith(",")) {
|
paramDefStr = paramDefStr.substring(0, paramDefStr.length() - 1);
|
}
|
paramDefStr += ")";
|
|
List<String> body = new ArrayList<>();
|
body.add(paramDefStr);
|
body.add("BEGIN");
|
body.add(" SET @cond='';");
|
|
// 条件片段（与 sp_query_outhosp 完全一致）
|
for (Object[] m : activeParams) {
|
String pName = (String) m[0];
|
String col = (String) m[1];
|
String kind = (String) m[2];
|
String line;
|
switch (kind) {
|
case "num":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "='," + pName + "); END IF;";
|
break;
|
case "eq":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "=',QUOTE(" + pName + ")); END IF;";
|
break;
|
case "like":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + " LIKE CONCAT(''%''," + pName + ",''%'')'); END IF;";
|
break;
|
case "date_ge":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + ">=',QUOTE(" + pName + ")); END IF;";
|
break;
|
case "date_le":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND " + col + "<=',QUOTE(" + pName + ")); END IF;";
|
break;
|
case "in":
|
line = " IF " + pName + " IS NOT NULL THEN SET @cond=CONCAT(@cond,' AND FIND_IN_SET(" + col + ",',QUOTE(" + pName + "),')'); END IF;";
|
break;
|
default:
|
line = "";
|
}
|
if (!line.isEmpty()) body.add(line);
|
}
|
|
// 拼 COUNT UNION ALL SQL：各分表各计一次再汇总
|
// SELECT SUM(cnt) FROM (SELECT COUNT(1) cnt FROM tbl1 WHERE del_flag=''0'' ...
|
// UNION ALL SELECT COUNT(1) cnt FROM tbl2 WHERE del_flag=''0'' ...) t
|
List<String> allQueryTables = new ArrayList<>(partTables);
|
allQueryTables.add("pat_med_outhosp");
|
|
StringBuilder concatArgs = new StringBuilder();
|
concatArgs.append("'SELECT SUM(cnt) FROM (");
|
for (int i = 0; i < allQueryTables.size(); i++) {
|
String tbl = allQueryTables.get(i);
|
if (i > 0) {
|
concatArgs.append(" UNION ALL SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''''0''''").append("',@cond,'");
|
} else {
|
concatArgs.append("SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''''0''''").append("',@cond,'");
|
}
|
}
|
concatArgs.append(") t';");
|
|
// 重新拼：每个子表 WHERE del_flag=''0'' 后拼 @cond
|
// 存储过程内 '' 表示一个单引号，所以 del_flag=''0'' 正确
|
StringBuilder concatArgs2 = new StringBuilder();
|
for (int i = 0; i < allQueryTables.size(); i++) {
|
String tbl = allQueryTables.get(i);
|
if (i == 0) {
|
concatArgs2.append("'SELECT SUM(cnt) FROM (SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
|
} else {
|
concatArgs2.append(",' UNION ALL SELECT COUNT(1) cnt FROM ").append(tbl).append(" WHERE del_flag=''0''',@cond");
|
}
|
}
|
concatArgs2.append(",' ) t'");
|
|
body.add(" SET @sql=CONCAT(" + concatArgs2 + ");");
|
body.add(" PREPARE stmt FROM @sql;");
|
body.add(" EXECUTE stmt;");
|
body.add(" DEALLOCATE PREPARE stmt;");
|
body.add("END");
|
|
String procSql = String.join("\n", body);
|
log.debug("[刷新count存储过程] DDL=\n{}", procSql);
|
|
try (Connection conn = dataSource.getConnection(); Statement jdbcStmt = conn.createStatement()) {
|
String showCreateDdl = null;
|
try (java.sql.ResultSet rs = jdbcStmt.executeQuery("SHOW CREATE PROCEDURE sp_count_outhosp")) {
|
if (rs.next()) showCreateDdl = rs.getString("Create Procedure");
|
} catch (Exception ignored) {
|
}
|
|
if (showCreateDdl == null) {
|
jdbcStmt.execute(procSql);
|
log.info("[刷新count存储过程] sp_count_outhosp 首次创建，分表数量：{}", partTables.size());
|
} else {
|
// 只追加新分表，不覆盖已有条件
|
java.util.regex.Matcher tblMatcher = java.util.regex.Pattern.compile("pat_med_outhosp_([0-9]{4,8})").matcher(showCreateDdl);
|
Set<String> existingTables = new java.util.LinkedHashSet<>();
|
while (tblMatcher.find()) existingTables.add(tblMatcher.group(0));
|
|
List<String> newTables = partTables.stream().filter(t -> !existingTables.contains(t)).collect(java.util.stream.Collectors.toList());
|
if (newTables.isEmpty()) {
|
log.info("[刷新count存储过程] 无新增分表，无需更新");
|
return;
|
}
|
|
// 从 DDL 里提取 COUNT(1) cnt FROM 后面的字段模式无案，COUNT存储过程没有字段列表
|
// 定位主表锚点：',@cond,' UNION ALL SELECT COUNT(1) cnt FROM pat_med_outhosp WHERE
|
String mainAnchor = "',@cond,' UNION ALL SELECT COUNT(1) cnt FROM pat_med_outhosp WHERE ";
|
int mainAnchorIdx = showCreateDdl.indexOf(mainAnchor);
|
if (mainAnchorIdx < 0) {
|
int fromIdx = showCreateDdl.indexOf(" FROM pat_med_outhosp WHERE ");
|
if (fromIdx >= 0) {
|
String before = showCreateDdl.substring(0, fromIdx);
|
int condIdx = before.lastIndexOf("',@cond,'");
|
if (condIdx >= 0) mainAnchorIdx = condIdx;
|
}
|
}
|
|
if (mainAnchorIdx < 0) {
|
log.warn("[刷新count存储过程] 未找到主表锚点，跳过追加");
|
return;
|
}
|
|
StringBuilder newFrag = new StringBuilder();
|
for (String nt : newTables) {
|
newFrag.append("',@cond,' UNION ALL SELECT COUNT(1) cnt FROM ").append(nt).append(" WHERE del_flag=''0''");
|
}
|
String updatedDdl = showCreateDdl.substring(0, mainAnchorIdx) + newFrag + showCreateDdl.substring(mainAnchorIdx);
|
log.debug("[刷新count存储过程] 更新后 DDL=\n{}", updatedDdl);
|
|
jdbcStmt.execute("DROP PROCEDURE IF EXISTS sp_count_outhosp");
|
try {
|
jdbcStmt.execute(updatedDdl);
|
log.info("[刷新count存储过程] sp_count_outhosp 追加新分表 {}", newTables);
|
} catch (Exception createEx) {
|
log.error("[刷新count存储过程] 新 DDL 失败，恢复原 DDL, updatedDdl=\n{}", updatedDdl, createEx);
|
try {
|
jdbcStmt.execute(showCreateDdl);
|
log.info("[刷新count存储过程] 已恢复原 DDL");
|
} catch (Exception rollbackEx) {
|
log.error("[刷新count存储过程] 恢复也失败！请手动修复, 原 DDL=\n{}", showCreateDdl, rollbackEx);
|
}
|
}
|
}
|
} catch (Exception e) {
|
log.error("[刷新count存储过程] sp_count_outhosp 更新失败", e);
|
}
|
}
|
|
|
}
|
