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

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

    数据库表： 

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

    1. [
    2.   {
    3.     "id": 1,
    4.     "name": "股东会",
    5.     "parentId": -1,
    6.     "depPath": ".1",
    7.     "enabled": true,
    8.     "isParent": true,
    9.     "children": [
    10.       {
    11.         "id": 2,
    12.         "name": "董事会",
    13.         "parentId": 1,
    14.         "depPath": ".1.2",
    15.         "enabled": true,
    16.         "isParent": true,
    17.         "children": [
    18.           {
    19.             "id": 3,
    20.             "name": "总办",
    21.             "parentId": 2,
    22.             "depPath": ".1.2.3",
    23.             "enabled": true,
    24.             "isParent": true,
    25.             "children": [
    26.               {
    27.                 "id": 4,
    28.                 "name": "财务部",
    29.                 "parentId": 3,
    30.                 "depPath": ".1.2.3.4",
    31.                 "enabled": true,
    32.                 "isParent": false,
    33.                 "children": [],
    34.                 "result": null
    35.               },
    36.               {
    37.                 "id": 5,
    38.                 "name": "市场部",
    39.                 "parentId": 3,
    40.                 "depPath": ".1.2.3.5",
    41.                 "enabled": true,
    42.                 "isParent": true,
    43.                 "children": [
    44.                   {
    45.                     "id": 6,
    46.                     "name": "华东市场部",
    47.                     "parentId": 5,
    48.                     "depPath": "1.2.3.5.6",
    49.                     "enabled": true,
    50.                     "isParent": true,
    51.                     "children": [
    52.                       {
    53.                         "id": 8,
    54.                         "name": "上海市场部",
    55.                         "parentId": 6,
    56.                         "depPath": "1.2.3.5.6.8",
    57.                         "enabled": true,
    58.                         "isParent": false,
    59.                         "children": [],
    60.                         "result": null
    61.                       }
    62.                     ],
    63.                     "result": null
    64.                   },
    65.                   {
    66.                     "id": 7,
    67.                     "name": "华南市场部",
    68.                     "parentId": 5,
    69.                     "depPath": "1.2.3.5.7",
    70.                     "enabled": true,
    71.                     "isParent": false,
    72.                     "children": [],
    73.                     "result": null
    74.                   },
    75.                   {
    76.                     "id": 9,
    77.                     "name": "西北市场部",
    78.                     "parentId": 5,
    79.                     "depPath": ".1.2.3.5.9",
    80.                     "enabled": true,
    81.                     "isParent": true,
    82.                     "children": [
    83.                       {
    84.                         "id": 10,
    85.                         "name": "贵阳市场",
    86.                         "parentId": 9,
    87.                         "depPath": ".1.2.3.5.9.10",
    88.                         "enabled": true,
    89.                         "isParent": true,
    90.                         "children": [
    91.                           {
    92.                             "id": 11,
    93.                             "name": "乌当区市场",
    94.                             "parentId": 10,
    95.                             "depPath": ".1.2.3.5.9.10.11",
    96.                             "enabled": true,
    97.                             "isParent": false,
    98.                             "children": [],
    99.                             "result": null
    100.                           }
    101.                         ],
    102.                         "result": null
    103.                       }
    104.                     ],
    105.                     "result": null
    106.                   }
    107.                 ],
    108.                 "result": null
    109.               },
    110.               {
    111.                 "id": 12,
    112.                 "name": "技术部",
    113.                 "parentId": 3,
    114.                 "depPath": ".1.2.3.12",
    115.                 "enabled": true,
    116.                 "isParent": false,
    117.                 "children": [],
    118.                 "result": null
    119.               },
    120.               {
    121.                 "id": 13,
    122.                 "name": "运维部",
    123.                 "parentId": 3,
    124.                 "depPath": ".1.2.3.13",
    125.                 "enabled": true,
    126.                 "isParent": true,
    127.                 "children": [
    128.                   {
    129.                     "id": 149,
    130.                     "name": "测试部门1",
    131.                     "parentId": 13,
    132.                     "depPath": ".1.2.3.13.149",
    133.                     "enabled": true,
    134.                     "isParent": false,
    135.                     "children": [],
    136.                     "result": null
    137.                   }
    138.                 ],
    139.                 "result": null
    140.               }
    141.             ],
    142.             "result": null
    143.           }
    144.         ],
    145.         "result": null
    146.       }
    147.     ],
    148.     "result": null
    149.   }
    150. ]

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

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

    1. @Data
    2. @EqualsAndHashCode(callSuper = false)
    3. @Accessors(chain = true)
    4. @TableName("t_department")
    5. @ApiModel(value="Department对象", description="")
    6. public class Department implements Serializable {
    7.  
    8.     private static final long serialVersionUID = 1L;
    9.  
    10.     @ApiModelProperty(value = "id")
    11.     @TableId(value = "id", type = IdType.AUTO)
    12.     private Integer id;
    13.  
    14.     @ApiModelProperty(value = "部门名称")
    15.     private String name;
    16.  
    17.     @ApiModelProperty(value = "父id")
    18.     private Integer parentId;
    19.  
    20.     @ApiModelProperty(value = "路径")
    21.     private String depPath;
    22.  
    23.     @ApiModelProperty(value = "是否启用")
    24.     private Boolean enabled;
    25.  
    26.     @ApiModelProperty(value = "是否上级")
    27.     private Boolean isParent;
    28.  
    29.     @ApiModelProperty(value = "子部门列表")
    30.     @TableField(exist = false)
    31.     private List children;
    32.  
    33.     @ApiModelProperty(value = "返回结果，存储过程使用")
    34.     @TableField(exist = false)
    35.     private Integer result;
    36.  
    37. }

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

    ServiceImpl.java

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

    1.     /**
    2.      * 获取所有部门
    3.      * @return
    4.      */
    5.     @Override
    6.     public List getAllDepartments() {
    7.         return departmentMapper.getAllDepartments(-1);
    8.     }

    Mapper.java

    1.     /**
    2.      * 获取所有部门
    3.      * @return
    4.      */
    5.     List getAllDepartments(Integer parentId);

    Mapper.xml

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

    1.     
    2.     <select id="getAllDepartments" resultMap="DepartmentWithChildren" parameterType="Integer">
    3.         select
    4.         <include refid="Base_Column_List" />
    5.         from t_department
    6.         where parentId = #{parentId}
    7.     select>
    8.  
    9.  
    10.      <resultMap id="DepartmentWithChildren" type="com.example.pojo.Department" extends="BaseResultMap">
    11.         
    12.         <collection property="children" ofType="com.example.pojo.Department" select="com.example.mapper.DepartmentMapper.getAllDepartments" column="id">
    13.         collection>
    14.     resultMap>
    15.  
    16.     
    17.     <sql id="Base_Column_List">
    18.         id, name, parentId, depPath, enabled, isParent
    19.     sql>
    20.

     方法二： 通过sql关联查询

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

    查询出来是这样的：

     

    数据库表：

     

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

    1. [
    2.   {
    3.     "id": 2,
    4.     "url": "/",
    5.     "path": "/home",
    6.     "component": "Home",
    7.     "name": "员工资料",
    8.     "iconCls": "fa fa-user-circle-o",
    9.     "keepAlive": null,
    10.     "requireAuth": true,
    11.     "parentId": 1,
    12.     "enabled": true,
    13.     "children": [
    14.       {
    15.         "id": 7,
    16.         "url": "/employee/basic/**",
    17.         "path": "/emp/basic",
    18.         "component": "EmpBasic",
    19.         "name": "基本资料",
    20.         "iconCls": null,
    21.         "keepAlive": null,
    22.         "requireAuth": true,
    23.         "parentId": 2,
    24.         "enabled": true,
    25.         "children": null,
    26.         "roles": null
    27.       }
    28.     ],
    29.     "roles": null
    30.   },
    31.   {
    32.     "id": 3,
    33.     "url": "/",
    34.     "path": "/home",
    35.     "component": "Home",
    36.     "name": "人事管理",
    37.     "iconCls": "fa fa-address-card-o",
    38.     "keepAlive": null,
    39.     "requireAuth": true,
    40.     "parentId": 1,
    41.     "enabled": true,
    42.     "children": [
    43.       {
    44.         "id": 9,
    45.         "url": "/personnel/emp/**",
    46.         "path": "/per/emp",
    47.         "component": "PerEmp",
    48.         "name": "员工资料",
    49.         "iconCls": null,
    50.         "keepAlive": null,
    51.         "requireAuth": true,
    52.         "parentId": 3,
    53.         "enabled": true,
    54.         "children": null,
    55.         "roles": null
    56.       },
    57.       {
    58.         "id": 10,
    59.         "url": "/personnel/ec/**",
    60.         "path": "/per/ec",
    61.         "component": "PerEc",
    62.         "name": "员工奖惩",
    63.         "iconCls": null,
    64.         "keepAlive": null,
    65.         "requireAuth": true,
    66.         "parentId": 3,
    67.         "enabled": true,
    68.         "children": null,
    69.         "roles": null
    70.       },
    71.       {
    72.         "id": 11,
    73.         "url": "/personnel/train/**",
    74.         "path": "/per/train",
    75.         "component": "PerTrain",
    76.         "name": "员工培训",
    77.         "iconCls": null,
    78.         "keepAlive": null,
    79.         "requireAuth": true,
    80.         "parentId": 3,
    81.         "enabled": true,
    82.         "children": null,
    83.         "roles": null
    84.       },
    85.       {
    86.         "id": 12,
    87.         "url": "/personnel/salary/**",
    88.         "path": "/per/salary",
    89.         "component": "PerSalary",
    90.         "name": "员工调薪",
    91.         "iconCls": null,
    92.         "keepAlive": null,
    93.         "requireAuth": true,
    94.         "parentId": 3,
    95.         "enabled": true,
    96.         "children": null,
    97.         "roles": null
    98.       },
    99.       {
    100.         "id": 13,
    101.         "url": "/personnel/remove/**",
    102.         "path": "/per/mv",
    103.         "component": "PerMv",
    104.         "name": "员工调动",
    105.         "iconCls": null,
    106.         "keepAlive": null,
    107.         "requireAuth": true,
    108.         "parentId": 3,
    109.         "enabled": true,
    110.         "children": null,
    111.         "roles": null
    112.       }
    113.     ],
    114.     "roles": null
    115.   },
    116.   {
    117.     "id": 4,
    118.     "url": "/",
    119.     "path": "/home",
    120.     "component": "Home",
    121.     "name": "薪资管理",
    122.     "iconCls": "fa fa-money",
    123.     "keepAlive": null,
    124.     "requireAuth": true,
    125.     "parentId": 1,
    126.     "enabled": true,
    127.     "children": [
    128.       {
    129.         "id": 14,
    130.         "url": "/salary/sob/**",
    131.         "path": "/sal/sob",
    132.         "component": "SalSob",
    133.         "name": "工资账套管理",
    134.         "iconCls": null,
    135.         "keepAlive": null,
    136.         "requireAuth": true,
    137.         "parentId": 4,
    138.         "enabled": true,
    139.         "children": null,
    140.         "roles": null
    141.       },
    142.       {
    143.         "id": 15,
    144.         "url": "/salary/sobcfg/**",
    145.         "path": "/sal/sobcfg",
    146.         "component": "SalSobCfg",
    147.         "name": "员工账套设置",
    148.         "iconCls": null,
    149.         "keepAlive": null,
    150.         "requireAuth": true,
    151.         "parentId": 4,
    152.         "enabled": true,
    153.         "children": null,
    154.         "roles": null
    155.       },
    156.       {
    157.         "id": 16,
    158.         "url": "/salary/table/**",
    159.         "path": "/sal/table",
    160.         "component": "SalTable",
    161.         "name": "工资表管理",
    162.         "iconCls": null,
    163.         "keepAlive": null,
    164.         "requireAuth": true,
    165.         "parentId": 4,
    166.         "enabled": true,
    167.         "children": null,
    168.         "roles": null
    169.       },
    170.       {
    171.         "id": 17,
    172.         "url": "/salary/month/**",
    173.         "path": "/sal/month",
    174.         "component": "SalMonth",
    175.         "name": "月末处理",
    176.         "iconCls": null,
    177.         "keepAlive": null,
    178.         "requireAuth": true,
    179.         "parentId": 4,
    180.         "enabled": true,
    181.         "children": null,
    182.         "roles": null
    183.       },
    184.       {
    185.         "id": 18,
    186.         "url": "/salary/search/**",
    187.         "path": "/sal/search",
    188.         "component": "SalSearch",
    189.         "name": "工资表查询",
    190.         "iconCls": null,
    191.         "keepAlive": null,
    192.         "requireAuth": true,
    193.         "parentId": 4,
    194.         "enabled": true,
    195.         "children": null,
    196.         "roles": null
    197.       }
    198.     ],
    199.     "roles": null
    200.   },
    201.   {
    202.     "id": 5,
    203.     "url": "/",
    204.     "path": "/home",
    205.     "component": "Home",
    206.     "name": "统计管理",
    207.     "iconCls": "fa fa-bar-chart",
    208.     "keepAlive": null,
    209.     "requireAuth": true,
    210.     "parentId": 1,
    211.     "enabled": true,
    212.     "children": [
    213.       {
    214.         "id": 19,
    215.         "url": "/statistics/all/**",
    216.         "path": "/sta/all",
    217.         "component": "StaAll",
    218.         "name": "综合信息统计",
    219.         "iconCls": null,
    220.         "keepAlive": null,
    221.         "requireAuth": true,
    222.         "parentId": 5,
    223.         "enabled": true,
    224.         "children": null,
    225.         "roles": null
    226.       },
    227.       {
    228.         "id": 20,
    229.         "url": "/statistics/score/**",
    230.         "path": "/sta/score",
    231.         "component": "StaScore",
    232.         "name": "员工积分统计",
    233.         "iconCls": null,
    234.         "keepAlive": null,
    235.         "requireAuth": true,
    236.         "parentId": 5,
    237.         "enabled": true,
    238.         "children": null,
    239.         "roles": null
    240.       },
    241.       {
    242.         "id": 21,
    243.         "url": "/statistics/personnel/**",
    244.         "path": "/sta/pers",
    245.         "component": "StaPers",
    246.         "name": "人事信息统计",
    247.         "iconCls": null,
    248.         "keepAlive": null,
    249.         "requireAuth": true,
    250.         "parentId": 5,
    251.         "enabled": true,
    252.         "children": null,
    253.         "roles": null
    254.       },
    255.       {
    256.         "id": 22,
    257.         "url": "/statistics/recored/**",
    258.         "path": "/sta/record",
    259.         "component": "StaRecord",
    260.         "name": "人事记录统计",
    261.         "iconCls": null,
    262.         "keepAlive": null,
    263.         "requireAuth": true,
    264.         "parentId": 5,
    265.         "enabled": true,
    266.         "children": null,
    267.         "roles": null
    268.       }
    269.     ],
    270.     "roles": null
    271.   },
    272.   {
    273.     "id": 6,
    274.     "url": "/",
    275.     "path": "/home",
    276.     "component": "Home",
    277.     "name": "系统管理",
    278.     "iconCls": "fa fa-windows",
    279.     "keepAlive": null,
    280.     "requireAuth": true,
    281.     "parentId": 1,
    282.     "enabled": true,
    283.     "children": [
    284.       {
    285.         "id": 23,
    286.         "url": "/system/basic/**",
    287.         "path": "/sys/basic",
    288.         "component": "SysBasic",
    289.         "name": "基础信息设置",
    290.         "iconCls": null,
    291.         "keepAlive": null,
    292.         "requireAuth": true,
    293.         "parentId": 6,
    294.         "enabled": true,
    295.         "children": null,
    296.         "roles": null
    297.       },
    298.       {
    299.         "id": 24,
    300.         "url": "/system/cfg/**",
    301.         "path": "/sys/cfg",
    302.         "component": "SysCfg",
    303.         "name": "系统管理",
    304.         "iconCls": null,
    305.         "keepAlive": null,
    306.         "requireAuth": true,
    307.         "parentId": 6,
    308.         "enabled": true,
    309.         "children": null,
    310.         "roles": null
    311.       },
    312.       {
    313.         "id": 25,
    314.         "url": "/system/log/**",
    315.         "path": "/sys/log",
    316.         "component": "SysLog",
    317.         "name": "操作日志管理",
    318.         "iconCls": null,
    319.         "keepAlive": null,
    320.         "requireAuth": true,
    321.         "parentId": 6,
    322.         "enabled": true,
    323.         "children": null,
    324.         "roles": null
    325.       },
    326.       {
    327.         "id": 26,
    328.         "url": "/system/admin/**",
    329.         "path": "/sys/admin",
    330.         "component": "SysAdmin",
    331.         "name": "操作员管理",
    332.         "iconCls": null,
    333.         "keepAlive": null,
    334.         "requireAuth": true,
    335.         "parentId": 6,
    336.         "enabled": true,
    337.         "children": null,
    338.         "roles": null
    339.       },
    340.       {
    341.         "id": 27,
    342.         "url": "/system/data/**",
    343.         "path": "/sys/data",
    344.         "component": "SysData",
    345.         "name": "备份恢复数据库",
    346.         "iconCls": null,
    347.         "keepAlive": null,
    348.         "requireAuth": true,
    349.         "parentId": 6,
    350.         "enabled": true,
    351.         "children": null,
    352.         "roles": null
    353.       },
    354.       {
    355.         "id": 28,
    356.         "url": "/system/init/**",
    357.         "path": "/sys/init",
    358.         "component": "SysInit",
    359.         "name": "初始化数据库",
    360.         "iconCls": null,
    361.         "keepAlive": null,
    362.         "requireAuth": true,
    363.         "parentId": 6,
    364.         "enabled": true,
    365.         "children": null,
    366.         "roles": null
    367.       }
    368.     ],
    369.     "roles": null
    370.   }
    371. ]

    对应的返回实体类：

    1. @Data
    2. @EqualsAndHashCode(callSuper = false)
    3. @Accessors(chain = true)
    4. @TableName("t_menu")
    5. @ApiModel(value="Menu对象", description="")
    6. public class Menu implements Serializable {
    7.  
    8.     private static final long serialVersionUID = 1L;
    9.  
    10.     @ApiModelProperty(value = "id")
    11.     @TableId(value = "id", type = IdType.AUTO)
    12.     private Integer id;
    13.  
    14.     @ApiModelProperty(value = "url")
    15.     private String url;
    16.  
    17.     @ApiModelProperty(value = "path")
    18.     private String path;
    19.  
    20.     @ApiModelProperty(value = "组件")
    21.     private String component;
    22.  
    23.     @ApiModelProperty(value = "菜单名")
    24.     private String name;
    25.  
    26.     @ApiModelProperty(value = "图标")
    27.     private String iconCls;
    28.  
    29.     @ApiModelProperty(value = "是否保持激活")
    30.     private Boolean keepAlive;
    31.  
    32.     @ApiModelProperty(value = "是否要求权限")
    33.     private Boolean requireAuth;
    34.  
    35.     @ApiModelProperty(value = "父id")
    36.     private Integer parentId;
    37.  
    38.     @ApiModelProperty(value = "是否启用")
    39.     private Boolean enabled;
    40.  
    41.     @ApiModelProperty(value = "子菜单")
    42.     @TableField(exist = false) // 告诉mybatisplus这个字段不在表中，查询的时候不要去查
    43.     private List children;
    44.  
    45.     @ApiModelProperty(value = "角色列表")
    46.     @TableField(exist = false)
    47.     private List roles;
    48.  
    49. }

    mapper:

        List getMenuByAdminId(Integer id);

    mapper.xml

    1.     
    2.     <resultMap id="BaseResultMap" type="com.example.pojo.Menu">
    3.         <id column="id" property="id" />
    4.         <result column="url" property="url" />
    5.         <result column="path" property="path" />
    6.         <result column="component" property="component" />
    7.         <result column="name" property="name" />
    8.         <result column="iconCls" property="iconCls" />
    9.         <result column="keepAlive" property="keepAlive" />
    10.         <result column="requireAuth" property="requireAuth" />
    11.         <result column="parentId" property="parentId" />
    12.         <result column="enabled" property="enabled" />
    13.     resultMap>
    14.  
    15.     <resultMap id="Menus" type="com.example.pojo.Menu" extends="BaseResultMap">
    16.         
    17.         <collection property="children" ofType="com.example.pojo.Menu">
    18.             <id column="id2" property="id" />
    19.             <result column="url2" property="url" />
    20.             <result column="path2" property="path" />
    21.             <result column="component2" property="component" />
    22.             <result column="name2" property="name" />
    23.             <result column="iconCls2" property="iconCls" />
    24.             <result column="keepAlive2" property="keepAlive" />
    25.             <result column="requireAuth2" property="requireAuth" />
    26.             <result column="parentId2" property="parentId" />
    27.             <result column="enabled2" property="enabled" />
    28.         collection>
    29.     resultMap>
    30.  
    31.  
    32.  
    33.     <select id="getMenuByAdminId" resultMap="Menus">
    34.         select
    35.             DISTINCT
    36.             m1.*,
    37.             m2.id as id2,
    38.             m2.url as url2,
    39.             m2.path as path2,
    40.             m2.component as component2,
    41.             m2.name as name2,
    42.             m2.keepAlive as keepAlive2,
    43.             m2.requireAuth as requireAuth2,
    44.             m2.parentId as parentId2,
    45.             m2.enabled as enabled2
    46.         from
    47.             t_menu m1,
    48.             t_menu m2,
    49.             t_admin_role ar,
    50.             t_menu_role mr
    51.         where
    52.             m1.id =  m2.parentId
    53.           and m2.id = mr.mid
    54.           and mr.rid = ar.rid
    55.           and ar.adminId = #{id}
    56.           and m2.enabled = true
    57.         order by m1.id
    58.     select>

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

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

  • 相关阅读:
    那些自损八百的甲方要求
    【WebGL】VAO和VBO理解
    Oracle快速入门 | 黑马
    Apache Paimon系列之：主键表
    线程池（治理线程的法宝）
    维格云连接功能日志入门教程
    工程机械——起重机导电滑环
    给Hexo添加说说功能
    Typora免费版下载【Mac、Windows】
    Spring Boot中使用XMl文件配置MyBatis
  • 原文地址：https://blog.csdn.net/w13966597931/article/details/128039135