文章目录
一、表之间的关联关系
关联关系是有方向的。
- 一对多关联关系
- 多对一关联关系
- 一对一关联关系
- 多对多关联关系
二、一对多关联关系
客户和订单就是典型的一对多关系。(一个客户可以对应多个订单)
客户表(customer 主键表):
订单表(orders 外键表):
使用一对多的关联关系,可以满足查询客户的同时查询该客户名下的所有订单。
SQL语句:
2.1 方法一 collection标签
collection标签
编写实体类(customer):
package com.bjpowernode.pojo;
import java.util.List;
public class Customer {
//customer表中的三个列
private int id;
private String name;
private int age;
//该客户名下的所有订单的集合
private List<Orders> ordersList;
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
", ordersList=" + ordersList +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public List<Orders> getOrdersList() {
return ordersList;
}
public void setOrdersList(List<Orders> ordersList) {
this.ordersList = ordersList;
}
public Customer(int id, String name, int age, List<Orders> ordersList) {
this.id = id;
this.name = name;
this.age = age;
this.ordersList = ordersList;
}
public Customer() {
}
}
编写实体类(orders):
package com.bjpowernode.pojo;
public class Orders {
private int id;
private String orderNumber;
private Double orderPrice;
public Orders(int id, String orderNumber, Double orderPrice) {
this.id = id;
this.orderNumber = orderNumber;
this.orderPrice = orderPrice;
}
public Orders() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
public Double getOrderPrice() {
return orderPrice;
}
public void setOrderPrice(Double orderPrice) {
this.orderPrice = orderPrice;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
", orderPrice=" + orderPrice +
'}';
}
}
编写接口 CustomerMapper
public interface CustomerMapper {
//根据客户的id查询客户信息,并同时查询该客户名下的所有订单
List<Customer> getById(int id);
}
编写xml文件,CustomerMapper.xml:
注意 collection标签中是oftype
注意 起别名问题
<resultMap id="customermap" type="customer">
<id property="id" column="cid"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<collection property="ordersList" ofType="orders">
<id property="id" column="oid"></id>
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
</collection>
</resultMap>
<select id="getById" parameterType="int" resultMap="customermap">
select c.id cid,name,age,o.id oid,orderNumber,orderPrice,customer_id
from customer c inner join orders o on c.id = o.customer_id
where c.id = #{cid}
</select>
测试:
@Test
public void test1() throws IOException {
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = factory.openSession();
CustomerMapper cMapper = session.getMapper(CustomerMapper.class);
List<Customer> list = cMapper.getById(1);
list.forEach(customer-> System.out.println(customer));
}
运行结果:
存在bug:
当测试去查找id为3的客户信息以及其名下的订单,由于没有订单,所以导致id为3的客户信息也查不出来:
改进:
修改xml文件中的sql语句,等值匹配改为左连接:
<select id="getById" parameterType="int" resultMap="customermap">
select c.id cid,name,age,o.id oid,orderNumber,orderPrice,customer_id
from customer c left join orders o on c.id = o.customer_id
where c.id = #{cid}
</select>
2.2 方法二 分步查询
分步查询
step1:先根据customer的id属性找到对应的客户信息
step2:在对应的客户信息中根据客户的id去订单中寻找customer_id = id 的记录
接口:
编写接口 CustomerMapper
//根据客户的id查询客户信息,现根据客户编号获取客户信息
Customer getByStep(int cid);
编写接口 OrdersrMapper
//根据客户的id查询客户信息,现根据客户编号获取客户信息
List<Orders> getOrdersByStep(int cid);
CustomerMapper.xml:
编写xml文件,CustomerMapper.xml:
<resultMap id="customerByStep" type="customer">
<id property="id" column="cid"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
<collection property="ordersList"
select="com.bjpowernode.mapper.OrdersMapper.getOrdersByStep"
column="cid"/>
</resultMap>
<select id="getByStep" resultMap="customerByStep">
select c.id cid,name,age
from customer c
where cid = #{cid}
</select>
OrdersMapper.xml:
编写xml文件,OrdersMapper.xml:
<select id="getOrdersByStep" resultType="orders">
select o.id oid,orderNumber,orderPrice,customer_id
from orders o
where customer_id= #{cid}
</select>
三、多对一关联关系
订单和客户就是多对一关联:
站在订单的方向查询订单的同时将客户信息查出。
3.1 方法一 association标签
一条SQL语句
使用高级映射resultMap中的association标签
代码实现
编写实体类(customer):
package com.bjpowernode.pojo;
import java.util.List;
public class Customer {
//customer表中的三个列
private int id;
private String name;
private int age;
@Override
public String toString() {
return "Customer{" +
"id=" + id +
", name='" + name + '\'' +
", age=" + age +
'}';
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getAge() {
return age;
}
public void setAge(int age) {
this.age = age;
}
public Customer(int id, String name, int age) {
this.id = id;
this.name = name;
this.age = age;
}
public Customer() {
}
}
编写实体类(orders):
public class Orders {
private int id;
private String orderNumber;
private Double orderPrice;
private int customer_id;
//订单对应的客户信息
private Customer customer;
public Orders() {
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public int getCustomer_id() {
return customer_id;
}
public void setCustomer_id(int customer_id) {
this.customer_id = customer_id;
}
public String getOrderNumber() {
return orderNumber;
}
public void setOrderNumber(String orderNumber) {
this.orderNumber = orderNumber;
}
public Double getOrderPrice() {
return orderPrice;
}
public void setOrderPrice(Double orderPrice) {
this.orderPrice = orderPrice;
}
@Override
public String toString() {
return "Orders{" +
"id=" + id +
", orderNumber='" + orderNumber + '\'' +
", orderPrice=" + orderPrice +
", customer=" + customer +
'}';
}
public Orders(int id, String orderNumber, Double orderPrice, Customer customer) {
this.id = id;
this.orderNumber = orderNumber;
this.orderPrice = orderPrice;
this.customer = customer;
}
}
编写接口 OrdersMapper
public interface OrdersMapper {
Orders getByOrder(int id);
}
编写xml文件,OrdersMapperr.xml:
注意 association标签中是javatype
注意 起别名问题
<resultMap id="ordersmap" type="orders">
<id property="id" column="oid"></id>
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
<association property="customer" javaType="customer">
<id property="id" column="cid"></id>
<result property="name" column="name"></result>
<result property="age" column="age"></result>
</association>
</resultMap>
<select id="getByOrder" parameterType="int" resultMap="ordersmap">
select o.id oid,orderNumber,orderPrice,c.id cid,name,age
from orders o inner join customer c on o.customer_id = c.id
where o.id = #{oid}
</select>
测试:
@Test
public void test2() throws IOException {
InputStream in = Resources.getResourceAsStream("SqlMapConfig.xml");
SqlSessionFactory factory = new SqlSessionFactoryBuilder().build(in);
SqlSession session = factory.openSession();
OrdersMapper ordersMapper = session.getMapper(OrdersMapper.class);
Orders orders = ordersMapper.getByOrder(11);
System.out.println(orders);
}
运行结果:
3.2 方法二 级联属性映射
一条SQL语句,级联属性映射
与方法一区别在于mapper.xml文件,不使用association
<resultMap id="ordersmap" type="orders">
<id property="id" column="oid"></id>
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
<result property="customer.id" column="cid"></id>
<result property="customer.name" column="name"></result>
<result property="customer.age" column="age"></result>
</resultMap>
<select id="getByOrder" parameterType="int" resultMap="ordersmap">
select o.id oid,orderNumber,orderPrice,c.id cid,name,age
from orders o inner join customer c on o.customer_id = c.id
where o.id = #{oid}
</select>
3.3 方法三(分步查询)
两条SQL语句
step1:先根据oid去查对应的订单信息,在订单信息中找到对应的客户cid
step2:根据cid再进行sql查询,找到对应的客户信息
实体类不需要改变
接口:两个接口文件都需要修改:
1、编写接口 OrdersMapper
public interface OrdersMapper {
Orders getByOid(int oid);
}
2、编写接口 CustomerMapper
/**
*分布查询第二步
*/
public interface CustomerMapper{
Customer getByCid(int cid);
}
mapper.xml文件
Ordersmapper.xml文件
根据订单id拿到订单信息包括订单信息中的customer_id
association标签中的select属性需要指定另外第二步的Sqlid
SqlId=“接口的全限定名称(namespace).sql语句的id”
association标签中的column属性需要指定第二张表需要的参数
column属性的值一定是第一张表查出来的customer_id 如果进行了起别名 column也要修改。
select=“com.bjpowernode.mapper.CustomerMapper.getByCid”
<!--两条SQL语句完成多对一的分布查询-->
<!--这里是第一步,根据订单的id查询订单的所有信息,这些信息中包含customer_id-->
<resultMap id="ordersmapByStep" type="orders">
<id property="id" column="oid"></id>
<result property="orderNumber" column="orderNumber"></result>
<result property="orderPrice" column="orderPrice"></result>
<association property="customer"
select="com.bjpowernode.mapper.CustomerMapper.getByCid"
column="cid"/>
</resultMap>
<select id="getByOid" resultMap="ordersmapByStep">
select id,orderNumber,orderPrice,customer_id as cid
from orders
where id = #{oid}
</select>
Customermapper.xml文件
<!--这里是第二步,根据id获取客户所有信息-->
<select id="getByCid" resultType="customer">
select id,name,age
from customer
where id = #{cid}
</select>
四、延迟加载
分步查询的优点:
1、复用性强,可以重复利用。(大步拆分成N多个小步,每一个小碎步可以重复利用)
2、采用分布查询,可以充分利用他们的延迟加载/懒加载机制
2.1、什么是延迟加载?什么用?
延迟加载的核心原理是:用的时候再执行查询语句,不用的时候不查询
作用:提高性能。尽可能的不查或尽可能的少查提高效率
2.2、Mybatis当中怎么开启延迟加载?
association标签中添加fetchType=“lazy”
默认情况下是没有开启的
这种在association标签中配置fetchType=“lazy”,是局部的配置,只对当前的association关联的sql语句起作用
2.3、启用全局的延迟加载,所有只要带有分布的,都采用延迟加载。
在实际的开发中,大部分都是需要使用延迟加载的,所以建议开启全局的延迟加载机制
在mybatis核心配置文件当中添加全局配置,lazyLoadingEnabled=true。
如果有特殊需要,某一些sql语句不想让它延迟加载,在association标签中添加fetchType=“eager”
实际开发中的模式:把全局的延迟加载打开,如果某一步不需要使用延迟加载,设置fetchType=“eager”
总结
一对多:one2many (one中间有o—>collection—>oftype)
多对一:many2one(many中间有a—>assocation—>javatype)
谁在前谁是主表