在实际运用中,多对多也是十分常见的,比如一个订单可能包含多个产品,而每个商品有可能出现在多个订单中,在数据库中这样的情况就需要一张中间表来维护,下面通过一个案例来学习
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.查看测试结果