Mybatis 关联查询

在 MyBatis 中，关联查询（也称为复杂映射）是指将多个表的数据通过 SQL 查询和结果映射的方式，组合成一个或多个 Java 对象。这种查询方式用于处理实体之间的关系，如一对一、一对多和多对多关系。通过关联查询，你可以更好地表示现实世界中的业务逻辑，并简化数据访问层的代码。

一、数据准备

1、用户表

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', '李四');

2、订单表

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, '202112290838001', 2535, '已评价', 2);
INSERT INTO `orders` VALUES (2, '202112290838002', 4704.6, '已签收', 2);
INSERT INTO `orders` VALUES (3, '202112290838003', 3620, '已支付', 2);
INSERT INTO `orders` VALUES (4, '202112290840001', 600, '已发货', 3);
INSERT INTO `orders` VALUES (5, '202112290840002', 280, '未支付', 3);

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);

4、商品表

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、数据模型分析

二、一对一查询——订单查询关联用户信息查询

（一）、通过resultType方式实现

1、实体类：

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

package org.example.vo;

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 OrdersQuery() {
    }

    public OrdersQuery(Integer id, String orderNumber, Double totalPrice, String status, Integer userId, String username, String password, String realname) {
        this.id = id;
        this.orderNumber = orderNumber;
        this.totalPrice = totalPrice;
        this.status = status;
        this.userId = userId;
        this.username = username;
        this.password = password;
        this.realname = 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 + '\'' +
                '}';
    }
}

2、mapper接口

import org.example.vo.OrdersQuery;
import java.util.List;

public interface OrdersMapper {
    //通过resultType实现查询订单信息，关联查询相关的用户信息
    List<OrdersQuery> selectUseResultType();
}

3、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="org.example.mapper.OrdersMapper">

    <!--1、ResultType类型实现一对一查询-->
    <select id="selectUseResultType" resultType="org.example.vo.OrdersQuery">
        SELECT o.*,u.username,u.`password`,u.realname FROM orders o,users u WHERE o.user_id=u.id
    </select>
</mapper>

4、测试代码

 //ResultType类型实现一对一查询
    @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();
    }

（二）、通过resultMap方式实现

1、用户类

package org.example.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 + '\'' +
                '}';
    }
}

2、订单类

在Orders类中加入Users属性，Users属性用于存储关联查询的用户信息。
因为订单关联查询用户是一对一关系，所以这里使用单个Users对象存储关联查询的用户信息。

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 +
'}';
}
}

3、mapper接口

//通过resultMap实现查询订单信息，关联查询相关的用户信息
    List<Orders> selectUseResultMap();

4、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="org.example.mapper.OrdersMapper">
<!--2、resultMap实现一对一查询-->
    <resultMap id="selectResultMap" type="org.example.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"/>

        <!--一对一映射
        property:要映射的实体类的属性名
        javaType：指定映射属性的类型
        -->
        <association property="users" javaType="org.example.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 o.id, o.order_number, o.total_price, o.status, o.user_id, u.username,
               u.password, u.realname from orders o, users u where o.user_id=u.id
    </select>

5、测试代码

 //resultMap实现一对一查询
    @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();
    }

三、一对多查询——查询订单信息，关联订单详情信息

1、订单详情类

package org.example.entity;

public class OrderDetail {
    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 "OrderDetail{" +
                "id=" + id +
                ", amount=" + amount +
                ", ordersId=" + ordersId +
                ", goodsId=" + goodsId +
                '}';
    }
}

2、订单类

在Order类中加入List<OrdersDetail> detailList 属性，details属性用于存储关联查询的订单详
情。
因为订单关联查询订单详情是一对多关系，所以这里使用集合对象存储关联查询的订单详情信息。

package org.example.entity;

import java.util.List;

public class Orders {
    private Integer id;
    private String orderNumber;
    private Double totalPrice;
    private String status;
    private Integer userId;


    private Users users;//一对一映射属性：一个订单属于一个用户

    List<OrderDetail> orderDetailList;//一对多关系属性：一个订单包含多个订单详情

    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<OrderDetail> getOrderDetailList() {
        return orderDetailList;
    }

    public void setOrderDetailList(List<OrderDetail> orderDetailList) {
        this.orderDetailList = orderDetailList;
    }

    @Override
    public String toString() {
        return "Orders{" +
                "id=" + id +
                ", orderNumber='" + orderNumber + '\'' +
                ", totalPrice=" + totalPrice +
                ", status='" + status + '\'' +
                ", userId=" + userId +
                ", users=" + users +
                ", orderDetailList=" + orderDetailList +
                '}';
    }
}

3、mapper接口

 //查询订单信息，关联订单详情信息
    List<Orders> selectOrdersAndDetail();

4、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="org.example.mapper.OrdersMapper">

 <!--3、一对多关系属性-->
    <resultMap id="detailResultMap" type="org.example.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="org.example.entity.Users">
            <id column="user_id" property="id"/>
            <result column="username" property="username"/>
            <result column="password" property="password"/>
            <result column="realname" property="realname"/>
        </association>
        <!--一对多映射
        property:订单实体的属性
        ofType：集合里面存储的类型
        -->
        <collection property="orderDetailList" ofType="org.example.entity.OrderDetail">
            <id column="detail_id" property="id"/>
            <result column="amount" property="amount"/>
            <result column="orders_id" property="ordersId"/>
            <result column="goods_id" property="goodsId"/>
        </collection>
    </resultMap>

    <select id="selectOrdersAndDetail" resultMap="detailResultMap">
        SELECT o.*,u.username,u.`password`,u.realname ,d.id detail_id,d.amount,d.orders_id,d.goods_id
        FROM orders o,users u,orders_detail d
        WHERE o.user_id=u.id and o.id=d.orders_id
    </select>
</mapper>

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();
    }

四、多对多查询——查询订单信息，关联查询商品信息

1、商品类

将OrderDetail类中Integer类型的goods_id属性修改为Goods类型属性，goods属性用于存储关联查询的商品信息。
订单与订单详情是一对多关系，订单详情与商品是一对一关系，反之商品与订单详情是一对多关系，订单详情与订单是一对一关系，所以订单与商品为多对多关系。

package org.example.entity;

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 +
                '}';
    }
}

2、订单详情类

package org.example.entity;

public class OrderDetail {
    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 "OrderDetail{" +
                "id=" + id +
                ", amount=" + amount +
                ", ordersId=" + ordersId +
                ", goodsId=" + goodsId +
                ", goods=" + goods +
                '}';
    }
}

3、mapper接口

//查询订单信息，关联查询商品信息
    //订单信息和商品表没有直接关系，在订单详情中定义与商品的关系
    List<Orders> selectOrdersAndGoods();

4、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="org.example.mapper.OrdersMapper">

 <!--4、多对多-->
    <resultMap id="goodsResultMap" type="org.example.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="org.example.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="orderDetailList" ofType="org.example.entity.OrderDetail">
            <id column="detail_id" property="id"/>
            <result column="amount" property="amount"/>
            <result column="orders_id" property="ordersId"/>
            <result column="goods_id" property="goodsId"/>

            <!--多对多-->
            <association property="goods" javaType="org.example.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 o.*,u.username,u.`password`,u.realname ,d.id detail_id,d.amount,d.orders_id,d.goods_id,g.goods_name,g.description,g.price
        FROM orders o,users u,orders_detail d,goods g
        WHERE o.user_id=u.id and o.id=d.orders_id and d.goods_id=g.id
    </select>





</mapper>

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();
    }