From d2b45544842581d9f335f06dbad13da02ad5dd60 Mon Sep 17 00:00:00 2001
From: liusheng <337615773@qq.com>
Date: 星期一, 23 三月 2026 18:20:44 +0800
Subject: [PATCH] 修改存储过程字段长度
---
ruoyi-quartz/src/main/java/com/ruoyi/quartz/util/CreateDataTable.java | 587 ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
1 files changed, 587 insertions(+), 0 deletions(-)
diff --git a/ruoyi-quartz/src/main/java/com/ruoyi/quartz/util/CreateDataTable.java b/ruoyi-quartz/src/main/java/com/ruoyi/quartz/util/CreateDataTable.java
new file mode 100644
index 0000000..e9655c3
--- /dev/null
+++ b/ruoyi-quartz/src/main/java/com/ruoyi/quartz/util/CreateDataTable.java
@@ -0,0 +1,587 @@
+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("銆恈reateOuthospQuarterTable銆戝紑濮嬫墽琛岄棬璇婂垎琛ㄤ换鍔�");
+ try {
+ // 浠� sys_config 鏌ヨ鍒嗚〃妯″紡锛� key = outhosp.split.mode
+ String splitMode = configService.selectConfigByKey("outhosp.split.mode");
+ if (org.apache.commons.lang3.StringUtils.isBlank(splitMode)) {
+ log.info("銆恈reateOuthospQuarterTable銆戞湭閰嶇疆 outhosp.split.mode锛屼笉鎵ц鍒嗚〃");
+ return;
+ }
+ splitMode = splitMode.trim();
+ if (!"瀛ｅ害".equals(splitMode) && !"骞�".equals(splitMode) && !"鏈�".equals(splitMode)) {
+ log.warn("銆恈reateOuthospQuarterTable銆戝垎琛ㄦā寮忛厤缃潪娉曪細{}锛屽簲涓� quarter/year/month锛屼笉鎵ц鍒嗚〃", splitMode);
+ return;
+ }
+ String newTable = createPartitionTableAndRefreshView(splitMode);
+ if (newTable == null) {
+ log.info("銆恈reateOuthospQuarterTable銆戝綋鍓嶅懆鏈熻〃宸插瓨鍦紝鏃犻渶鎿嶄綔");
+ } else {
+ log.info("銆恈reateOuthospQuarterTable銆戞柊寤哄垎琛ㄦ垚鍔燂細{}锛屽凡鍒锋柊瑙嗗浘 v_pat_med_outhosp", newTable);
+ }
+ } catch (Exception e) {
+ log.error("銆恈reateOuthospQuarterTable銆戝缓琛ㄥ紓甯�", 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)) {
+ // 鎸夊勾锛歱at_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)) {
+ // 鎸夋湀锛歱at_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. 鍒涘缓鏂拌〃锛圠IKE 妯℃澘琛紝鍙鍒剁粨鏋�+绱㈠紩锛屼笉鍚暟鎹級
+ 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 鎵ц锛圕REATE PROCEDURE 鍐呭惈鍒嗗彿锛孧yBatis 鏃犳硶澶勭悊锛�
+ */
+ 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锛坉el_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", "TEXT"});
+ break;
+ }
+ if (colName.equals("hospitaldistrictcode")) {
+ activeParams.add(new Object[]{"p_" + colName, colName, "in", "TEXT"});
+ 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 鎵ц锛圕REATE 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);
+
+ // 瀹夊叏淇濇姢锛欴ROP 鍓嶅厛淇濆瓨鍘� DDL锛孋REATE 澶辫触鍒欑珛鍒荤敤鍘� 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);
+ }
+ }
+
+
+}
--
Gitblit v1.9.3