Spring+MyBatis使用collection标签的两种使用方法

项目场景：

本文说明了Spring Boot+MyBatis使用collection标签的两种使用方法

1. 方法一: 关联查询
2. 方法二: 嵌套select查询

实战操作：

1.创建菜单表

这里只创建一张表，树结构只有两级，方便学习，多表关联是同样的道理

-- ----------------------------
-- Table structure for menu
-- ----------------------------
DROP TABLE IF EXISTS `menu`;
CREATE TABLE `menu`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '角色名称',
  `pid` int(3) NULL DEFAULT NULL COMMENT '0代表父级',
  `order` int(3) NULL DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of menu
-- ----------------------------
INSERT INTO `menu` VALUES (1, '审核', 0, 1);
INSERT INTO `menu` VALUES (2, '栏目管理', 0, 2);
INSERT INTO `menu` VALUES (3, '应用审核', 1, 1);
INSERT INTO `menu` VALUES (4, '服务审核', 1, 2);
INSERT INTO `menu` VALUES (5, '角色管理', 2, 1);

2.创建实体 

package com.test.entity;

import lombok.Data;
import java.util.List;

@Data
public class Menu {

    private Integer id;
    private String name;
    private Integer pid;
    private Integer order;

    private List<Menu> menus;

}

3.创建Mapper

package com.test.dao;

import com.test.entity.Menu;
import java.util.List;

public interface TestMapper{
    //关联查询
	List<Menu> selectMenu();
    //嵌套查询
	List<Menu> selectNestedMenu();
}

4.创建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.test.dao.TestMapper">
    <!-- 公共字段 -->
    <resultMap id="BaseResultMap" type="com.test.entity.Menu">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="pid" property="pid"/>
        <result column="order" property="order"/>
    </resultMap>

    <!-- 方式一、关联查询：执行一次sql -->
    <!-- 定义resultMap -->
    <resultMap id="MenuResultMap" extends="BaseResultMap" type="com.test.entity.Menu">
        <!-- 关联集合映射，columnPrefix前缀，用于区分相同字段 -->
        <collection ofType="com.test.entity.Menu" resultMap="BaseResultMap" property="menus" columnPrefix="menu_">
        </collection>
    </resultMap>

    <!-- 查询语句 -->
    <select id="selectMenu" resultMap="MenuResultMap">
        select r.id ,r.name ,r.pid ,r.order ,
               r1.id AS menu_id ,r1.name AS menu_name ,r1.pid AS menu_pid,r1.order AS menu_order
        FROM menu r
        LEFT JOIN menu r1 ON r.id = r1.pid
        where r.pid=0
        order by r.order,r1.order
    </select>

    <!-- 方式二、嵌套查询：会执行多次sql -->
    <!-- 定义resultMap -->
    <resultMap id="NestedResultMap" extends="BaseResultMap" type="com.test.entity.Menu">
        <!-- 嵌套查询集合映射 -->
        <collection property="menus" column="id" select="selectChildMenu"/>
    </resultMap>

    <!-- 查询所有父级语句 -->
    <select id="selectNestedMenu" resultMap="NestedResultMap">
        select r.id ,r.name ,r.pid ,r.order
        FROM menu r
        where r.pid=0
        order by r.order
    </select>

    <!-- 查询下级 -->
    <select id="selectChildMenu" resultMap="BaseResultMap">
        select r.id ,r.name ,r.pid ,r.order
        FROM menu r
        where r.pid=#{id}
        order by r.order
    </select>

</mapper>

属性描述：

效率比较：

源码：