Mybatis+Mysql 实现向下递归查询

介绍

说到递归查询，大家可以想到的技术实现方式主要如下几种：
1、各种主流应用开发语言本身通过算法实现
2、各种数据库引擎自身提供的算法实现

本文提到主要是针对第二种和第一种的结合

主要技术栈

1、ORM：Mybatis
2、DB：MySQL
3、开发语言：Java

代码实现示例

方式一、Mybatis Mapper类实现代码示例如下

// dept_employee  员工和部门的关系表
// dept 部门信息表
// 根据员工编号查询其直属部门的所有下级部门节点
@Select({"<script>","with recursive child_dept as\n" +"(\n" +"select id, code,name,name as dept_structure\n" +"from dept\n" +"where id in (\n" +"select DISTINCT dept_id from dept_employee\n" +"where employee_id= #{employeeId} " +"<when test='type!=null'>","and type=#{type}\n" +"</when>"+")\n" +"union\n" +"select a.id,a.code ,a.name, concat(b.dept_structure, ' > ', a.name) as dept_structure\n" +"from child_dept as b\n" +"join dept as a on b.code=a.parent_code\n" +")\n" +"select DISTINCT id from child_dept;\t","</script>"})List<Long> queryAllChildDeptByEmployeeId(@Param("employeeId") Long employeeId,@Param("type") String type);

方式二、Mybatis+存储过程示例如下：

    // Mybatis Mapper类调用存储过程@Select("call queryAllChildDeptByEmployeeId(#{employeeId},#{type})")@Options(statementType = StatementType.CALLABLE)List<Long> queryAllChildDeptByEmployeeId(@Param("employeeId") Long employeeId, String type);

//存储过程示例
CREATE DEFINER=`test`@`%` PROCEDURE `queryAllChildDeptByEmployeeId`(IN employeeId bigint,IN type VARCHAR(10))
BEGIN
if type is  null or type ='' THENwith recursive child_dept as(select id, code,name,name as dept_structurefrom deptwhere id in (select DISTINCT dept_id from dept_employeewhere employee_id= employeeId )unionselect a.id,a.code ,a.name, concat(b.dept_structure, ' > ', a.name) as dept_structurefrom child_dept as bjoin dept as a on b.code=a.parent_code)select DISTINCT id from child_dept;
ELSEwith recursive child_dept as(select id, code,name,name as dept_structurefrom deptwhere id in (select DISTINCT dept_id from dept_employeewhere employee_id= employeeId and type=type)unionselect a.id,a.code ,a.name, concat(b.dept_structure, ' > ', a.name) as dept_structurefrom child_dept as bjoin dept as a on b.code=a.parent_code)select DISTINCT id from child_dept;	
END IF;										
END

踩坑介绍

以上实现方式涉及到了MySQL的with as 语法，此实现方式必须给予MySQL 8.0.x版本，5.x.x版本不支持，

解决方案：
1、升级数据库版本为8.x版本
2、采用5.x版本语法FIND_IN_SET,如下代码示例

SELECT au.id, au.name, au.pidFROM (SELECT * FROM dept WHERE pid IS NOT NULL) au,(SELECT @pid := '1002,1005') pdWHERE FIND_IN_SET(pid, @pid) > 0 and @pid := concat(@pid, ',', id)
UNION
SELECT id, name, pidFROM deptWHERE FIND_IN_SET(id, @pid) > 0ORDER BY id;  

3、采用应用开发语言实现，如Java代码实现