Mybatis之关联

一、一对多关联

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_idwhere t_customer.customer_id=#{customerId}</select>

测试：

   @Testpublic 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_idwhere order_id=#{orderId};</select>

测试:

   @Testpublic  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_idwhere 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,namefrom books,category,category_bookWHERE books.id=category_book.book_idand category_book.category_id=category.category_idand 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_idfrom  books,category,category_bookWHERE books.id=category_book.book_idand category_book.category_id=category.category_idand 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'}