mysql mybatis 递归查询语句_mybatis+mysql递归查询

建表：

CREATE TABLE `sys_menu` (

`id` bigint(20) NOT NULL AUTO_INCREMENT,

`pid` bigint(20) DEFAULT NULL COMMENT '父菜单ID，一级菜单为0',

`name` varchar(50) DEFAULT NULL COMMENT '菜单名称',

`url` varchar(200) DEFAULT NULL COMMENT '菜单URL',

`perms` varchar(500) DEFAULT NULL COMMENT '授权(多个用逗号分隔，如：user:list,user:create)',

`type` int(11) DEFAULT NULL COMMENT '类型 0：目录 1：菜单 2：按钮',

`icon` varchar(50) DEFAULT NULL COMMENT '菜单图标',

`order_num` int(11) DEFAULT NULL COMMENT '排序',

`create_time` datetime DEFAULT NULL COMMENT '创建时间',

PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=66 DEFAULT CHARSET=utf8 COMMENT='菜单管理'

建JavaBean：

/**

* Project Name:meatball-admin

* File Name:MenuController.java

* Package Name:com.meatball.system.menu.controller

* Date:2018年1月1日下午5:04:03

* Copyright (c) 2018, zhang.xiangyu@foxmail.com All Rights Reserved.

*/

package com.meatball.system.menu.model;

import java.io.Serializable;

import java.util.Date;

import java.util.List;

/**

* @Title: SysMenu.java

* @Package com.meatball.system.menu.model

* @Description: TODO(菜单)

* @author 暴雪首席执行官

* @date 2018年1月2日 下午10:33:42

* @version V1.0

*/

public class SysMenu implements Serializable {

private static final long serialVersionUID = 1L;

// 主键

private Long id;

// 父ID

private Long pid;

// 名称

private String name;

// 链接地址

private String url;

// 授权(多个用逗号分隔，如：user:list,user:create)

private String perms;

// 类型 0：目录 1：菜单 2：按钮

private Integer type;

// 菜单图标

private String icon;

// 排序

private Integer orderNum;

// 创建时间

private Date createTime;

private List children;

public Long getId() {

return id;

}

public void setId(Long id) {

this.id = id;

}

public Long getPid() {

return pid;

}

public void setPid(Long pid) {

this.pid = pid;

}

public String getName() {

return name;

}

public void setName(String name) {

this.name = name;

}

public String getUrl() {

return url;

}

public void setUrl(String url) {

this.url = url;

}

public String getPerms() {

return perms;

}

public void setPerms(String perms) {

this.perms = perms;

}

public Integer getType() {

return type;

}

public void setType(Integer type) {

this.type = type;

}

public String getIcon() {

return icon;

}

public void setIcon(String icon) {

this.icon = icon;

}

public Integer getOrderNum() {

return orderNum;

}

public void setOrderNum(Integer orderNum) {

this.orderNum = orderNum;

}

public Date getCreateTime() {

return createTime;

}

public void setCreateTime(Date createTime) {

this.createTime = createTime;

}

public List getChildren() {

return children;

}

public void setChildren(List children) {

this.children = children;

}

}

mybatis+mysql实现递归查询：

select

from sys_menu

pid = 0;

select

from sys_menu

pid = #{pid,jdbcType=BIGINT}