可参考链接: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>