JavaEE MyBatis關聯對映之多對多（教材學習筆記）

在實際運用中，多對多也是十分常見的，比如一個訂單可能包含多個產品，而每個商品有可能出現在多個訂單中，在資料庫中這樣的情況就需要一張中間表來維護，下面通過一個案例來學習

1.新建三個資料表

其中訂單表在上一票部落格中已經建立過了（點這裡跳轉至上篇部落格），下面展示中間表以及商品表的建立

create table tb_product(
    id int(32) primary key auto_increment,
    name varchar(32),
    price double
);

insert into tb_product values('1','Java基礎入門','44.5');
insert into tb_product values('2','Java Web程式開發入門','38.5');
insert into tb_product values('3','SSM框架整合實戰','50');

create table tb_ordersitem(
    id int(32) primary key auto_increment,
    orders_id int(32),
    product_id int(32),
    foreign key(orders_id) references tb_orders(id),
    foreign key(product_id) references tb_product(id)
);

insert into tb_ordersitem values('1','1','1');
insert into tb_ordersitem values('2','1','3');
insert into tb_ordersitem values('3','3','3');

2.在com.itheima.po包中建立持久化類product，並在類中定義相關屬性和方法，

package com.itheima.po;

import java.util.List;

public class Product {
	private Integer id;//商品id
	private String name;//商品名稱
	private Double price;//商品價格
	private List<Orders> orders;//與訂單的關聯屬性
	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 Double getPrice() {
		return price;
	}
	public void setPrice(Double price) {
		this.price = price;
	}
	public List<Orders> getOrders() {
		return orders;
	}
	public void setOrders(List<Orders> orders) {
		this.orders = orders;
	}
	public String toString() {
		return "Product [id="+id+",name="+name+",price="+price+"]";
	}

}

並在訂單類Orders中新增如下程式碼

private List<Product> productlist;
	public List<Product> getProductlist() {
		return productlist;
	}
	public void setProductlist(List<Product> productlist) {
		this.productlist = productlist;
	}

並重寫Orders中的tostring方法

@Override
	public String toString() {
		return "Orders [id=" + id + ", number=" + number + ", productList=" + productList + "]";
	}

3.建立OrdersMapper.xml檔案，程式碼如下：

<?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.itheima.mapper.OrdersMapper">
    <select id="findOrdersWithProduct" parameterType="Integer" resultMap="OrdersWithProductResult">
        select o.*,p.id as pid,p.name,p.price
        from tb_orders o,tb_product p,tb_ordersitem oi
        where oi.orders_id=o.id
        and oi.product_id=p.id
        and o.id=#{id}
    </select>
    <resultMap type="Orders" id="OrdersWithProductResult">
      <id property="id" column="id"/>
      <result property="number" column="number"/>
      <collection property="productList" ofType="Product">
          <id property="id" column="pid"/>
          <result property="name" column="name"/>
          <result property="price" column="price"/>
      </collection> 
    </resultMap>
</mapper>

4.在mybatis-config中配置OrdersMapper的路徑

<mapper resource="com/itheima/mapper/OrdersMapper.xml"/>

5.建立測試方法

@Test
	public void findOrdersAndPorductTest() {
		SqlSession session = MybatisUtils.getSession();
		Orders orders = session.selectOne("com.itheima.mapper.OrdersMapper.findOrdersWithProduct",1);
		System.out.println(orders);
		//關閉SqlSession
	    session.close();
	}

6.檢視測試結果