博客

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<Detail>

/**
 * @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;
    }
}

Detail

在Detail中创建List<Item>

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) {
        this.orderId = orderId;
        this.itemId = itemId;
        this.itemNum = itemNum;
        this.detailId = detailId;
        this.itemList = itemList;
    }
}

User



在User中创建List<Order>

package com.entity;

import lombok.Data;

import java.util.Date;
import java.util.List;

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

@Data
public class User {
    private Integer userId;
    private String userName;
    private String userGender;
    private Date userBirthday;
    private String userAddress;
    private List<Order> orderList;

    public User(Integer userId) {
        this.userId = userId;
    }

    public User(Integer userId, String userName, String userGender, Date userBirthday, String userAddress, List<Order> orderList) {
        this.userId = userId;
        this.userName = userName;
        this.userGender = userGender;
        this.userBirthday = userBirthday;
        this.userAddress = userAddress;
        this.orderList = orderList;
    }
}

Item

package com.entity;

import lombok.Data;

import java.util.Date;

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

@Data
public class Item {
    private Integer itemId;
    private String iteDetail;
    private Double itemPrice;
    private String itemPicture;
    private Date iteDate;

    public Item() {
    }

    public Item(Integer itemId, String iteDetail, Double itemPrice, String itemPicture, Date iteDate) {
        this.itemId = itemId;
        this.iteDetail = iteDetail;
        this.itemPrice = itemPrice;
        this.itemPicture = itemPicture;
        this.itemDate = itemDate;
    }
}

定义接口类

package com.mapper;

import com.entity.User;

import java.util.List;

/**
 * @author Una
 * @date 2022/8/17 22:39
 * @description:
 */

public interface UserMapper {
    public List<User> getAll();
}

结果集的映射

• OrderDetails List<Item> itemList — 使用assocation
• Orders List<OrderDetails> detailList — 使用collection
• User List<Order> orderList — 使用collection

<?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.mapper.UserMapper">
    <resultMap id="userMap" type="User">
        <result property="userId" column="user_id" javaType="java.lang.Integer"/>
        <result property="userName" column="user_name" javaType="java.lang.String"/>
        <result property="userGender" column="user_gender" javaType="java.lang.String"/>
        <result property="userBirthday" column="user_birthday" javaType="java.util.Date"/>
        <result property="userAddress" column="user_address" javaType="java.lang.String"/>


        <association property="orderList" javaType="Order">
            <result property="orderId" column="order_id" javaType="java.lang.Integer"/>
            <result property="userId" column="user_id" javaType="java.lang.Integer"/>
            <result property="orderName" column="order_name" javaType="java.lang.String"/>
            <result property="orderDate" column="order_createTime" javaType="java.util.Date"/>
            <result property="orderNote" column="order_note" javaType="java.lang.String"/>
            <result property="orderNum" column="order_num" javaType=""/>


            <collection property="detailList" ofType="List">
                <result property="orderId" column="order_id" javaType="java.lang.Integer"/>
                <result property="userId" column="user_id" javaType="java.lang.Integer"/>
                <result property="itemNum" column="item_num" javaType="java.lang.Double"/>
                <result property="detailId" column="orderdetail_id" javaType="java.lang.Integer"/>


                <collection property="itemList" ofType="Item">
                    <result property="itemId" column="item_id" javaType="java.lang.Integer"/>
                    <result property="itemDetail" column="item_detail" javaType="java.lang.String"/>
                    <result property="itemPrice" column="item_price" javaType="java.lang.Integer"/>
                    <result property="itemPicture" column="item_price" javaType="java.lang.String"/>
                    <result property="itemDate" column="createTime" javaType="java.util.Date"/>
                </collection>
            </collection>
        </association>
    </resultMap>
    <select id="getAll" resultMap="userMap">
        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_id=items.item_id
    </select>
</mapper>