树形表，自关联表查询技巧

方法一：部门表，部门表中除了自身主键id外，还有另一个字段parentId父id，可以一直递归下去

数据库表： 

菜单这样展示就需要我们在接口的返回值中，返回这样的层级数据：

[
  {
    "id": 1,
    "name": "股东会",
    "parentId": -1,
    "depPath": ".1",
    "enabled": true,
    "isParent": true,
    "children": [
      {
        "id": 2,
        "name": "董事会",
        "parentId": 1,
        "depPath": ".1.2",
        "enabled": true,
        "isParent": true,
        "children": [
          {
            "id": 3,
            "name": "总办",
            "parentId": 2,
            "depPath": ".1.2.3",
            "enabled": true,
            "isParent": true,
            "children": [
              {
                "id": 4,
                "name": "财务部",
                "parentId": 3,
                "depPath": ".1.2.3.4",
                "enabled": true,
                "isParent": false,
                "children": [],
                "result": null
              },
              {
                "id": 5,
                "name": "市场部",
                "parentId": 3,
                "depPath": ".1.2.3.5",
                "enabled": true,
                "isParent": true,
                "children": [
                  {
                    "id": 6,
                    "name": "华东市场部",
                    "parentId": 5,
                    "depPath": "1.2.3.5.6",
                    "enabled": true,
                    "isParent": true,
                    "children": [
                      {
                        "id": 8,
                        "name": "上海市场部",
                        "parentId": 6,
                        "depPath": "1.2.3.5.6.8",
                        "enabled": true,
                        "isParent": false,
                        "children": [],
                        "result": null
                      }
                    ],
                    "result": null
                  },
                  {
                    "id": 7,
                    "name": "华南市场部",
                    "parentId": 5,
                    "depPath": "1.2.3.5.7",
                    "enabled": true,
                    "isParent": false,
                    "children": [],
                    "result": null
                  },
                  {
                    "id": 9,
                    "name": "西北市场部",
                    "parentId": 5,
                    "depPath": ".1.2.3.5.9",
                    "enabled": true,
                    "isParent": true,
                    "children": [
                      {
                        "id": 10,
                        "name": "贵阳市场",
                        "parentId": 9,
                        "depPath": ".1.2.3.5.9.10",
                        "enabled": true,
                        "isParent": true,
                        "children": [
                          {
                            "id": 11,
                            "name": "乌当区市场",
                            "parentId": 10,
                            "depPath": ".1.2.3.5.9.10.11",
                            "enabled": true,
                            "isParent": false,
                            "children": [],
                            "result": null
                          }
                        ],
                        "result": null
                      }
                    ],
                    "result": null
                  }
                ],
                "result": null
              },
              {
                "id": 12,
                "name": "技术部",
                "parentId": 3,
                "depPath": ".1.2.3.12",
                "enabled": true,
                "isParent": false,
                "children": [],
                "result": null
              },
              {
                "id": 13,
                "name": "运维部",
                "parentId": 3,
                "depPath": ".1.2.3.13",
                "enabled": true,
                "isParent": true,
                "children": [
                  {
                    "id": 149,
                    "name": "测试部门1",
                    "parentId": 13,
                    "depPath": ".1.2.3.13.149",
                    "enabled": true,
                    "isParent": false,
                    "children": [],
                    "result": null
                  }
                ],
                "result": null
              }
            ],
            "result": null
          }
        ],
        "result": null
      }
    ],
    "result": null
  }
]

返回这样的数据：其中有个简单的方法

1： 修改数据库表对应的实体类，给实体类添加一个字段 children

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_department")
@ApiModel(value="Department对象", description="")
public class Department implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "id")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "部门名称")
    private String name;

    @ApiModelProperty(value = "父id")
    private Integer parentId;

    @ApiModelProperty(value = "路径")
    private String depPath;

    @ApiModelProperty(value = "是否启用")
    private Boolean enabled;

    @ApiModelProperty(value = "是否上级")
    private Boolean isParent;

    @ApiModelProperty(value = "子部门列表")
    @TableField(exist = false)
    private List<Department> children;

    @ApiModelProperty(value = "返回结果，存储过程使用")
    @TableField(exist = false)
    private Integer result;

}

2. 正常的写Controlelr，service，下面给出ServiceImpl后面的代码

ServiceImpl.java

这里-1是 顶级节点的parentId为-1

    /**
     * 获取所有部门
     * @return
     */
    @Override
    public List<Department> getAllDepartments() {
        return departmentMapper.getAllDepartments(-1);
    }

Mapper.java

    /**
     * 获取所有部门
     * @return
     */
    List<Department> getAllDepartments(Integer parentId);

Mapper.xml

定义一个返回的Map，在这个Map中，通过collection标签的 select 属性，来给这个集合赋值，每次赋值就再调用一下 getAllDepartments方法查询一次，携带的参数为第一次查询时候的主键id

    <!--获取所有部门-->
    <select id="getAllDepartments" resultMap="DepartmentWithChildren" parameterType="Integer">
        select
        <include refid="Base_Column_List" />
        from t_department
        where parentId = #{parentId}
    </select>


     <resultMap id="DepartmentWithChildren" type="com.example.pojo.Department" extends="BaseResultMap">
        <!--select 相当是拿到children，如何拿到值呢，那就再调用一下这个方法，传id-->
        <collection property="children" ofType="com.example.pojo.Department" select="com.example.mapper.DepartmentMapper.getAllDepartments" column="id">
        </collection>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, parentId, depPath, enabled, isParent
    </sql>

 方法二： 通过sql关联查询

比如 二级树，左侧的菜单查询

查询出来是这样的：

 

数据库表：

 

返回的数据格式是这样的：

[
  {
    "id": 2,
    "url": "/",
    "path": "/home",
    "component": "Home",
    "name": "员工资料",
    "iconCls": "fa fa-user-circle-o",
    "keepAlive": null,
    "requireAuth": true,
    "parentId": 1,
    "enabled": true,
    "children": [
      {
        "id": 7,
        "url": "/employee/basic/**",
        "path": "/emp/basic",
        "component": "EmpBasic",
        "name": "基本资料",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 2,
        "enabled": true,
        "children": null,
        "roles": null
      }
    ],
    "roles": null
  },
  {
    "id": 3,
    "url": "/",
    "path": "/home",
    "component": "Home",
    "name": "人事管理",
    "iconCls": "fa fa-address-card-o",
    "keepAlive": null,
    "requireAuth": true,
    "parentId": 1,
    "enabled": true,
    "children": [
      {
        "id": 9,
        "url": "/personnel/emp/**",
        "path": "/per/emp",
        "component": "PerEmp",
        "name": "员工资料",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 3,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 10,
        "url": "/personnel/ec/**",
        "path": "/per/ec",
        "component": "PerEc",
        "name": "员工奖惩",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 3,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 11,
        "url": "/personnel/train/**",
        "path": "/per/train",
        "component": "PerTrain",
        "name": "员工培训",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 3,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 12,
        "url": "/personnel/salary/**",
        "path": "/per/salary",
        "component": "PerSalary",
        "name": "员工调薪",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 3,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 13,
        "url": "/personnel/remove/**",
        "path": "/per/mv",
        "component": "PerMv",
        "name": "员工调动",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 3,
        "enabled": true,
        "children": null,
        "roles": null
      }
    ],
    "roles": null
  },
  {
    "id": 4,
    "url": "/",
    "path": "/home",
    "component": "Home",
    "name": "薪资管理",
    "iconCls": "fa fa-money",
    "keepAlive": null,
    "requireAuth": true,
    "parentId": 1,
    "enabled": true,
    "children": [
      {
        "id": 14,
        "url": "/salary/sob/**",
        "path": "/sal/sob",
        "component": "SalSob",
        "name": "工资账套管理",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 4,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 15,
        "url": "/salary/sobcfg/**",
        "path": "/sal/sobcfg",
        "component": "SalSobCfg",
        "name": "员工账套设置",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 4,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 16,
        "url": "/salary/table/**",
        "path": "/sal/table",
        "component": "SalTable",
        "name": "工资表管理",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 4,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 17,
        "url": "/salary/month/**",
        "path": "/sal/month",
        "component": "SalMonth",
        "name": "月末处理",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 4,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 18,
        "url": "/salary/search/**",
        "path": "/sal/search",
        "component": "SalSearch",
        "name": "工资表查询",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 4,
        "enabled": true,
        "children": null,
        "roles": null
      }
    ],
    "roles": null
  },
  {
    "id": 5,
    "url": "/",
    "path": "/home",
    "component": "Home",
    "name": "统计管理",
    "iconCls": "fa fa-bar-chart",
    "keepAlive": null,
    "requireAuth": true,
    "parentId": 1,
    "enabled": true,
    "children": [
      {
        "id": 19,
        "url": "/statistics/all/**",
        "path": "/sta/all",
        "component": "StaAll",
        "name": "综合信息统计",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 5,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 20,
        "url": "/statistics/score/**",
        "path": "/sta/score",
        "component": "StaScore",
        "name": "员工积分统计",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 5,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 21,
        "url": "/statistics/personnel/**",
        "path": "/sta/pers",
        "component": "StaPers",
        "name": "人事信息统计",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 5,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 22,
        "url": "/statistics/recored/**",
        "path": "/sta/record",
        "component": "StaRecord",
        "name": "人事记录统计",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 5,
        "enabled": true,
        "children": null,
        "roles": null
      }
    ],
    "roles": null
  },
  {
    "id": 6,
    "url": "/",
    "path": "/home",
    "component": "Home",
    "name": "系统管理",
    "iconCls": "fa fa-windows",
    "keepAlive": null,
    "requireAuth": true,
    "parentId": 1,
    "enabled": true,
    "children": [
      {
        "id": 23,
        "url": "/system/basic/**",
        "path": "/sys/basic",
        "component": "SysBasic",
        "name": "基础信息设置",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 6,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 24,
        "url": "/system/cfg/**",
        "path": "/sys/cfg",
        "component": "SysCfg",
        "name": "系统管理",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 6,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 25,
        "url": "/system/log/**",
        "path": "/sys/log",
        "component": "SysLog",
        "name": "操作日志管理",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 6,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 26,
        "url": "/system/admin/**",
        "path": "/sys/admin",
        "component": "SysAdmin",
        "name": "操作员管理",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 6,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 27,
        "url": "/system/data/**",
        "path": "/sys/data",
        "component": "SysData",
        "name": "备份恢复数据库",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 6,
        "enabled": true,
        "children": null,
        "roles": null
      },
      {
        "id": 28,
        "url": "/system/init/**",
        "path": "/sys/init",
        "component": "SysInit",
        "name": "初始化数据库",
        "iconCls": null,
        "keepAlive": null,
        "requireAuth": true,
        "parentId": 6,
        "enabled": true,
        "children": null,
        "roles": null
      }
    ],
    "roles": null
  }
]

对应的返回实体类：

@Data
@EqualsAndHashCode(callSuper = false)
@Accessors(chain = true)
@TableName("t_menu")
@ApiModel(value="Menu对象", description="")
public class Menu implements Serializable {

    private static final long serialVersionUID = 1L;

    @ApiModelProperty(value = "id")
    @TableId(value = "id", type = IdType.AUTO)
    private Integer id;

    @ApiModelProperty(value = "url")
    private String url;

    @ApiModelProperty(value = "path")
    private String path;

    @ApiModelProperty(value = "组件")
    private String component;

    @ApiModelProperty(value = "菜单名")
    private String name;

    @ApiModelProperty(value = "图标")
    private String iconCls;

    @ApiModelProperty(value = "是否保持激活")
    private Boolean keepAlive;

    @ApiModelProperty(value = "是否要求权限")
    private Boolean requireAuth;

    @ApiModelProperty(value = "父id")
    private Integer parentId;

    @ApiModelProperty(value = "是否启用")
    private Boolean enabled;

    @ApiModelProperty(value = "子菜单")
    @TableField(exist = false) // 告诉mybatisplus这个字段不在表中，查询的时候不要去查
    private List<Menu> children;

    @ApiModelProperty(value = "角色列表")
    @TableField(exist = false)
    private List<Role> roles;

}

mapper:

    List<Menu> getMenuByAdminId(Integer id);

mapper.xml

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.example.pojo.Menu">
        <id column="id" property="id" />
        <result column="url" property="url" />
        <result column="path" property="path" />
        <result column="component" property="component" />
        <result column="name" property="name" />
        <result column="iconCls" property="iconCls" />
        <result column="keepAlive" property="keepAlive" />
        <result column="requireAuth" property="requireAuth" />
        <result column="parentId" property="parentId" />
        <result column="enabled" property="enabled" />
    </resultMap>

    <resultMap id="Menus" type="com.example.pojo.Menu" extends="BaseResultMap">
        <!--子分类菜单   property:属性-->
        <collection property="children" ofType="com.example.pojo.Menu">
            <id column="id2" property="id" />
            <result column="url2" property="url" />
            <result column="path2" property="path" />
            <result column="component2" property="component" />
            <result column="name2" property="name" />
            <result column="iconCls2" property="iconCls" />
            <result column="keepAlive2" property="keepAlive" />
            <result column="requireAuth2" property="requireAuth" />
            <result column="parentId2" property="parentId" />
            <result column="enabled2" property="enabled" />
        </collection>
    </resultMap>


 <!--通过用户id查询菜单列表-->
    <select id="getMenuByAdminId" resultMap="Menus">
        select
            DISTINCT
            m1.*,
            m2.id as id2,
            m2.url as url2,
            m2.path as path2,
            m2.component as component2,
            m2.name as name2,
            m2.keepAlive as keepAlive2,
            m2.requireAuth as requireAuth2,
            m2.parentId as parentId2,
            m2.enabled as enabled2
        from
            t_menu m1,
            t_menu m2,
            t_admin_role ar,
            t_menu_role mr
        where
            m1.id =  m2.parentId
          and m2.id = mr.mid
          and mr.rid = ar.rid
          and ar.adminId = #{id}
          and m2.enabled = true
        order by m1.id
    </select>

这里用关联查询，这几张表分别是 菜单表，角色菜单表，用户角色表，关联关系如下：

通过关联查出来的结果直接用Map中的 colection给放到实体类中的children属性里即可一次性查出来