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 ='' THEN
		with recursive child_dept as
						(
						select id, code,name,name as dept_structure
						from dept
						where id in (
						select DISTINCT dept_id from dept_employee
						where employee_id= employeeId 
						 
						)
						union
						select a.id,a.code ,a.name, concat(b.dept_structure, ' > ', a.name) as dept_structure
						from child_dept as b
						join dept as a on b.code=a.parent_code
						)
						select DISTINCT id from child_dept;
ELSE
   	with recursive child_dept as
						(
						select id, code,name,name as dept_structure
						from dept
						where id in (
						select DISTINCT dept_id from dept_employee
						where employee_id= employeeId and type=type
						)
						union
						select a.id,a.code ,a.name, concat(b.dept_structure, ' > ', a.name) as dept_structure
						from child_dept as b
						join 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.pid
  FROM (SELECT * FROM dept WHERE pid IS NOT NULL) au,
       (SELECT @pid := '1002,1005') pd
 WHERE FIND_IN_SET(pid, @pid) > 0 and @pid := concat(@pid, ',', id)
UNION
SELECT id, name, pid
  FROM dept
 WHERE FIND_IN_SET(id, @pid) > 0
 ORDER BY id;  

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