转载

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,…}