【Mybatis从入门到实战教程】第五章 Mybatis 关联查询详解

一时失言乱红尘 2024-04-28 13:19 80阅读 0赞

## 五、Mybatis 关联查询 ##

### 5.1 数据模型分析 ###

![watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5rOi5ZOp5Liq5rOi_size_15_color_FFFFFF_t_70_g_se_x_16][]

#### 5.1.1 表功能介绍 ####

*  用户表: 记录用户的基本信息。
 *  订单表: 记录用户所创建的订单（购买商品的订单）。
 *  订单详情表: 记录订单的详细信息，即购买商品的信息。
 *  商品表: 记录商品的基本信息。

#### 5.1.2 表之间的业务关系 ####

**用户表和订单表：**

*  用户表 ---> 订单表: 一个用户可以创建多个订单，一对多关系；
 *  订单表 ---> 用户表: 一个订单只由一个用户创建，一对一关系；

**订单表和订单详情表：**

*  订单表 ---> 订单详情表: 一个订单可以包含多个订单详情，因为一个订单可以购买多个商品，每个商品的购买信息在订单详情表中记录，一对多关系；
 *  订单详情表 ---> 订单表: 一个订单详情只能包括在一个订单中，一对一关系；

**订单详情表和商品表：**

*  订单详情表 ---> 商品表: 一个订单详情只对应一个商品信息，一对一关系；
 *  商品表 ---> 订单详情表: 一个商品可以包括在多个订单详情，一对多关系；

**订单表和商品表：**

*  订单表 <---> 商品表: 一个订单中包含多个商品，一个商品可以添加在多个订单中，两者是通过订单详情表建立关系，多对多关系；

**注意：**

*  如果两张表有主外键关联关系，那么他们的业务关系是一对一/一对多，或者是双向一对一（比如用户表和用户详情表）。
 *  如果两张表是双向一对多关系，那么他们是多对多关系，并且必然存在一张关系描述表作为中间表。

#### 5.1.3 表结构 ####

**用户表：**

CREATE TABLE `users`  (
      `id` int(11) PRIMARY KEY AUTO_INCREMENT,
      `username` varchar(20),
      `password` varchar(50),
      `realname` varchar(20)
    );
    
    INSERT INTO `users` VALUES (1, 'admin', '123456', '管理员');
    INSERT INTO `users` VALUES (2, 'tom', '123', '汤姆');
    INSERT INTO `users` VALUES (3, 'jerry', '456', '杰瑞');
    INSERT INTO `users` VALUES (4, 'zhangsan', '111', '张三');
    INSERT INTO `users` VALUES (5, 'lisi', '222', '李四');

**订单表：**

CREATE TABLE `orders`  (
      `id` int(11) PRIMARY KEY AUTO_INCREMENT,
      `order_number` varchar(30),
      `total_price` double,
      `status` varchar(5),
      `user_id` int(11)
    );
    
    INSERT INTO `orders` VALUES (1, '201812290838001', 2535, '已评价', 2);
    INSERT INTO `orders` VALUES (2, '201812290838002', 4704.6, '已签收', 2);
    INSERT INTO `orders` VALUES (3, '201812290838003', 3620, '已支付', 2);
    INSERT INTO `orders` VALUES (4, '201812290840001', 600, '已发货', 3);
    INSERT INTO `orders` VALUES (5, '201812290840002', 280, '未支付', 3);

**订单详情表：**

CREATE TABLE `orders_detail`  (
      `id` int(11) PRIMARY KEY AUTO_INCREMENT,
      `amount` int(11),
      `goods_id` int(11),
      `orders_id` int(11)
    );
    
    INSERT INTO `orders_detail` VALUES (1, 1, 1, 1);
    INSERT INTO `orders_detail` VALUES (2, 3, 8, 1);
    INSERT INTO `orders_detail` VALUES (3, 1, 2, 2);
    INSERT INTO `orders_detail` VALUES (4, 2, 7, 2);
    INSERT INTO `orders_detail` VALUES (5, 1, 3, 3);
    INSERT INTO `orders_detail` VALUES (6, 6, 6, 3);
    INSERT INTO `orders_detail` VALUES (7, 2, 4, 4);
    INSERT INTO `orders_detail` VALUES (8, 1, 5, 5);

**商品表：**

CREATE TABLE `goods`  (
      `id` int(11) PRIMARY KEY AUTO_INCREMENT,
      `goods_name` varchar(50),
      `description` varchar(500),
      `price` double
    );
    
    INSERT INTO `goods` VALUES (1, '手机', '手机', 2499);
    INSERT INTO `goods` VALUES (2, '笔记本电脑', '笔记本电脑', 4699);
    INSERT INTO `goods` VALUES (3, 'IPAD', 'IPAD', 3599);
    INSERT INTO `goods` VALUES (4, '运动鞋', '运动鞋', 300);
    INSERT INTO `goods` VALUES (5, '外套', '外套', 280);
    INSERT INTO `goods` VALUES (6, '可乐', '可乐', 3.5);
    INSERT INTO `goods` VALUES (7, '辣条', '辣条', 2.8);
    INSERT INTO `goods` VALUES (8, '水杯', '水杯', 12);

### 5.2 一对一查询 ###

#### 5.2.1 需求 ####

查询订单信息。关联如下:  
1、关联查询其相关用户信息。

#### 5.2.2 通过resultType方式实现 ####

**实体类：**

实体类Orders类不能映射全部字段，需要新创建的实体类，创建一个包括查询字段较多的实体类。OrdersQuery中包含了Orders以及Users需要查询的属性。

package com.newcapec.vo;
    
    /**
     * OrdersQuery值对象,不是entity/po,因为它和数据库中表的字段不是对应关系
     */
    public class OrdersQuery {
    
        //订单属性
        private Integer id;
        private String orderNumber;
        private Double totalPrice;
        private String status;
        private Integer userId;
        //用户属性
        private String username;
        private String password;
        private String realname;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getOrderNumber() {
            return orderNumber;
        }
    
        public void setOrderNumber(String orderNumber) {
            this.orderNumber = orderNumber;
        }
    
        public Double getTotalPrice() {
            return totalPrice;
        }
    
        public void setTotalPrice(Double totalPrice) {
            this.totalPrice = totalPrice;
        }
    
        public String getStatus() {
            return status;
        }
    
        public void setStatus(String status) {
            this.status = status;
        }
    
        public Integer getUserId() {
            return userId;
        }
    
        public void setUserId(Integer userId) {
            this.userId = userId;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getRealname() {
            return realname;
        }
    
        public void setRealname(String realname) {
            this.realname = realname;
        }
    
        @Override
        public String toString() {
            return "OrdersQuery{" +
                "id=" + id +
                ", orderNumber='" + orderNumber + '\'' +
                ", totalPrice=" + totalPrice +
                ", status='" + status + '\'' +
                ", userId=" + userId +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", realname='" + realname + '\'' +
                '}';
        }
    }

**mapper接口：**

package com.newcapec.mapper;
    
    import com.newcapec.vo.OrdersQuery;
    
    import java.util.List;
    
    public interface OrdersMapper {
    
        List<OrdersQuery> selectUseResultType();
    }

**mapper文件：**

**测试：**

public class QueryTest {
    
        @Test
        public void testOneToOneResultType() {
            SqlSession sqlSession = MybatisUtil.getSession();
            OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
            List<OrdersQuery> list = ordersMapper.selectUseResultType();
            for (OrdersQuery ordersQuery : list) {
                System.out.println(ordersQuery);
            }
            sqlSession.close();
        }
    }

#### 5.2.3 通过resultMap方式实现 ####

*  **5.2.3.1 实体类**

**用户类：**

package com.newcapec.entity;
    
    public class Users {
    
        private Integer id;
        private String username;
        private String password;
        private String realname;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getUsername() {
            return username;
        }
    
        public void setUsername(String username) {
            this.username = username;
        }
    
        public String getPassword() {
            return password;
        }
    
        public void setPassword(String password) {
            this.password = password;
        }
    
        public String getRealname() {
            return realname;
        }
    
        public void setRealname(String realname) {
            this.realname = realname;
        }
    
        @Override
        public String toString() {
            return "Users{" +
                "id=" + id +
                ", username='" + username + '\'' +
                ", password='" + password + '\'' +
                ", realname='" + realname + '\'' +
                '}';
        }
    }

**订单类：**

在Orders类中加入Users属性，Users属性用于存储关联查询的用户信息。

因为订单关联查询用户是一对一关系，所以这里使用单个Users对象存储关联查询的用户信息。

package com.newcapec.entity;
    
    public class Orders {
    
        private Integer id;
        private String orderNumber;
        private Double totalPrice;
        private String status;
        private Integer userId;
        /**
         * 一对一关系属性
         */
        private Users users;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getOrderNumber() {
            return orderNumber;
        }
    
        public void setOrderNumber(String orderNumber) {
            this.orderNumber = orderNumber;
        }
    
        public Double getTotalPrice() {
            return totalPrice;
        }
    
        public void setTotalPrice(Double totalPrice) {
            this.totalPrice = totalPrice;
        }
    
        public String getStatus() {
            return status;
        }
    
        public void setStatus(String status) {
            this.status = status;
        }
    
        public Integer getUserId() {
            return userId;
        }
    
        public void setUserId(Integer userId) {
            this.userId = userId;
        }
    
        public Users getUsers() {
            return users;
        }
    
        public void setUsers(Users users) {
            this.users = users;
        }
    
        @Override
        public String toString() {
            return "Orders{" +
                    "id=" + id +
                    ", orderNumber='" + orderNumber + '\'' +
                    ", totalPrice=" + totalPrice +
                    ", status='" + status + '\'' +
                    ", userId=" + userId +
                    ", users=" + users +
                    '}';
        }
    }

*  **5.2.3.2 mapper接口**

List<Orders> selectUseResultMap();

*  **5.2.3.3 mapper文件**

association标签: 一对一关系映射描述。  
property: 关系属性名称  
javaType: 关系属性类型

<resultMap id="selectResultMap" type="com.newcapec.entity.Orders">
        <id column="id" property="id"/>
        <result column="order_number" property="orderNumber"/>
        <result column="total_price" property="totalPrice"/>
        <result column="status" property="status"/>
        <result column="user_id" property="userId"/>
        <association property="users" javaType="com.newcapec.entity.Users">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="realname" property="realname"/>
        </association>
    </resultMap>
    <select id="selectUseResultMap" resultMap="selectResultMap">
        select a.id,a.order_number,a.total_price,a.status,a.user_id,b.username,b.password,b.realname
        from orders a, users b where a.user_id=b.id
    </select>

*  **5.2.3.4 测试**

@Test
    public void testOneToOneResultMap() {
        SqlSession sqlSession = MybatisUtil.getSession();
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> list = ordersMapper.selectUseResultMap();
        for (Orders orders : list) {
            System.out.println(orders);
        }
        sqlSession.close();
    }

#### 5.2.4 resultType和resultMap实现一对一查询小结 ####

*  resultType：使用resultType实现较为简单，如果实体类中没有包括查询出来的列名，需要增加列名对应的属性，即可完成映射。如果查询结果没有特殊要求，建议使用resultType。
 *  resultMap：需要单独定义resultMap，实现有点麻烦，如果对查询结果有特殊的要求，使用resultMap可以完成将关联查询映射到实体类的属性中。
 *  resultMap可以实现延迟加载，resultType无法实现延迟加载。

### 5.3 一对多查询 ###

#### 5.3.1 需求 ####

查询订单信息。关联如下：  
1、关联查询其相关用户信息  
2、关联查询其相关订单详情信息。

#### 5.3.2 实体类 ####

**订单详情类：**

public class OrdersDetail {
        
        private Integer id;
        private Integer amount;
        private Integer ordersId;
        private Integer goodsId;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getAmount() {
            return amount;
        }
    
        public void setAmount(Integer amount) {
            this.amount = amount;
        }
    
        public Integer getOrdersId() {
            return ordersId;
        }
    
        public void setOrdersId(Integer ordersId) {
            this.ordersId = ordersId;
        }
    
        public Integer getGoodsId() {
            return goodsId;
        }
    
        public void setGoodsId(Integer goodsId) {
            this.goodsId = goodsId;
        }
    
        @Override
        public String toString() {
            return "OrdersDetail{" +
                    "id=" + id +
                    ", amount=" + amount +
                    ", ordersId=" + ordersId +
                    ", goodsId=" + goodsId +
                    '}';
        }
    }

**订单类：**

在Order类中加入List<OrdersDetail> detailList属性，details属性用于存储关联查询的订单详情。

因为订单关联查询订单详情是一对多关系，所以这里使用集合对象存储关联查询的订单详情信息。

public class Orders {
    
        private Integer id;
        private String orderNumber;
        private Double totalPrice;
        private String status;
        private Integer userId;
        /**
         * 一对一关系属性
         */
        private Users users;
        /**
         * 一对多关系属性
         */
        private List<OrdersDetail> detailList;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getOrderNumber() {
            return orderNumber;
        }
    
        public void setOrderNumber(String orderNumber) {
            this.orderNumber = orderNumber;
        }
    
        public Double getTotalPrice() {
            return totalPrice;
        }
    
        public void setTotalPrice(Double totalPrice) {
            this.totalPrice = totalPrice;
        }
    
        public String getStatus() {
            return status;
        }
    
        public void setStatus(String status) {
            this.status = status;
        }
    
        public Integer getUserId() {
            return userId;
        }
    
        public void setUserId(Integer userId) {
            this.userId = userId;
        }
    
        public Users getUsers() {
            return users;
        }
    
        public void setUsers(Users users) {
            this.users = users;
        }
    
        public List<OrdersDetail> getDetailList() {
            return detailList;
        }
    
        public void setDetailList(List<OrdersDetail> detailList) {
            this.detailList = detailList;
        }
    
        @Override
        public String toString() {
            return "Orders{" +
                    "id=" + id +
                    ", orderNumber='" + orderNumber + '\'' +
                    ", totalPrice=" + totalPrice +
                    ", status='" + status + '\'' +
                    ", userId=" + userId +
                    ", users=" + users +
                    ", detailList=" + detailList +
                    '}';
        }
    }

#### 5.3.3 mapper接口 ####

List<Orders> selectOrdersAndDetail();

#### 5.3.4 mapper文件 ####

collection标签: 一对多关系映射描述。  
property: 关系属性名称  
ofType: 关系属性是一个List集合，集合中存放的元素类型

<resultMap id="detailResultMap" type="com.newcapec.entity.Orders">
        <id column="id" property="id"/>
        <result column="order_number" property="orderNumber"/>
        <result column="total_price" property="totalPrice"/>
        <result column="status" property="status"/>
        <result column="user_id" property="userId"/>
        <association property="users" javaType="com.newcapec.entity.Users">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="realname" property="realname"/>
        </association>
        <collection property="detailList" ofType="com.newcapec.entity.OrdersDetail">
            <id column="detail_id" property="id"/>
            <result column="amount" property="amount"/>
            <result column="goods_id" property="goodsId"/>
            <result column="id" property="ordersId"/>
        </collection>
    </resultMap>
    <select id="selectOrdersAndDetail" resultMap="detailResultMap">
        select a.id,a.order_number,a.total_price,a.status,a.user_id,b.username,b.password,b.realname,
        c.id detail_id,c.amount,c.goods_id
        from orders a
        join users b on a.user_id=b.id
        join orders_detail c on a.id=c.orders_id
    </select>

#### 5.3.5 测试 ####

@Test
    public void testOneToMany() {
        SqlSession sqlSession = MybatisUtil.getSession();
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> list = ordersMapper.selectOrdersAndDetail();
        for (Orders orders : list) {
            System.out.println(orders);
        }
        sqlSession.close();
    }

### 5.4 多对多查询 ###

#### 5.4.1 订单与商品 ####

*  **5.4.1.1 需求**

查询订单信息。关联如下：  
1、关联查询其相关用户信息  
2、关联查询其相关订单详情信息  
3、关联查询订单详情中的商品信息

*  **5.4.1.2 实体类**

将OrderDetail类中Integer类型的goods\_id属性修改为Goods类型属性，goods属性用于存储关联查询的商品信息。

订单与订单详情是一对多关系，订单详情与商品是一对一关系，反之商品与订单详情是一对多关系，订单详情与订单是一对一关系，所以订单与商品之前为多对多关系。

**商品类：**

public class Goods {
    
        private Integer id;
        private String goodsName;
        private String description;
        private Double price;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getGoodsName() {
            return goodsName;
        }
    
        public void setGoodsName(String goodsName) {
            this.goodsName = goodsName;
        }
    
        public String getDescription() {
            return description;
        }
    
        public void setDescription(String description) {
            this.description = description;
        }
    
        public Double getPrice() {
            return price;
        }
    
        public void setPrice(Double price) {
            this.price = price;
        }
    
        @Override
        public String toString() {
            return "Goods{" +
                    "id=" + id +
                    ", goodsName='" + goodsName + '\'' +
                    ", description='" + description + '\'' +
                    ", price=" + price +
                    '}';
        }
    }

**订单详情类：**

public class OrdersDetail {
    
        private Integer id;
        private Integer amount;
        private Integer ordersId;
        private Integer goodsId;
        /**
         * 一对一关系
         */
        private Goods goods;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public Integer getAmount() {
            return amount;
        }
    
        public void setAmount(Integer amount) {
            this.amount = amount;
        }
    
        public Integer getOrdersId() {
            return ordersId;
        }
    
        public void setOrdersId(Integer ordersId) {
            this.ordersId = ordersId;
        }
    
        public Integer getGoodsId() {
            return goodsId;
        }
    
        public void setGoodsId(Integer goodsId) {
            this.goodsId = goodsId;
        }
    
        public Goods getGoods() {
            return goods;
        }
    
        public void setGoods(Goods goods) {
            this.goods = goods;
        }
    
        @Override
        public String toString() {
            return "OrdersDetail{" +
                    "id=" + id +
                    ", amount=" + amount +
                    ", ordersId=" + ordersId +
                    ", goodsId=" + goodsId +
                    ", goods=" + goods +
                    '}';
        }
    }

*  **5.4.1.3 mapper接口**

List<Orders> selectOrdersAndGoods();

*  **5.4.1.4 mapper文件**

<resultMap id="goodsResultMap" type="com.newcapec.entity.Orders">
        <id column="id" property="id"/>
        <result column="order_number" property="orderNumber"/>
        <result column="total_price" property="totalPrice"/>
        <result column="status" property="status"/>
        <result column="user_id" property="userId"/>
        <association property="users" javaType="com.newcapec.entity.Users">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="realname" property="realname"/>
        </association>
        <collection property="detailList" ofType="com.newcapec.entity.OrdersDetail">
            <id column="detail_id" property="id"/>
            <result column="amount" property="amount"/>
            <result column="goods_id" property="goodsId"/>
            <result column="id" property="ordersId"/>
            <association property="goods" javaType="com.newcapec.entity.Goods">
                <id column="goods_id" property="id"/>
                <result column="goods_name" property="goodsName"/>
                <result column="description" property="description"/>
                <result column="price" property="price"/>
            </association>
        </collection>
    </resultMap>
    <select id="selectOrdersAndGoods" resultMap="goodsResultMap">
        select a.id,a.order_number,a.total_price,a.status,a.user_id,b.username,b.password,b.realname,
        c.id detail_id,c.amount,c.goods_id,d.goods_name,d.description,d.price
        from orders a
        join users b on a.user_id=b.id
        join orders_detail c on a.id=c.orders_id
        join goods d on c.goods_id=d.id
    </select>

*  **5.4.1.5 测试**

@Test
    public void testManyToMany1() {
        SqlSession sqlSession = MybatisUtil.getSession();
        OrdersMapper ordersMapper = sqlSession.getMapper(OrdersMapper.class);
        List<Orders> list = ordersMapper.selectOrdersAndGoods();
        for (Orders orders : list) {
            System.out.println(orders);
        }
        sqlSession.close();
    }

#### 5.4.2 学生与课程之间的多对多关系 ####

*  **5.4.2.1 需求**

查询学生信息，并关联查询学生相应的课程信息。

*  **5.4.2.2 表结构**

CREATE TABLE `course`  (
      `id` int(11) PRIMARY KEY AUTO_INCREMENT,
      `cname` varchar(20)
    );
    
    INSERT INTO `course` VALUES (1, '大学语文');
    INSERT INTO `course` VALUES (2, '大学英语');
    INSERT INTO `course` VALUES (3, '高等数学');
    INSERT INTO `course` VALUES (4, 'JAVA语言');
    INSERT INTO `course` VALUES (5, '网络维护');
    INSERT INTO `course` VALUES (6, '通信原理');
    
    CREATE TABLE `student`  (
      `id` int(11) PRIMARY KEY AUTO_INCREMENT,
      `name` varchar(20),
      `gender` varchar(20),
      `major` varchar(20)
    );
    
    INSERT INTO `student` VALUES (1, '小明', '男', '软件工程');
    INSERT INTO `student` VALUES (2, '小红', '女', '网络工程');
    INSERT INTO `student` VALUES (3, '小丽', '女', '物联网');
    
    CREATE TABLE `student_course`  (
      `id` int(11) PRIMARY KEY AUTO_INCREMENT,
      `student_id` int(11),
      `course_id` int(11)
    );
    
    INSERT INTO `student_course` VALUES (1, 1, 1);
    INSERT INTO `student_course` VALUES (2, 1, 3);
    INSERT INTO `student_course` VALUES (3, 1, 4);
    INSERT INTO `student_course` VALUES (4, 2, 1);
    INSERT INTO `student_course` VALUES (5, 2, 2);
    INSERT INTO `student_course` VALUES (6, 2, 5);
    INSERT INTO `student_course` VALUES (7, 3, 2);
    INSERT INTO `student_course` VALUES (8, 3, 3);
    INSERT INTO `student_course` VALUES (9, 3, 6);

*  **5.4.2.3 实体类**

**课程类：**

public class Course {
    
        private Integer id;
        private String cname;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getCname() {
            return cname;
        }
    
        public void setCname(String cname) {
            this.cname = cname;
        }
    
        @Override
        public String toString() {
            return "Course{" +
                    "id=" + id +
                    ", cname='" + cname + '\'' +
                    '}';
        }
    }

**学生类：**

public class Student {
        private Integer id;
        private String name;
        private String gender;
        private String major;
    
        /**
         * 一对多
         */
        private List<Course> courseList;
    
        public Integer getId() {
            return id;
        }
    
        public void setId(Integer id) {
            this.id = id;
        }
    
        public String getName() {
            return name;
        }
    
        public void setName(String name) {
            this.name = name;
        }
    
        public String getGender() {
            return gender;
        }
    
        public void setGender(String gender) {
            this.gender = gender;
        }
    
        public String getMajor() {
            return major;
        }
    
        public void setMajor(String major) {
            this.major = major;
        }
    
        public List<Course> getCourseList() {
            return courseList;
        }
    
        public void setCourseList(List<Course> courseList) {
            this.courseList = courseList;
        }
    
        @Override
        public String toString() {
            return "Student{" +
                    "id=" + id +
                    ", name='" + name + '\'' +
                    ", gender='" + gender + '\'' +
                    ", major='" + major + '\'' +
                    ", courseList=" + courseList +
                    '}';
        }
    }

*  **5.4.2.4 mapper接口**

public interface StudentMapper {
        
        List<Student> select();
    }

*  **5.4.2.5 mapper文件**

<?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.newcapec.mapper.StudentMapper">
    
        <resultMap id="selectResultMap" type="com.newcapec.entity.Student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="gender" property="gender"/>
            <result column="major" property="major"/>
            <collection property="courseList" ofType="com.newcapec.entity.Course">
                <id column="course_id" property="id"/>
                <result column="cname" property="cname"/>
            </collection>
        </resultMap>
        <select id="select" resultMap="selectResultMap">
            select a.id,a.name,a.gender,a.major,b.course_id,c.cname
            from student a
                     join student_course b on a.id=b.student_id
                     join course c ON b.course_id=c.id
        </select>
    </mapper>

*  **5.4.2.6 测试**

@Test
    public void testManyToMany2() {
        SqlSession sqlSession = MybatisUtil.getSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> list = studentMapper.select();
        for (Student student : list) {
            System.out.println(student);
        }
        sqlSession.close();
    }

### 5.5 关联查询总结 ###

#### 5.5.1 resultType ####

作用：将查询结果按照SQL列名与实体类属性名一致性映射到实体类对象中。  
  
场合：常见一些明细记录的展示，比如用户购买商品明细，将关联查询信息全部展示在页面时，此时可直接使用resultType将每一条记录映射到实体类中，在前端页面遍历list（list中是实体类）即可。

#### 5.5.2 resultMap ####

使用association和collection完成一对一和一对多高级映射（对结果有特殊的映射要求）。

**association**

作用：将关联查询信息映射到一个实体类对象中。

场合：为了方便查询关联信息可以使用association将关联信息映射为当前对象的一个属性，比如：查询订单以及关联用户信息。

**collection**

作用：将关联查询信息映射到一个list集合中。

场合：为了方便查询遍历关联信息可以使用collection将关联信息映射到list集合中，比如: 查询用户权限范围模块及模块下的菜单，可使用collection将模块映射到模块list中，将菜单列表映射到模块对象的菜单list属性中，这样的作的目的也是方便对查询结果集进行遍历查询。如果使用resultType无法将查询结果映射到list集合中。

**resultMap的继承**

resultMap标签可以通过extends属性来继承一个已有的或公共的resultMap，避免重复配置的出现，减少配置量。

**例子如下：**

<resultMap id="baseResultMap" type="com.newcapec.entity.Orders">
        <id column="id" property="id"/>
        <result column="order_number" property="orderNumber"/>
        <result column="total_price" property="totalPrice"/>
        <result column="status" property="status"/>
        <result column="user_id" property="userId"/>
    </resultMap>
    
    <resultMap id="subResultMap" type="com.newcapec.entity.Orders" extends="baseResultMap">
        <association property="users" javaType="com.newcapec.entity.Users">
            <id column="id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="realname" property="realname"/>
        </association>
    </resultMap>

[watermark_type_d3F5LXplbmhlaQ_shadow_50_text_Q1NETiBA5oiR5piv5rOi5ZOp5Liq5rOi_size_15_color_FFFFFF_t_70_g_se_x_16]: https://image.dandelioncloud.cn/pgy_files/images/2024/04/28/9480df3160f44f0f9ee47d23393f5a1e.png