Two entities: order and commodity. An order can contain multiple commodities, and a commodity can belong to multiple orders, that is, many to many.

Goods table

Order form TB:

no is the order number, and the user "id is associated with the id of the user table.

You need to create a new intermediate table, order item TB, and introduce two "many" primary keys as foreign keys to connect the two "many":

Purchase amount is the purchase quantity of this product.

Implementing one to many with nested results

(1) Writing pojo classes

package com.chy.pojo;

public class Goods {
    private Integer id;  //commodity id
    private String goodsName;  //Trade name
    private float goodsPrice;  //item pricing
    private Integer purchaseAmount;  //Purchase quantity

    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 float getGoodsPrice() {
        return goodsPrice;
    }

    public void setGoodsPrice(float goodsPrice) {
        this.goodsPrice = goodsPrice;
    }

    public Integer getPurchaseAmount() {
        return purchaseAmount;
    }

    public void setPurchaseAmount(Integer purchaseAmount) {
        this.purchaseAmount = purchaseAmount;
    }

    @Override
    public String toString() {
        return "Goods{" +
                "id=" + id +
                ", goodsName='" + goodsName + '\'' +
                ", goodsPrice=" + goodsPrice +
                ", purchaseAmount=" + purchaseAmount +
                '}';
    }
}

package com.chy.pojo;

import java.util.List;

public class Order {
    private Integer no;
    private Integer userId;
    private List<Goods> goodsList;  //Included items

    public Integer getNo() {
        return no;
    }

    public void setNo(Integer no) {
        this.no = no;
    }

    public Integer getUserId() {
        return userId;
    }

    public void setUserId(Integer userId) {
        this.userId = userId;
    }

    public List<Goods> getGoodsList() {
        return goodsList;
    }

    public void setGoodsList(List<Goods> goodsList) {
        this.goodsList = goodsList;
    }

    @Override
    public String toString() {
        return "Order{" +
                "no=" + no +
                ", userId=" + userId +
                ", goodsList=" + goodsList +
                '}';
    }
}

In which pojo to use List to associate other entities, it needs to be determined according to business requirements.

(2) Write Mapper interface and mapping file

package com.chy.mapper;

import com.chy.pojo.Order;

public interface OrderMapper {
    //according to orderId Query order information
    public Order queryOrderByOrderNo(Integer orderNo);
}

<?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.chy.mapper.OrderMapper">
    <select id="queryOrderByOrderNo" parameterType="integer" resultMap="orderResultWithGoods">
        SELECT order_tb.*,goods_tb.*,order_item_tb.purchase_amount
        FROM order_tb,goods_tb,order_item_tb
        WHERE order_tb.no=#{no} AND order_item_tb.order_no=order_tb.no AND goods_tb.id=order_item_tb.goods_id
    </select>
    <resultMap id="orderResultWithGoods" type="order">
        <id property="no" column="no"/>
        <result property="userId" column="user_id"/>
        <collection property="goodsList" ofType="goods">
            <id property="id" column="id"/>
            <result property="goodsName" column="goods_name"/>
            <result property="goodsPrice" column="goods_price"/>
            <result property="purchaseAmount" column="purchase_amount"/>
        </collection>
    </resultMap>
</mapper>

If you think the table name and field name are not easy to write, you can use as to set the alias of the table name and field name.

Not every pojo class needs to write the corresponding Mapper interface and mapping file. What to write depends on the business requirements.

(3) Use

package com.chy.utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtils {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }
}

package com.chy.test;

import com.chy.mapper.OrderMapper;
import com.chy.pojo.Order;
import com.chy.utils.MyBatisUtils;
import org.apache.ibatis.session.*;

public class Test {
    public static void main(String[] args) {
        SqlSession sqlSession = MyBatisUtils.getSqlSession();
        OrderMapper mapper = sqlSession.getMapper(OrderMapper.class);
        Order order = mapper.queryOrderByOrderNo(1);
        System.out.println(order);
        sqlSession.close();
    }
}

Result:

Order{no=1, userId=1, goodsList=[Goods{id=1, goodsName='Tissue', goodsPrice=6.5, purchaseAmount=1}, Goods{id=2, goodsName='The Chinese people', goodsPrice=80.0, purchaseAmount=2}]}

Nested queries can also be used to achieve many to many, but they have many disadvantages and are not recommended.