Mybatis用SQL做自连表查询

1 需求

现在有一个菜单表，菜单里同时保存有一级菜单和二级菜单，一级菜单的父菜单id字段是null，二级菜单的父菜单id字段保存了它的父菜单的id。在java中，一个菜单对象里有一个list类型的属性保存它的子菜单。现在需要将数据库中的数据转为java中菜单兑现的结构。

2 方法

本文采用mybatis，编写sql语句做自连表查询。

3 数据库表

名	类型
id	int
description	varchar
path	varchar
parent_menu_id	int

4 pojo

package com.lpc.labbackend.entity;

import java.util.list;

public class menu {
    private integer id;
    private string description;
    private string path;
    private list<menu> childmenus;
	
    //省略构造函数、getter、setter
}

5 mybatis的mapper.xml

<?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.lpc.labbackend.dao.menumapper">
    <resultmap id="baseresultmap" type="com.lpc.labbackend.entity.menu">
        <id column="id" property="id" jdbctype="integer"/>
        <result column="description" property="description" jdbctype="varchar"/>
        <result column="path" property="path" jdbctype="varchar"/>
        <collection property="childmenus" oftype="com.lpc.labbackend.entity.menu" column="id" select="getchildmenus">
        </collection>
    </resultmap>
    <sql id="base_column_list">
        id, description, path
    </sql>

    <select id="getmenu" resultmap="baseresultmap">
        select
        <include refid="base_column_list"/>
        from menu
        where parent_menu_id is null
    </select>

    <select id="getchildmenus" resultmap="baseresultmap">
        select
        <include refid="base_column_list"/>
        from menu
        where menu.parent_menu_id = #{id}
    </select>
</mapper>

resultmap标签中用一个collection标签表示java里的list，oftype属性表示这个集合的类型。这个list是通过select属性使用了一个查询语句获取数据，column属性是这个查询语句的查询条件。

6 前台接收到的数据

0: {id: 1, icon: "el-icon-user-solid", description: "账户设置", path: null,…}
    id: 1
    icon: "el-icon-user-solid"
    description: "账户设置"
    path: null
    childmenus: [{id: 5, icon: null, description: "所有账户", path: "users", childmenus: null}]
        0: {id: 5, icon: null, description: "所有账户", path: "users", childmenus: null}
        id: 5
        icon: null
        description: "所有账户"
        path: "users"
        childmenus: null
1: {id: 2, icon: "el-icon-s-data", description: "信息统计", path: null,…}
2: {id: 3, icon: "el-icon-s-check", description: "申请批准", path: null,…}

7 参考资料

本文由博客群发一文多发等运营工具平台 openwrite 发布