Mybatis多表连接查询——多对多

Mybatis多表连接查询——多对多

有如下四张表；如果要整合他们的信息到一张表；可以使用如下查询语句
SELECT orders.*, user.user_name, user.user_gender, user.user_address,
orderdetail.orderdetail_id, orderdetail.item_id, orderdetail.item_num,
orderdetail.order_id, items.item_name, items.item_price
FROM orders, user, orderdetail, items
WHERE orders.user_id=user.user_id AND orders.order_id = orderdetail.order_id AND orderdetail.item_
orderdetail（订单细节）：

orders（订单）

items（商品信息）

user（用户）

创建表所对应的类

把item装进detail；把detail装进order；把order装进user；最后的结果由user呈现；就把多对多的关系转换成了一对多的关系

Order
在Order中创造List

/**
 * @author Una
 * @date 2022/8/17 21:11
 * @description:
 */

@Data
public class Order {
   
    private Integer orderId;
    private Integer userId;
    private String orderName;
    private Date orderDate;
    private String orderNote;
    private Integer orderNum;
    private List<Detail> detailList;

    public Order() {
   
    }

    public Order(Integer orderId, Integer userId, String orderName, Date orderDate, String orderNote, Integer orderNum, List<Detail> detailList) {
   
        this.orderId = orderId;
        this.userId = userId;
        this.orderName = orderName;
        this.orderDate = orderDate;
        this.orderNote = orderNote;
        this.orderNum = orderNum;
        this.detailList = detailList;
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

Detail
在Detail中创建List

package com.entity;

import lombok.Data;

import java.util.List;

/**
 * @author Una
 * @date 2022/8/17 21:11
 * @description:
 */

@Data
public class Detail {
   
    private Integer orderId;
    private Integer itemId;
    private Double itemNum;
    private Integer detailId;
    private List<Item> itemList;

    public Detail() {
   
    }

    public Detail(Integer orderId, Integer itemId, Double itemNum, Integer detailId, List<Item> itemList) 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25