Mybatis通过collection递归查询菜单树的实现方法

最新推荐文章于 2024-07-10 20:29:52 发布

CodeXu_cyber

最新推荐文章于 2024-07-10 20:29:52 发布

阅读量1.8k

点赞数 1

文章标签： java 开发语言后端

本文链接：https://blog.csdn.net/qq_16469323/article/details/123076652

版权

在业务开发中我们可能会遇到菜单树的查询之类的需求，即实体中包含本实体的list或数组，层数不定，下面为借助Mybatis的collection的一种方法

现有表结构如下

domain如下

package tech.niua.auth.domain;

import com.baomidou.mybatisplus.annotation.IdType;
import com.baomidou.mybatisplus.annotation.TableField;
import com.baomidou.mybatisplus.annotation.TableId;
import com.baomidou.mybatisplus.annotation.TableName;
import lombok.AllArgsConstructor;
import lombok.Builder;
import lombok.Data;
import lombok.NoArgsConstructor;

import java.util.List;

@Data
@Builder
@AllArgsConstructor
@NoArgsConstructor
public class SysResource {
    @TableId(type= IdType.AUTO)
    private Long id;

    private String name;

    private Integer orderNum;

    private String permission;

    private Integer type;

    private String url;

    private Long parentId;

    private Boolean available;

    @TableField(exist=false)
    private List<SysResource> childResources;

}

xml我们这样写

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="tech.niua.auth.mapper.SysResourceMapper">
  <resultMap id="BaseResultMap" type="tech.niua.auth.domain.SysResource">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="available" jdbcType="BIT" property="available" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="order_num" jdbcType="INTEGER" property="orderNum" />
    <result column="permission" jdbcType="VARCHAR" property="permission" />
    <result column="type" jdbcType="INTEGER" property="type" />
    <result column="url" jdbcType="VARCHAR" property="url" />
    <result column="parent_id" jdbcType="BIGINT" property="parentId" />
   <!--连接查询，获取子栏目的资源信息-->
  <collection property="childResources" column="id" ofType="tech.niua.auth.domain.SysResource" select="selectResourceByParentId" />
  </resultMap>
  <sql id="Base_Column_List">
    id, available, name, order_num, permission, type, url, parent_id
  </sql>
    <select id="selectResourceByParentId" parameterType="java.lang.Long"  resultMap="BaseResultMap">
        SELECT
        sys_resource.id as id,
        sys_resource.available as available,
        sys_resource.`name` as name ,
        sys_resource.permission as permission,
        sys_resource.order_num as order_num,
        sys_resource.type as type,
        sys_resource.url as url,
        sys_resource.parent_id as parent_id
        FROM
        sys_resource
        where parent_id = #{id} order by order_num
  </select>
</mapper>