目录
一对多:一本书对应多种书本类型,例如:西游记 -> 神话、古典、名著
多对一:多本书指向一种书本类型,例如:西游记、山海经、聊斋志异 -> 神话
一个项目由多个人来开发,例如:A项目由张三、李四、王五来开发
一对多:一个A类类型对应多个B类类型的情况,需要在A类中以集合的方式引入B类类型的对象,在B类中定义A类类型的属性a, 在多的一方,添加一的一方的主键作为外键
2.通过mybatis-generator插件生成dao、mapper、model层
3..配置mybatis关联映射文件------CustomerMapper.xml
建立实体层面的关联关系: 多对一:多个订单对应一个客户(一个订单对应唯一一个客户)
2.配置mybatis关联映射文件------OrderMapper.xml
在元素中,包含了一个子元素,MyBatis通过该元素来处理一对多关联关系。
在元素中,包含了一个子元素,MyBatis就是通过该元素来处理一对一关联关系。
2.通过mybatis-generator插件生成dao、mapper、model层
关联关系是指类之间的引用关系,如果类A与类B关联,那么类A将被定义成类B的属性。
以下例子可以表现类之间的引用关系:
Adress类:
- public class Address{
- private String name;
- }
B类:
- public class B{
- private String sid;
- private Float score;
- private Address address;
- }
adress这个类被定义成类B的属性,可以通过B类来调用adress类的相关属性,它们之间存在着引用关系
- -- 一对多
- -- 客户表(主表)
- create table t_customer
- (
- customer_id int primary key not null auto_increment,
- customer_name varchar(50) not null
- );
-
- -- 多对一
- -- 订单表(从表)
- create table t_order
- (
- order_id int primary key not null auto_increment,
- order_no varchar(50) not null unique,
- cid int not null,
- foreign key(cid) references t_customer(customer_id)
- );
-
-
- -- 初始化代码
- delete from t_order;
- delete from t_customer;
-
- -- 与mssql不一样,自动增长是可以赋值,也可以不赋值
- insert into t_customer(customer_id,customer_name) values(1, 'zs');
- insert into t_customer(customer_id,customer_name) values(2, 'ls');
- insert into t_customer(customer_id,customer_name) values(3, 'ww');
- insert into t_customer(customer_id,customer_name) values(4, 'xm');
-
- insert into t_order(order_no,cid) values('P01',1);
- insert into t_order(order_no,cid) values('P02',1);
- insert into t_order(order_no,cid) values('P03',1);
- insert into t_order(order_no,cid) values('P04',1);
- insert into t_order(order_no,cid) values('P05',1);
- insert into t_order(order_no,cid) values('P06',2);
- insert into t_order(order_no,cid) values('P07',2);
- insert into t_order(order_no,cid) values('P08',2);
- insert into t_order(order_no,cid) values('P09',3);
- insert into t_order(order_no,cid) values('P10',3);
-
-
-
- SELECT * FROM t_customer;
- SELECT * FROM t_order;
如果有兴趣了解插件的配置,可以参考之前的博客:
- 建立实体层面的关联关系:
一对多:一个客户(customer)对应多个订单(order)
如果要建立一对多的关系,则要在客户实体类建立关系:
private List orders=new ArrayList<>();
- package com.tangyuan.model;
-
- import lombok.ToString;
-
- import java.util.ArrayList;
- import java.util.List;
-
- /**
- * 建立实体层面的关联关系:
- * 一对多:一个客户(customer)对应多个订单(order)
- */
-
- public class Customer {
- private Integer customerId;
-
- private String customerName;
-
- //A:订单(Order) B:客户(Customer)
- //一对多
- private List
orders=new ArrayList<>(); -
-
- public Customer(Integer customerId, String customerName) {
- this.customerId = customerId;
- this.customerName = customerName;
- }
-
- public List
getOrders() { - return orders;
- }
-
- public void setOrders(List
orders) { - this.orders = orders;
- }
-
- public Customer() {
- super();
- }
-
- public Integer getCustomerId() {
- return customerId;
- }
-
- public void setCustomerId(Integer customerId) {
- this.customerId = customerId;
- }
-
- public String getCustomerName() {
- return customerName;
- }
-
- public void setCustomerName(String customerName) {
- this.customerName = customerName;
- }
-
- @Override
- public String toString() {
- return "Customer{" +
- "customerId=" + customerId +
- ", customerName='" + customerName + '\'' +
- ", orders=" + orders +
- '}';
- }
- }
- package com.tangyuan.mapper;
-
- import com.tangyuan.model.Customer;
- import org.springframework.stereotype.Repository;
-
- import java.util.List;
-
- @Repository
- public interface CustomerMapper {
-
- /**
- * 查询所有
- * @return
- */
- List
queryCustomer(); -
-
- }
- <resultMap id="one2many" type="Customer">
- <id column="customer_id" property="customerId"/>
- <result column="customer_name" property="customerName"/>
-
-
- <collection property="orders" ofType="Order">
- <id column="order_id" property="orderId"/>
- <result column="order_no" property="orderNo"/>
- collection>
- resultMap>
在
Property:指定映射到的实体类对象属性,与表字段一一对应
Column:指定表中对应的字段
javaType:指定映射到实体对象属性的类型
Select:指定引入嵌套查询的子SQL语句,该属性用于关联映射中的嵌套查询
完整代码如下:
- mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
- <mapper namespace="com.tangyuan.mapper.CustomerMapper" >
- <resultMap id="BaseResultMap" type="com.tangyuan.model.Customer" >
- <constructor >
- <idArg column="customer_id" jdbcType="INTEGER" javaType="java.lang.Integer" />
- <arg column="customer_name" jdbcType="VARCHAR" javaType="java.lang.String" />
- constructor>
- resultMap>
- <resultMap id="oneToMany" type="com.tangyuan.model.Customer">
-
- <id property="customerId" javaType="java.lang.Integer" column="order_id"/>
- <result property="customerName" javaType="java.lang.String" column="customer_name"/>
-
- <collection property="orders" ofType="com.tangyuan.model.Order" column="cid">
- <id property="cid" javaType="java.lang.Integer" column="cid"/>
- <id property="orderId" javaType="java.lang.Integer" column="order_id"/>
- <result property="orderNo" javaType="java.lang.String" column="order_no"/>
- collection>
- resultMap>
- <sql id="Base_Column_List" >
- customer_id, customer_name
- sql>
- <select id="queryCustomer" resultMap="oneToMany">
- SELECT * FROM t_customer c left join
- t_order o on c.customer_id=o.cid;
- select>
-
- mapper>
注意事项,使用左外连接而非内连接,为了防止因为内连接的特性而让有一些客户因为没有订单而消失不见
- package com.tangyuan.service;
-
- import com.tangyuan.model.Customer;
- import org.springframework.stereotype.Repository;
-
- import java.util.List;
-
-
- public interface ICustomerService {
-
- /**
- * 查询所有
- * @return
- */
- List
queryCustomer(); -
-
- }
- package com.tangyuan.service.impl;
-
- import com.tangyuan.mapper.CustomerMapper;
- import com.tangyuan.model.Customer;
- import com.tangyuan.service.ICustomerService;
- import com.tangyuan.service.IOrderService;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
-
- import java.util.List;
-
- /**
- * @author 唐渊
- * @create 2022-07-27 11:29
- */
- @Service
- public class CustomerServiceImpl implements ICustomerService {
- @Autowired
- private CustomerMapper customerMapper;
-
-
- @Override
- public List
queryCustomer() { - return customerMapper.queryCustomer();
- }
- }
- package com.tangyuan.service.impl;
-
- import com.tangyuan.model.Customer;
- import com.tangyuan.model.Order;
- import com.tangyuan.service.ICustomerService;
- import com.tangyuan.service.IOrderService;
-
- import org.junit.Test;
- import org.springframework.beans.factory.annotation.Autowired;
-
-
- import java.util.List;
-
- import static org.junit.Assert.*;
-
- /**
- * @author 唐渊
- * @create 2022-07-27 11:32
- */
- public class CustomerServiceImplTest extends BaseTest{
-
- @Autowired
- //用接口接收代理对象
- private ICustomerService customerService;
-
-
- @Test
- public void queryCustomer() {
- List
customers = customerService.queryCustomer(); - customers.forEach(System.out::println);
-
- }
-
-
- }
测试结果:
重点:
private Customer customer;
- package com.tangyuan.model;
- /**
- * 建立实体层面的关联关系:
- * 多对一:多个订单对应一个客户(一个订单对应唯一一个客户)
- */
- public class Order {
- private Integer orderId;
-
- private String orderNo;
-
- private Integer cid;
-
- //B:订单(Order) A:客户(Customer)
- //多对一
- private Customer customer;
-
-
-
- public Order(Integer orderId, String orderNo, Integer cid) {
- this.orderId = orderId;
- this.orderNo = orderNo;
- this.cid = cid;
- }
-
- public Order() {
- super();
- }
-
- public Customer getCustomer() {
- return customer;
- }
-
- public void setCustomer(Customer customer) {
- this.customer = customer;
- }
-
- public Integer getOrderId() {
- return orderId;
- }
-
- public void setOrderId(Integer orderId) {
- this.orderId = orderId;
- }
-
- public String getOrderNo() {
- return orderNo;
- }
-
- public void setOrderNo(String orderNo) {
- this.orderNo = orderNo;
- }
-
- public Integer getCid() {
- return cid;
- }
-
- public void setCid(Integer cid) {
- this.cid = cid;
- }
-
- @Override
- public String toString() {
- return "Order{" +
- "orderId=" + orderId +
- ", orderNo='" + orderNo + '\'' +
- ", cid=" + cid +
- ", customer=" + customer +
- '}';
- }
- }
- package com.tangyuan.mapper;
-
-
- import com.tangyuan.model.Order;
- import org.springframework.stereotype.Repository;
-
- @Repository
- public interface OrderMapper {
-
- Order queryOrderById(Integer OrderId);
-
- }
- <resultMap id="many2one" type="Order">
- <id column="order_id" property="orderId"/>
- <result column="order_no" property="orderNo"/>
- <result column="cid" property="cid"/>
-
-
-
- <association property="customer" javaType="Customer">
- <id column="customer_id" property="customerId"/>
- <result column="customer_name" property="customerName"/>
- association>
- resultMap>
完整版代码如下:
- mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
- <mapper namespace="com.tangyuan.mapper.OrderMapper" >
- <resultMap id="BaseResultMap" type="com.tangyuan.model.Order" >
- <constructor >
- <idArg column="order_id" jdbcType="INTEGER" javaType="java.lang.Integer" />
- <arg column="order_no" jdbcType="VARCHAR" javaType="java.lang.String" />
- <arg column="cid" jdbcType="INTEGER" javaType="java.lang.Integer" />
- constructor>
- resultMap>
- <resultMap id="ManytoOne" type="com.tangyuan.model.Order">
- <id property="orderId" javaType="java.lang.Integer" column="order_id"/>
- <result property="orderNo" javaType="java.lang.String" column="order_no"/>
- <result property="cid" javaType="java.lang.Integer" column="cid" />
-
-
- <association property="customer" javaType="com.tangyuan.model.Customer">
- <id property="customerId" javaType="java.lang.Integer" column="customer_id" />
- <result property="customerName" javaType="java.lang.String" column="customer_name" />
- association>
- resultMap>
-
- <sql id="Base_Column_List" >
- order_id, order_no, cid
- sql>
- <select id="queryOrderById" resultMap="ManytoOne">
- SELECT * FROM t_customer c INNER join
- t_order o on c.customer_id=o.cid
- where o.order_id=#{orderId}
-
- select>
-
- mapper>
- package com.tangyuan.service;
-
-
- import com.tangyuan.model.Order;
- import org.springframework.stereotype.Repository;
-
- @Repository
- public interface IOrderService {
-
- Order queryOrderById(Integer OrderId);
-
- }
- package com.tangyuan.service.impl;
-
- import com.tangyuan.mapper.OrderMapper;
- import com.tangyuan.model.Order;
- import com.tangyuan.service.IOrderService;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
-
- /**
- * @author 唐渊
- * @create 2022-07-27 11:56
- */
- //org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'com.tangyuan.service.impl.CustomerServiceImplTest': Unsatisfied dependency expressed through field 'orderService'; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type 'com.tangyuan.service.IOrderService' available: expected at least 1 bean which qualifies as autowire candidate. Dependency annotations:
- // {@org.springframework.beans.factory.annotation.Autowired(required=true)}
- @Service
- public class OrderServiceImpl implements IOrderService {
- @Autowired
- private OrderMapper orderMapper;
-
- @Override
- public Order queryOrderById(Integer OrderId) {
- return orderMapper.queryOrderById(OrderId);
- }
- }
- package com.tangyuan.service.impl;
-
- import com.tangyuan.model.Order;
- import com.tangyuan.service.IOrderService;
-
- import org.junit.Test;
- import org.springframework.beans.factory.annotation.Autowired;
-
-
- import java.util.List;
-
- import static org.junit.Assert.*;
-
- /**
- * @author 唐渊
- * @create 2022-07-27 11:32
- */
- public class CustomerServiceImplTest extends BaseTest{
-
- @Autowired
- private IOrderService orderService;
-
-
- @Test
- public void queryOrderById(){
- Order orders = orderService.queryOrderById(1);
- System.out.println(orders);
- }
-
- }
测试结果:
1.将数据表导入数据库中
- -- 多对多
- -- 主表
- create table t_news
- (
- news_id int primary key auto_increment, -- 新闻ID:主鍵
- title varchar(50) not null -- 标题
- );
-
- -- 主表
- create table t_category
- (
- category_id int primary key auto_increment, -- 类别编号:主鍵
- category_name varchar(50) not null -- 类别名称
- );
-
- -- 桥接表,在关系型数据库中:多对多关系是无法直接映射的,
- -- 必须将一个多对多关系转换成二个一对多关系才能进行映射
- create table t_news_category
- (
- id int primary key not null auto_increment,
- nid int not null,
- cid int not null,
- foreign key(nid) references t_news(news_id),
- foreign key(cid) references t_category(category_id)
- );
-
-
- insert into t_news(news_id,title) values(1,'110');
- insert into t_news(news_id,title) values(2,'111');
- insert into t_news(news_id,title) values(3,'112');
- insert into t_news(news_id,title) values(4,'113');
- insert into t_news(news_id,title) values(5,'114');
- insert into t_news(news_id,title) values(6,'115');
- insert into t_news(news_id,title) values(7,'116');
- insert into t_news(news_id,title) values(8,'117');
- insert into t_news(news_id,title) values(9,'118');
-
-
- insert into t_category(category_id,category_name) values(1,'焦点');
- insert into t_category(category_id,category_name) values(2,'国际');
- insert into t_category(category_id,category_name) values(3,'社会');
- insert into t_category(category_id,category_name) values(4,'房产');
- insert into t_category(category_id,category_name) values(5,'财经');
- insert into t_category(category_id,category_name) values(6,'娱乐');
-
-
- insert into t_news_category(nid,cid) values(1,1);
- insert into t_news_category(nid,cid) values(1,2);
-
- insert into t_news_category(nid,cid) values(2,1);
- insert into t_news_category(nid,cid) values(2,2);
-
- insert into t_news_category(nid,cid) values(3,1);
- insert into t_news_category(nid,cid) values(3,2);
- insert into t_news_category(nid,cid) values(3,3);
-
- insert into t_news_category(nid,cid) values(4,1);
- insert into t_news_category(nid,cid) values(4,2);
- insert into t_news_category(nid,cid) values(4,3);
- insert into t_news_category(nid,cid) values(4,4);
-
-
- SELECT * FROM t_news;
- SELECT * FROM t_category;
- SELECT * FROM t_news_category;
-
-
多对多:产生中间关系表,引入两张表的主键作为外键,两个主键成为联合主键或使用新的字段作为主键。
在A类中定义B类类型的集合,在B类中定义A类类型的集合
为了简单便捷,只在中间表的Mapper类实现效果:
- package com.tangyuan.mapper;
-
- import com.tangyuan.model.NewsCategory;
- import org.springframework.stereotype.Repository;
-
- import java.util.List;
-
- @Repository
- public interface NewsCategoryMapper {
-
- //根据新闻id查询
- List
queryNewsToMany(Integer newsId); - //根据类型id查询
- List
queryCategoryToMany(Integer categoryId); -
-
-
- }
- mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd" >
- <mapper namespace="com.tangyuan.mapper.NewsCategoryMapper" >
- <resultMap id="BaseResultMap" type="com.tangyuan.model.NewsCategory" >
- <constructor >
- <idArg column="id" jdbcType="INTEGER" javaType="java.lang.Integer" />
- <arg column="nid" jdbcType="INTEGER" javaType="java.lang.Integer" />
- <arg column="cid" jdbcType="INTEGER" javaType="java.lang.Integer" />
- constructor>
- resultMap>
- <resultMap id="ManyToMany" type="com.tangyuan.model.NewsCategory">
- <id property="id" javaType="java.lang.Integer" column="id"/>
- <result property="nid" javaType="java.lang.Integer" column="nid"/>
- <result property="cid" javaType="java.lang.Integer" column="cid"/>
- <association property="news" javaType="com.tangyuan.model.News">
- <id property="newsId" javaType="java.lang.Integer" column="category_Id"/>
- <result property="title" javaType="java.lang.String" column="title"/>
- association>
-
- <association property="category" javaType="com.tangyuan.model.Category">
- <id property="categoryId" javaType="java.lang.Integer" column="category_Id"/>
- <result property="categoryName" javaType="java.lang.String" column="category_Name"/>
- association>
-
- resultMap>
-
- <sql id="Base_Column_List" >
- id, nid, cid
- sql>
- <select id="queryNewsToMany" resultMap="ManyToMany">
- select
- *
- from
- t_news_category nc left join t_news n on nc.nid=n.news_id
- left join t_category c on nc.cid =c.category_id where n.news_id=#{value}
- select>
-
- <select id="queryCategoryToMany" resultMap="ManyToMany">
- select
- *
- from
- t_news_category nc left join t_news n on nc.nid=n.news_id
- left join t_category c on nc.cid =c.category_id where c.category_id=#{value}
- select>
-
- mapper>
- package com.tangyuan.service;
-
- import com.tangyuan.model.NewsCategory;
- import org.springframework.stereotype.Repository;
-
- import java.util.List;
-
- @Repository
- public interface INewsCategoryService {
-
- //根据新闻id查询
- List
queryNewsToMany(Integer newsId); - //根据类型id查询
- List
queryCategoryToMany(Integer categoryId); -
-
-
- }
- package com.tangyuan.service.impl;
-
- import com.tangyuan.mapper.NewsCategoryMapper;
- import com.tangyuan.model.NewsCategory;
- import com.tangyuan.service.INewsCategoryService;
- import org.springframework.beans.factory.annotation.Autowired;
- import org.springframework.stereotype.Service;
-
- import java.util.List;
-
- /**
- * @author 唐渊
- * @create 2022-07-27 13:54
- */
- @Service
- public class NewsCategoryServiceImpl implements INewsCategoryService {
-
- @Autowired
- private NewsCategoryMapper newsCategoryMapper;
-
- @Override
- public List
queryNewsToMany(Integer newsId) { - return newsCategoryMapper.queryNewsToMany(newsId);
- }
-
- @Override
- public List
queryCategoryToMany(Integer categoryId) { -
- return newsCategoryMapper.queryCategoryToMany(categoryId);
- }
- }
- package com.tangyuan.service.impl;
-
- import com.tangyuan.model.NewsCategory;
- import com.tangyuan.service.INewsCategoryService;
- import com.tangyuan.service.impl.BaseTest;
- import org.junit.Test;
- import org.springframework.beans.factory.annotation.Autowired;
-
- import java.util.List;
-
- import static org.junit.Assert.*;
-
- /**
- * @author 唐渊
- * @create 2022-07-27 13:53
- */
- public class NewsCategoryMapperTest extends BaseTest {
- @Autowired
- private INewsCategoryService newsCategoryService;
-
- @Test
- public void queryNewsToMany() {
- List
news = newsCategoryService.queryNewsToMany(1); - news.forEach(System.out::println);
-
-
- }
-
- @Test
- public void queryCategoryToMany() {
- List
categorys = newsCategoryService.queryCategoryToMany(1); - categorys.forEach(System.out::println);
- }
- }
测试结果: