MyBatis递归查询（菜单树状结构）

我们有时候需要使用MyBatis（MyBatis-plus）的递归查询来生成树状结构的菜单项（不仅仅是菜单可以用到，比如机构部门的结构都会用到）。所以在此处记录一下如何通过MyBatis（MyBatis-plus）来生成树状结构的数据。

1.设计菜单表

2.创建菜单实体类

package com.hk.employeemanagementsystem.entity;

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 java.util.List;

/**
 * @author by 何坤
 * @Classname MenuEntity
 * @Description TODO
 * @Date 2019/12/9 16:26
 */
@TableName("tab_menu")
public class MenuEntity {
    @TableId(value = "menu_id", type = IdType.AUTO)
    private Integer id;

    @TableField(value = "menu_name")
    private String name;

    @TableField(value = "menu_path")
    private String path;

    @TableField(value = "menu_url")
    private String url;

    @TableField(value = "menu_component")
    private String component;

    @TableField(value = "menu_icon_class")
    private String icon;

    @TableField(value = "menu_parent_id")
    private Integer parentId;

    @TableField(value = "menu_enabled")
    private Boolean enabled;

    @TableField(exist = false)
    private MenuMetaEntity meta;

    @TableField(exist = false)
    private List<MenuEntity> childrenMenu;

    // 注意，为了节省篇幅，此处省略了getter/setter方法
}

3.创建MapperEntityMapper接口和MapperEntityMapper.xml文件

package com.hk.employeemanagementsystem.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.hk.employeemanagementsystem.entity.MenuEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;

import java.util.List;

/**
 * @author by 何坤
 * @Classname MenuEntityMapper
 * @Description TODO
 * @Date 2019/12/19 11:13
 */
@Mapper
public interface MenuEntityMapper extends BaseMapper<MenuEntity> {
    /**
     * 获取菜单项
     * @return java.util.List<com.hk.employeemanagementsystem.entity.MenuEntity>
     * @date 2019/12/19 14:57
     * @author 何坤
     */
    MenuEntity selectMenuEntityByRole();
}

<?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="com.hk.employeemanagementsystem.mapper.MenuEntityMapper">
    <resultMap id="menuEntityMap" type="com.hk.employeemanagementsystem.entity.MenuEntity">
        <id column="menu_id" property="id" jdbcType="INTEGER"/>
        <result column="menu_name" property="name" jdbcType="VARCHAR"/>
        <result column="menu_path" property="path" jdbcType="VARCHAR"/>
        <result column="menu_url" property="url" jdbcType="VARCHAR"/>
        <result column="menu_parent_id" property="parentId" jdbcType="INTEGER"/>
        <result column="menu_icon_class" property="icon" jdbcType="VARCHAR"/>
        <result column="menu_component" property="component" jdbcType="VARCHAR"/>
        <result column="menu_enabled" property="enabled"/>
        <association property="meta" javaType="com.hk.employeemanagementsystem.entity.MenuMetaEntity">
            <result column="menu_keep_alive" property="keepAlive"/>
            <result column="menu_require_auth" property="requireAuth"/>
        </association>
        <collection column="menu_id"
                    property="childrenMenu"
                    ofType="com.hk.employeemanagementsystem.entity.MenuEntity"
                    javaType="java.util.ArrayList" select="selectMenuEntityChildrenByParentId">

        </collection>
    </resultMap>

    <select id="selectMenuEntityByRole" resultMap="menuEntityMap" parameterType="String">
        SELECT *
        FROM tab_menu LEFT JOIN tab_role_menu trm
        ON tab_menu.menu_id = trm.rm_menu_id
        LEFT JOIN tab_role tr
        ON trm.rm_role_id = tr.role_id
    </select>


    <select id="selectMenuEntityChildrenByParentId" parameterType="Integer" resultMap="menuEntityMap">
        SELECT * FROM tab_menu WHERE menu_parent_id = #{menu_id}
    </select>
</mapper>

4.注意项

通过以上几步就可以完成树状结构的查询，其中的重点是MenuEntityMapper.xml文件的reslutMap，其中的子菜单的集合childrenMenu是通过一个查询语句来映射的。

<collection column="menu_id"
                    property="childrenMenu"
                    ofType="com.hk.employeemanagementsystem.entity.MenuEntity"
                    javaType="java.util.ArrayList" select="selectMenuEntityChildrenByParentId">
</collection>

其中的column属性是指将每条记录中的menu_id作为select属性指定的sql语句的参数。查询出来的数据再映射为menuEntityMap，依次递归查询构建，最终完成树状结构数据的构建。