本次需要擊斃的MySQL函數

函數主要用于獲取部門的完整層級路徑，方便在應用程序或SQL查詢中直接調用，快速獲得部門的上下級關系信息。執行該函數之后簡單使用SQL可以實現數據庫中部門名稱查詢。例如下面sql

select name,GetDepartmentParentNames(du.department_code, du.tenant_id) as department_full_name xxx   from  tableName

CREATE
DEFINER = xxx@`%`
FUNCTION GetDepartmentParentNames(_code VARCHAR(255), _tenant_id VARCHAR(255)) RETURNS TEXT
BEGINDECLARE _name TEXT;DECLARE _parentCode VARCHAR(255);DECLARE _tempName TEXT;DECLARE _tempCode VARCHAR(255);-- 根據傳入的部門code和租戶id，查詢該部門的code、name和父級pidSELECT code, name, pid INTO _tempCode, _tempName, _parentCode FROM table1 WHERE code = _code AND tenant_id = _tenant_id;-- 初始化_name變量，格式為 "code#name"SET _name = CONCAT(_tempCode, '#', _tempName);-- 通過循環，逐級查找父部門，直到父部門pid為'0'（表示無父部門）WHILE _parentCode <> '0' DOSELECT code, name, pid INTO _tempCode, _tempName, _parentCode FROM table1 WHERE code = _parentCode AND tenant_id = _tenant_id;-- 將當前父部門信息拼接到_name前面，格式依然是 "code#name"，用逗號分隔SET _name = CONCAT(CONCAT(_tempCode, '#', _tempName), ',', _name);END WHILE;-- 返回拼接好的字符串，包含從頂級父部門到當前部門的所有層級信息RETURN _name;
END;

如何進行重構解決

分析函數的作用是通過遞歸的方式，基于部門code和tenant_id，逐級向上查找父部門，拼接出完整的部門層級名稱字符串。

方案一采用MySQL8+的CTE實現

WITH RECURSIVE dept_path AS (SELECT code, name, pid, CAST(CONCAT(code, '#', name) AS CHAR(1000)) AS full_pathFROM table1WHERE code = #{department_code} AND tenant_id = #{tenant_id}UNION ALLSELECT d.code, d.name, d.pid, CONCAT(CONCAT(d.code, '#', d.name), ',', dp.full_path)FROM table1 dJOIN dept_path dp ON dp.pid = d.codeWHERE d.tenant_id = #{tenant_id} AND d.pid <> '0'
)
SELECT full_path FROM dept_path WHERE pid = '0' LIMIT 1;

但是我不會這個咋辦，那就換一種實現方式~

方案二應用層或存儲過程外部實現遞歸

涉及機密下述代碼進行過脫敏處理

1. 修改SQL查詢部門部分，直接查詢用戶對應的部門編碼和部門名稱，不調用遞歸函數

  <select id="xxxxx" resultType="xxxxxxx">SELECT du.user_code AS code, du.department_code AS departmentNoFROM table1 duWHERE du.tenant_id = #{tenantId}<if test="userCodes != null and userCodes.size() > 0">AND du.user_code IN<foreach collection="userCodes" item="item" separator="," open="(" close=")">#{item}</foreach></if></select>

調用上述方法獲取到集合之后需要利用Set集合進行去重

     // 提取所有部門code（包括父部門）用于查詢部門信息Set<String> allDeptCodes = new HashSet<>();for (KbUserRoleInfo ud : userDepartments) {allDeptCodes.add(ud.getDepartmentNo());}

2.新增查詢部門信息相關信息sql

  <select id="listDepartmentsByCodes" resultType="xxxxxx">SELECT code, name, pidFROM table1WHERE tenant_id = #{tenantId}AND code IN<foreach collection="codes" item="code" separator="," open="(" close=")">#{code}</foreach></select>

獲取到部門相關信息之后，構建map結構，部門編碼為key，部門信息為value

        List<KbDepartment> departments = sysUserMapper.listDepartmentsByCodes(tenantId, new ArrayList<>(allDeptCodes));Map<String, KbDepartment> departmentMap = departments.stream().collect(Collectors.toMap(KbDepartment::getCode, d -> d));

3. 遞歸補全所有部門信息編碼需要使用編碼查詢部門信息

    /*** 遞歸添加父部門code*/private void addParentDepartments(String deptCode, Map<String, KbDepartment> departmentMap, Set<String> expandedDeptCodes) {KbDepartment dept = departmentMap.get(deptCode);if (dept != null && dept.getPid() != null && !"0".equals(dept.getPid()) && !expandedDeptCodes.contains(dept.getPid())) {expandedDeptCodes.add(dept.getPid());addParentDepartments(dept.getPid(), departmentMap, expandedDeptCodes);}}

4.根據完整部門編碼獲取完整部門信息

        // 再次查詢所有部門信息（包含父部門）List<KbDepartment> allDepartments = sysUserMapper.listDepartmentsByCodes(tenantId, new ArrayList<>(expandedDeptCodes));Map<String, KbDepartment> allDepartmentMap = allDepartments.stream().collect(Collectors.toMap(KbDepartment::getCode, d -> d));

5.構建用戶部門信息映射

// 由于一個用戶會涉及多個組織假如，因此構建Map結構，如果k-v都是簡單的String結構，會出現后面組織覆蓋前面組織情況
Map<String, List<String>> userDeptFullPathMap = new HashMap<>();for (KbUserRoleInfo ud : userDepartments) {String fullPath = buildDepartmentFullPath(ud.getDepartmentNo(), allDepartmentMap);userDeptFullPathMap.computeIfAbsent(ud.getCode(), k -> new ArrayList<>()).add(fullPath);}/*** 遞歸構建部門完整路徑字符串，格式：code#name,code#name,...*/private String buildDepartmentFullPath(String deptCode, Map<String, KbDepartment> departmentMap) {KbDepartment dept = departmentMap.get(deptCode);if (dept == null) {return "";}if ("0".equals(dept.getPid())) {return dept.getCode() + "#" + dept.getName();}String parentPath = buildDepartmentFullPath(dept.getPid(), departmentMap);return parentPath + "," + dept.getCode() + "#" + dept.getName();}

6.部門名稱格式化

    /*** 重置部門名稱*/public void resetDepartmentName() {// 處理部門名稱String departmentName = this.getDepartmentName();// 部門名稱從SQL數據庫查詢的規則是，部門編號#部門名稱, 例如：0001#技術部,000101#開發一組// 這里將部門名稱處理成為 技術部 > 開發一組，通過字符>連接起來if (StrUtil.isBlank(departmentName)) {return;}// 一個組織人員有多個部門的情況List<String> multipartDepartmentNameList = StrUtil.split(departmentName, "/");if (CollUtil.isEmpty(multipartDepartmentNameList)) {return;}List<String> departmentNames = new ArrayList<>();List<String> departmentShortNames = new ArrayList<>();List<String> departmentNos = new ArrayList<>();List<String> orgCodes = new ArrayList<>();for (String oneDepartmentName : multipartDepartmentNameList) {List<String> departmentNameList = StrUtil.split(oneDepartmentName, StrUtil.COMMA);// 獲取一級的組織編號orgCodes.add(DepartmentUtils.getOrgCode(departmentNameList.get(0)));// 設置最后一個部門名稱，獲取departmentNameList的末尾元素String shortName = DepartmentUtils.splitDepartmentName(departmentNameList.get(departmentNameList.size() - 1));departmentShortNames.add(shortName);String sortNo = DepartmentUtils.splitDepartmentNo(departmentNameList.get(departmentNameList.size() - 1));departmentNos.add(sortNo);List<String> names = new LinkedList<>();for (String name : departmentNameList) {names.add(DepartmentUtils.splitDepartmentName(name));}departmentNames.add(StrUtil.join(" > ", names));}this.setOrgCode(StrUtil.join(StrUtil.COMMA, orgCodes));// 設置部門名稱this.setDepartmentName(StrUtil.join(StrUtil.COMMA, departmentNames));this.setDepartmentNo(StrUtil.join(StrUtil.COMMA, departmentNos));this.setDepartmentShortName(StrUtil.join(StrUtil.COMMA, departmentShortNames));}