背景
在做部门管理的时候,部门可以有父部门和子部门,这是个典型的树形结构。在选择一个部门的父部门时需要排除其所有子部门。如A12的可选父部门就是红圈外的所有部门。
框架
mybatis 3.2.8
mysql 5.7
springMVC
由于mybatis中没有oracle中那么多省力的函数,我需要拿到所有可选父部门的id,名称,父部门名称,送到前端用zTree显示。所以需要手写存储过程去拿到结果集再组装成json。
代码
mybatis dept.xml
{call PRO_GET_ALL_CHILD_DEPT(
#{companyId,jdbcType=BIGINT,mode=IN},
#{deptId,jdbcType=BIGINT,mode=IN})}
存储过程
BEGIN
DECLARE str varchar(1000) DEFAULT '0'; /**tips**/
DECLARE childList VARCHAR(1000);
SET childList = CAST(rootId AS CHAR);
WHILE childList IS NOT NULL DO
SET str = CONCAT(str, ',', childList);
SELECT GROUP_CONCAT(id) INTO childList FROM company_depart WHERE FIND_IN_SET(parent_id, childList);
END WHILE;
select id, dept_name, parent_id from company_depart where company_id = companyId and is_deleted=0 and not FIND_IN_SET(id,str);
END
sql测试
call PRO_GET_ALL_CHILD_DEPT(0,116);
由于GROUP_CONCAT连接起来的id会被当做一个整体对待,所以在后面进行排除的时候不能用not in (str)进行筛选,需要用函数FIND_IN_SET取反完成。
单元测试代码
@Test
public void testGetChildDept(){
List data = dao.getChildDeptId(0L, 116L);
System.out.println(Arrays.deepToString(data.toArray()));
}
遇到的问题
1.ReflectionException There is no setter for property 查看实体类属性和映射文件字段是否匹配
2.测试配置文件的数据库配置错误
3.数据不全 MySql中group_concat是有长度限制的,查看:show variables like 'group_concat_max_len',修改:SET GLOBAL group_concat_max_len = 400000。如果数据库重启了,它会恢复原值。所以也可以在配置文件修改my.ini,配置:group_concat_max_len = 102400。
4.记得提前清理互为父部门的脏数据,否则会死循环的。
相关资料
MySql函数