项目场景:
需要根据部门id向上查出他的父级部门,或者向下查出他的子级部门
问题描述
因为我们公司使用的Mysql版本是5.7 ,不支持 WITH RECURSIVE(Mysql8.0之后的版本才支持)这种简单快捷的递归查询,所以我们使用Mybatis 提供的 collection 来进行递归查询
解决方案:
建表语句:
-- 部门信息表
DROP TABLE IF EXISTS `t_department`;
CREATE TABLE t_department (
department_id INT AUTO_INCREMENT COMMENT '主键',
department_name VARCHAR(100) NOT NULL COMMENT '部门名称',
parent_id INT,
PRIMARY KEY (`department_id`) USING BTREE,
INDEX `idx_id` (`department_id`) USING BTREE,
) COMMENT '部门信息表';
创建对应的实体类:
@Data
@AllArgsConstructor
@NoArgsConstructor
public class TDepartmentVo {
private Integer departmentId;
private String departmentName;
private Integer parentId;
private TDepartmentVo tDepartmentVo;
}
mapper:
TDepartmentVo queryDepartment(@Param("departmentId") Integer departmentId);
mapper.xml:
反向递归(查询父级部门)
<resultMap id="getParent" type="com.sdzh.curbstone.domain.vo.TDepartmentVo">
<id property="departmentId" column="department_id" jdbcType="INTEGER"/>
<result property="departmentName" column="department_name" jdbcType="VARCHAR"/>
<result property="parentId" column="parent_id" jdbcType="INTEGER"/>
<collection property="tDepartmentVo" select="queryDepartment" column="parent_id"></collection>
</resultMap>
<select id="queryDepartment" parameterType="int" resultMap="getParent">
SELECT
a.department_id,
a.department_name,
a.parent_id
FROM t_department AS a
WHERE a.department_id = #{departmentId}
</select>
正向递归(查询子级部门)
<resultMap id="getParent" type="com.sdzh.curbstone.domain.vo.TDepartmentVo">
<id property="departmentId" column="department_id" jdbcType="INTEGER"/>
<result property="departmentName" column="department_name" jdbcType="VARCHAR"/>
<result property="parentId" column="parent_id" jdbcType="INTEGER"/>
<collection property="tDepartmentVo" select="queryDepartment" column="department_id"></collection>
</resultMap>
<select id="queryDepartment" parameterType="int" resultMap="getParent">
SELECT
a.department_id,
a.department_name,
a.parent_id
FROM t_department AS a
WHERE a.parent_id = #{parentId}
</select>
总结:
正向递归和反向递归,主要的配置不同在于主查询语句的查询条件和复合查询语句里collection的column
补充:
因为需要向前端返回的格式为:"总公司->研发部->测试",所以用了封装了两个方法。
{
***********
//向上递归查询出他所有的父级部门
TDepartmentVo vo = tDepartmentService.queryDepartment(departmentId);
String department = buildDepartmentString(vo);
************
}
private static String buildDepartmentString(TDepartmentVo department) {
StringBuilder stringBuilder = new StringBuilder();
departmentRecursive(department, stringBuilder);
return stringBuilder.toString();
}
private static void departmentRecursive(TDepartmentVo department, StringBuilder stringBuilder) {
if (department != null) {
//逆序插入
if (stringBuilder.length() > 0) {
stringBuilder.insert(0, "->");
}
stringBuilder.insert(0, department.getDepartmentName());
//自我调用(遍历) 依次将部门名称取出
departmentRecursive(department.getTDepartmentVo(), stringBuilder);
}
}
最后就可以得到想要的结果了