Mybatis递归查询树型目录

可参考链接：https://blog.csdn.net/lianzhang861/article/details/86243532

• 效果图
  
• 数据库表
  
• 自定义数据返回值类型

<!-- 自定义返回值类型 -->
<resultMap id="user_group_dto" type="org.hzero.iam.api.controller.v1.dto.UserGroupDTO">
        <id column="user_group_id" property="userGroupId" javaType="java.lang.Long"/>
        <result column="group_code" property="groupCode"/>
        <result column="group_name" property="groupName"/>
        <result column="remark" property="remark"/>
        <result column="enabled_flag" property="enabledFlag"/>
        <result column="tenant_id" property="tenantId"/>
        <result column="group_level" property="groupLevel"/>
        <result column="level_path" property="levelPath"/>
        <result column="order_seq" property="orderSeq"/>
        <!-- 父目录下嵌套子目录，user_group_id用于递归查询 -->
        <collection column="user_group_id" property="children"
                    ofType="org.hzero.iam.api.controller.v1.dto.UserGroupDTO"
                    select="org.hzero.iam.infra.mapper.IamUserGroupMapper.selectChildUserGroup">
        </collection>
    </resultMap>

• sql查询

<!-- 第一次查询，查询的是所有一级目录 -->
<select id="selectUserGroupByGroupLevel" parameterType="entity.UserGroup" resultMap="user_group_dto">
    SELECT
        hug.user_group_id,
        hug.group_code,
        hug.group_name,
        hug.enabled_flag,
        hug.tenant_id,
        hug.remark,
        ht.tenant_name,            
        hug.group_level,
        hug.level_path,
        hug.order_seq,
        hug.parent_group_id
    FROM hiam_user_group hug
    LEFT JOIN hpfm_tenant ht ON hug.tenant_id = ht.tenant_id
    <where>
        hug.group_level = #{groupLevel}
        <if test="userGroupId != null">
            AND hug.user_group_id = #{userGroupId}
        </if>
        
    </where>
    ORDER BY hug.creation_date DESC
</select>

<!-- 子查询，查询一级目录下的子目录 -->
<!-- 返回值类型和父查询返回值类型一致，可以不断进行递归查询 -->
<select id="selectChildUserGroup" resultMap="user_group_dto">
    select
        hug.user_group_id,
        hug.group_code,
        hug.group_name,
        hug.enabled_flag,
        hug.tenant_id,
        hug.remark,
        ht.tenant_name,
        
        hug.group_level,
        hug.level_path,
        hug.order_seq,
        hug.parent_group_id
    FROM hiam_user_group hug
    LEFT JOIN hpfm_tenant ht ON hug.tenant_id = ht.tenant_id
    <where>
    	<!-- 子查询中，父目录id，parent_group_id等于从上次父查询中查出来的id-user_group_id -->
        parent_group_id = #{userGroupId}          
    </where>
</select>