一、一对多关联
eg：一个用户对应多个订单
建表语句
CREATE TABLE `t_customer` (
`customer_id` INT NOT NULL AUTO_INCREMENT,
`customer_name` CHAR(100),
PRIMARY KEY (`customer_id`)
);
CREATE TABLE `t_order` (
`order_id` INT NOT NULL AUTO_INCREMENT,
`order_name` CHAR(100),
`customer_id` INT,
PRIMARY KEY (`order_id`)
);
INSERT INTO `t_customer` (`customer_name`) VALUES ('张三');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o1', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o2', '1');
INSERT INTO `t_order` (`order_name`, `customer_id`) VALUES ('o3', '1');
关联查询：查询custmoer_id=1的用户的所有订单信息和用户信息
select t_customer.customer_id,customer_name,order_id,order_name from t_customer left JOIN t_order on t_customer.customer_id=t_order.customer_id
where t_customer.customer_id=1
Customer实体类：
@Data
public class Customer {
private Integer customerId;
private String customerName;
//一个顾客的所有订单
private List<Order> orderList;
}
Order实体类：
@Data
public class Order {
private Integer orderId;
private String orderName;
private Integer customerId;
}
mapper接口：
Customer getCustomerWithOrders(Integer customerId);
xml配置文件：
<resultMap id="CustomerMap" type="Customer">
<id property="customerId" column="customer_id"></id>
<result property="customerName" column="customer_name"></result>
<collection property="orderList" ofType="Order">
<id property="orderId" column="order_id"></id>
<result property="orderName" column="order_name"></result>
</collection>
</resultMap>
<select id="getCustomerWithOrders" resultMap="CustomerMap">
select t_customer.customer_id,customer_name,order_id,order_name from t_customer left JOIN t_order on t_customer.customer_id=t_order.customer_id
where t_customer.customer_id=#{customerId}
</select>
测试：
@Test
public void test01(){
Customer customerWithOrders = orderMapper.getCustomerWithOrders(1);
System.out.println(customerWithOrders);
}
在“对多”关联关系中，同样有很多配置，但是提炼出来最关键的就是：“collection”和“ofType”
二、对一关联
eg：一个订单对应一个用户
sql语句：查询订单号为1的订单和用户信息
select t_order.* ,t_customer.customer_name from t_order LEFT JOIN t_customer on t_order.customer_id=t_customer.customer_id
where order_id=1;
查询结果:
order实体类新增Customer属性
@Data
public class Order {
private Integer orderId;
private String orderName;
private Integer customerId;
//对一关系，用户信息
private Customer customer;
}
mapper接口：
Order getOrderWithCustomer(Integer orderId);
xml配置文件：
<resultMap id="OrderMap" type="Order">
<id property="orderId" column="order_id"></id>
<result property="orderName" column="order_name"></result>
<association property="customer" javaType="Customer">
<id property="customerId" column="customer_id"></id>
<result property="customerName" column="customer_name"></result>
</association>
</resultMap>
<select id="getOrderWithCustomer" resultMap="OrderMap">
select t_order.* ,t_customer.customer_name from t_order LEFT JOIN t_customer on t_order.customer_id=t_customer.customer_id
where order_id=#{orderId};
</select>
测试:
@Test
public void test02(){
Order orderWithCustomer = orderMapper.getOrderWithCustomer(1);
System.out.println(orderWithCustomer);
}
三、OGNL风格的对一关联
<!-- OGNL风格的对一关联-->
<select id="getOrderWithCustomer2" resultType="Order">
select t_order.order_id,t_order.order_name,t_order.customer_id as 'customer.customerId',t_customer.customer_name as 'customer.customerName' from t_order LEFT JOIN t_customer on t_order.customer_id=t_customer.customer_id
where order_id=#{orderId};
</select>
注意起别名时，对象属性costomer.customerId要加上引号
四、多对多关联
eg：一本书对应多个种类，一个种类对应多本书
建立中间表将书和种类对应起来
①根据书的id，查询对应的具体信息和所属种类的信息：
mapper接口：
/**
* 根据书的id，查询对应的具体信息和所属种类的信息
* @param bookId
* @return
*/
BookEntity selectBookOfCategories(Integer bookId);
xml配置文件：
<resultMap id="BookMap" type="BookEntity">
<id property="id" column="book_id"></id>
<result property="name" column="name"></result>
<collection property="categoryEntityList" ofType="CategoryEntity">
<id property="categoryId" column="category_id"></id>
<result property="categoryName" column="category_name"></result>
</collection>
</resultMap>
<select id="selectBookOfCategories" resultMap="BookMap">
select book_id,category.category_id,category_name,name
from books,category,category_book
WHERE books.id=category_book.book_id
and category_book.category_id=category.category_id
and books.id=#{value}
</select>
②根据种类的id，查询所有对应书的信息
mapper接口：
CategoryEntity getCategory(Integer id);
xml配置文件
<resultMap id="CategoryMap" type="CategoryEntity">
<id property="categoryId" column="category_id"></id>
<result property="categoryName" column="category_name"></result>
<collection property="bookEntityList" ofType="BookEntity">
<id property="id" column="book_id"></id>
<result property="name" column="name"></result>
</collection>
</resultMap>
<select id="getCategory" resultMap="CategoryMap">
SELECT name,category.category_id ,category_name,book_id
from books,category,category_book
WHERE books.id=category_book.book_id
and category_book.category_id=category.category_id
and category.category_id=#{value};
</select>
五、分步查询
①分步查询对多关联
根据id查询顾客=>设置resultMap=>collection中的select指定OrderMapper.xml中的根据顾客id查询所有订单的select语句=>column指定传参（顾客id）
CustomerMapper.xml
<resultMap id="CustomerMap" type="Customer">
<id property="customerId" column="customer_id"></id>
<result property="customerName" column="customer_name"></result>
<collection property="orderList" select="com.iflytek.mapper.OrderMapper.selectOrderListById" column="customer_id"></collection>
</resultMap>
<select id="getOrderListByCustomerId" resultMap="CustomerMap">
select * from t_customer where customer_id=#{id}
</select>
OrderMapper.xml
<select id="selectOrderListById" resultMap="OrderMap0">
select * from t_order where customer_id=#{value}
</select>
<resultMap id="OrderMap0" type="Order">
<id property="orderId" column="order_id"></id>
<result property="orderName" column="order_name"></result>
</resultMap>
关系总览：
②分步查询对一关联
OrderMapper.xml
根据订单id查询订单具体信息=>association标签中的select指定CustomerMapper.xml中的根据customer_id查询顾客信息的slecect语句=>column指定传参customer_id
<resultMap id="OrderMap2" type="Order">
<id property="orderId" column="order_id"></id>
<result property="orderName" column="order_name"></result>
<association property="customer" select="com.iflytek.mapper.CustomerMapper.getCustomerById" column="customer_id">
</association>
</resultMap>
<select id="getOrderAndCustomer" resultMap="OrderMap2">
select * from t_order where order_id=#{orderId}
</select>
CustomerMapper.xml
根据用户id查询用户具体信息
<resultMap id="CustomerMap0" type="Customer">
<id property="customerId" column="customer_id"></id>
<result property="customerName" column="customer_name"></result>
</resultMap>
<select id="getCustomerById" resultMap="CustomerMap0">
select * from t_customer where customer_id=#{value}
</select>
关系总览：
六、延迟加载
查询到Customer的时候，不一定会使用Order的List集合数据。如果Order的集合数据始终没有使用，那么这部分数据占用的内存就浪费了。对此，我们希望不一定会被用到的数据，能够在需要使用的时候再去查询。
延迟加载的概念：对于实体类关联的属性到需要使用时才查询。也叫懒加载。
yml配置文件中开启懒加载
mybatis:
configuration:
#开启懒加载
lazy-loading-enabled: true
测试：
@Test
public void testSelectCustomerWithOrderList() throws InterruptedException {
//对多关联
Customer customer = mapper.selectCustomerWithOrderList(1);
// 这里必须只打印“customerId或customerName”这样已经加载的属性才能看到延迟加载的效果
// 这里如果打印Customer对象整体则看不到效果
System.out.println("customer = " + customer.getCustomerName());
// 先指定具体的时间单位，然后再让线程睡一会儿
TimeUnit.SECONDS.sleep(5);
List<Order> orderList = customer.getOrderList();
for (Order order : orderList) {
System.out.println("order = " + order);
}
}
效果：刚开始先查询Customer本身，需要用到OrderList的时候才发送SQL语句去查询
DEBUG 11-30 11:25:31,127 ==> Preparing: select customer_id,customer_name from t_customer where customer_id=? (BaseJdbcLogger.java:145)
DEBUG 11-30 11:25:31,193 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145)
DEBUG 11-30 11:25:31,314 <== Total: 1 (BaseJdbcLogger.java:145)
customer = c01
DEBUG 11-30 11:25:36,316 ==> Preparing: select order_id,order_name from t_order where customer_id=? (BaseJdbcLogger.java:145)
DEBUG 11-30 11:25:36,316 ==> Parameters: 1(Integer) (BaseJdbcLogger.java:145)
DEBUG 11-30 11:25:36,321 <== Total: 3 (BaseJdbcLogger.java:145)
order = Order{orderId=1, orderName='o1'}
order = Order{orderId=2, orderName='o2'}
order = Order{orderId=3, orderName='o3'}
