记一个递归查菜单的sql格外的好用

业务层

    /**
     * 查询代理商菜单
     * @return
     */
    @Override
    public List<Menu> getAgentMenu() {
        return menuMapper.agentMenuList(-1);
    }

XML  文件

    /**
     * 查询代理商权限
     * @param i
     * @return
     */
    List<Menu> agentMenuList(int i);


// xml 

    <select id="agentMenuList" resultMap="AgentMenuAllMap">
        SELECT * FROM tb_menu WHERE parent_id = #{pid} AND is_del = 0 AND m_type = 2 ORDER BY order_num ASC
    </select>

    <resultMap id="AgentMenuAllMap" type="com..system.entity.Menu" extends="commonMap">
        <collection
                property="children"
                ofType="com.mxktech.system.entity.Menu"
                column="menu_id"
                select="agentMenuList">
        </collection>
    </resultMap>

    <resultMap id="commonMap" type="com.mxktech.system.entity.Menu">
        <id         column="menu_id"                property="menuId"       />
        <result     column="parent_id"              property="parentId"     />
        <result     column="path"                   property="path"         />
        <result     column="type"                   property="type"         />
        <result     column="permission"             property="permission"   />
        <result     column="component"              property="component"   />
        <result     column="icon"                   property="icon"         />
        <result     column="title"                  property="title"        />
        <result     column="name"                   property="name"         />
        <result     column="description"            property="description"  />
        <result     column="hidden"                 property="hidden"       />
        <result     column="order_num"               property="orderNum"    />
        <result     column="is_del"                  property="isDel"        />
        <result     column="create_time"             property="createTime"  />
        <result     column="create_user"             property="createUser"  />
        <result     column="update_time"             property="updateTime"  />
        <result     column="update_user"             property="updateUser"  />
    </resultMap>

结果会显示这样一个菜单的层级：