mybatis+mysql递归查询

为什么80%的码农都做不了架构师？>>>   

建表：

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<SysMenu> 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<SysMenu> getChildren() {
		return children;
	}

	public void setChildren(List<SysMenu> children) {
		this.children = children;
	}
}

mybatis+mysql实现递归查询：

  <resultMap id="BaseResultMap" type="com.meatball.system.menu.model.SysMenu">
    <id column="id" jdbcType="BIGINT" property="id" />
    <result column="pid" jdbcType="BIGINT" property="pid" />
    <result column="name" jdbcType="VARCHAR" property="name" />
    <result column="url" jdbcType="VARCHAR" property="url" />
    <result column="perms" jdbcType="VARCHAR" property="perms" />
    <result column="type" jdbcType="INTEGER" property="type" />
    <result column="icon" jdbcType="VARCHAR" property="icon" />
    <result column="order_num" jdbcType="INTEGER" property="orderNum" />
    <result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
    <collection property="children" column="id" ofType="com.meatball.system.menu.model.SysMenu" select="selectByPid" />
  </resultMap>

  <!-- 查询所有，使用resultMap返回结果集，将数据组装成树形结构 -->
  <select id="selectAllMenu" resultMap="BaseResultMap">
  	select 
    <include refid="Base_Column_List" />
    from sys_menu
    <where>
    	pid = 0;
    </where>
  </select>
  
  <!-- 根据PID查询菜单 -->
  <select id="selectByPid" parameterType="java.lang.Integer" resultMap="BaseResultMap">
    select 
    <include refid="Base_Column_List" />
    from sys_menu
    <where>
    	pid = #{pid,jdbcType=BIGINT}
    </where>
  </select>

 

转载于:https://my.oschina.net/qjedu/blog/1600860