12 关联查询

数据准备：

sql

/*
SQLyog Ultimate v12.08 (64 bit)
MySQL - 5.7.18 : Database - mybatis_java1
*********************************************************************
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/`mybatis_java1` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin */;

USE `mybatis_java1`;

/*Table structure for table `account` */

DROP TABLE IF EXISTS `account`;

CREATE TABLE `account` (
  `acc_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_id` int(11) DEFAULT NULL,
  `acc_amout` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`acc_id`),
  KEY `AK_Key_1` (`acc_id`),
  KEY `FK_Reference_1` (`stu_id`),
  CONSTRAINT `FK_Reference_1` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*Data for the table `account` */

insert  into `account`(`acc_id`,`stu_id`,`acc_amout`) values (1,1,'50'),(2,2,'60'),(3,3,'80'),(4,4,'90'),(5,5,'500');

/*Table structure for table `order_detail` */

DROP TABLE IF EXISTS `order_detail`;

CREATE TABLE `order_detail` (
  `detail_id` int(11) NOT NULL,
  `order_id` int(11) DEFAULT NULL,
  `product_name` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  `price` decimal(10,2) DEFAULT NULL,
  PRIMARY KEY (`detail_id`),
  KEY `FK_Reference_2` (`order_id`),
  CONSTRAINT `FK_Reference_2` FOREIGN KEY (`order_id`) REFERENCES `product_order` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*Data for the table `order_detail` */

insert  into `order_detail`(`detail_id`,`order_id`,`product_name`,`price`) values (1,1,'保温壶','20.00'),(2,1,'报纸','30.00'),(3,2,'唐诗三百首','100.00'),(4,2,'儿童玩具车','400.00'),(5,2,'婴儿车','100.00');

/*Table structure for table `product_order` */

DROP TABLE IF EXISTS `product_order`;

CREATE TABLE `product_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `createtime` datetime DEFAULT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `addressee` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `address` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  `status` varchar(100) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*Data for the table `product_order` */

insert  into `product_order`(`order_id`,`createtime`,`amount`,`addressee`,`address`,`status`) values (1,'2021-03-24 16:22:27','50.00','张三','黑龙江','代发货'),(2,'2021-03-10 16:22:49','600.00','王五','辽宁','已签收');

/*Table structure for table `stu_teacher_rela` */

DROP TABLE IF EXISTS `stu_teacher_rela`;

CREATE TABLE `stu_teacher_rela` (
  `teacher_id` int(11) DEFAULT NULL,
  `stu_id` int(11) DEFAULT NULL,
  KEY `FK_Reference_3` (`teacher_id`),
  KEY `FK_Reference_4` (`stu_id`),
  CONSTRAINT `FK_Reference_3` FOREIGN KEY (`teacher_id`) REFERENCES `teacher` (`teacher_id`),
  CONSTRAINT `FK_Reference_4` FOREIGN KEY (`stu_id`) REFERENCES `student` (`stu_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*Data for the table `stu_teacher_rela` */

insert  into `stu_teacher_rela`(`teacher_id`,`stu_id`) values (1,1),(1,2),(2,2),(2,3),(3,3),(3,4),(3,5);

/*Table structure for table `student` */

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (
  `stu_id` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  `stu_no` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  `birthday` datetime DEFAULT NULL,
  `address` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  `face` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`stu_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*Data for the table `student` */

insert  into `student`(`stu_id`,`stu_name`,`stu_no`,`birthday`,`address`,`face`) values (1,'哈利波特','17040001','2021-03-24 14:06:40','伦敦','1'),(2,'郭靖','17040002','2021-03-24 14:06:43','大宋','2'),(3,'奥特曼','17040003','2021-03-16 14:06:44','日本','3'),(4,'成吉思汗','17040004','2021-03-10 14:06:46','蒙古','4'),(5,'特朗普','17040005','2021-03-24 14:06:48','美国','5');

/*Table structure for table `teacher` */

DROP TABLE IF EXISTS `teacher`;

CREATE TABLE `teacher` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(500) COLLATE utf8mb4_bin DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `salary` decimal(6,2) DEFAULT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

/*Data for the table `teacher` */

insert  into `teacher`(`teacher_id`,`name`,`age`,`salary`) values (1,'张飞',50,'5555.01'),(2,'赵云',55,'8873.00'),(3,'刘备',66,'88.00');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

12.1 一对一关联查询

通过全量sql，join on两张表关联查询。
- 实体中需要枚举出所有的属性
- resultMap将所有字段映射成实体中的属性

association 需要执行两种sql，主表一种sql，从表一种sql。

实体中（主表的实体）:添加从表的属性。
resultMap：主表信息映射跟单表查询保持不动，关联从表的属性需要用association 关联需要给association 指定几个属性
- property: 从表在主表实体中的属性名字
- select : 查询从表的Statement ID
- column：从表查询是关联条件字段

具体Mapper.xml语法如下：

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.neuedu.dao.One2OneMapper">

    <resultMap id="resultMap1" type="com.neuedu.entity.StudentAndAccount">

        <!--学生的 -->
        <id column="stu_id" jdbcType="INTEGER" property="stuId"/>
        <result column="stu_name" jdbcType="VARCHAR" property="stuName"/>
        <result column="stu_no" jdbcType="VARCHAR" property="stuNo"/>
        <result column="birthday" jdbcType="TIMESTAMP" property="birthday"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="face" jdbcType="VARCHAR" property="face"/>

        <!--账户的信息 -->

        <result column="acc_id" jdbcType="INTEGER" property="accId"/>
        <result column="stu_id" jdbcType="INTEGER" property="stuId"/>
        <result column="acc_amout" jdbcType="VARCHAR" property="accAmout"/>

    </resultMap>

    <resultMap id="resultMap2" type="com.neuedu.entity.Student">

        <!--学生的 -->
        <id column="stu_id" jdbcType="INTEGER" property="stuId"/>
        <result column="stu_name" jdbcType="VARCHAR" property="stuName"/>
        <result column="stu_no" jdbcType="VARCHAR" property="stuNo"/>
        <result column="birthday" jdbcType="TIMESTAMP" property="birthday"/>
        <result column="address" jdbcType="VARCHAR" property="address"/>
        <result column="face" jdbcType="VARCHAR" property="face"/>

        <!--账户的信息 -->
        <association property="account" column="stu_id" select="selectAccount">
        </association>


    </resultMap>


    <select id="selectStudentAndAccount" resultMap="resultMap1">
    SELECT
      student.stu_id,
      stu_name,
      stu_no,
      birthday,
      address,
      face,

      acc_id,
      acc_amout
    FROM
      student
      INNER JOIN account
        ON student.stu_id = account.stu_id
  </select>

    <select id="selectStudentAndAccount2" resultMap="resultMap2">
    SELECT
      student.stu_id,
      stu_name,
      stu_no,
      birthday,
      address,
      face
    FROM student
  </select>


    <select id="selectAccount" resultMap="com.neuedu.dao.AccountMapper.BaseResultMap" >
    select   acc_id, stu_id, acc_amout from account where stu_id = #{stu_id}
  </select>

</mapper>

12.2 延迟加载

在主表的实体中声明从表的属性对象 Student
两个SQL
- 查询主表的 student select * from student
- 单独查account 从表关联都是主表的外检 select * from account where stu_id = ?
- 通过主表的resultMap关联从表的查询sql （关联关系 sql_id）

在关联查询是使用 association 或者是collection 可以设置按需加载(获取从表对象的属性)，需要配置：

xml

<setting name="lazyLoadingEnabled" value="true"/>
<setting name="aggressiveLazyLoading" value="false"/>

在数据与对象进行 mapping 操作时，只有在真正使用到该对象时，才进行 mapping 操作，以减少数据库查询开销，从而提升系统性能。

但是Lazy Load也有缺点，在按需加载时会多次连接数据库，同时会增加数据库的压力。所以在实际使用时，会衡量是否使用延迟加载。resultMap可以实现高级映射（使用association、collection实现一对一及一对多映射），association、collection具备延迟加载功能。

12.3. 一对多

查询时需要通过collection标签识别从表的集合的信息:

如果是全量查询，需要指定：
- property：从表在主表中的实体属性名
- ofType：从表实体全限定名
- 并在collection子标签中添加id、result标签映射从表中的字段

编写思路：

-- 1 . ProductOrder  List<OrderDetail> orderDetailList 持有一个从表的集合对象  setter getter
-- 2 . Mapper.xml      
	-- 编写sql  SELECT * FROM product_order  JOIN order_detail  ON product_order.`order_id` = order_detail.`order_id`
        -- 映射结果 
        
	-- 1 主表数据 product——order字段映射到	ProductOrder属性中
	-- 2 从表数据 order_detail  使用collection标签映射  告诉collection往
		-- 哪个属性中映射orderDetailList
		-- 映射的类型  ofType ="com.neuedu.mybatis.entity.OrderDetail"
		-- column : detail_id ,order_id product_name price--- property:detailId

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.neuedu.mybatis.mapper.One2ManyMapper">

  <resultMap id="one2manyResultMap" type="com.neuedu.mybatis.entity.ProductOrder" >
    <!--    主表信息 product_order.*-->
    <id column="order_id" jdbcType="INTEGER" property="orderId" />
    <result column="createtime" jdbcType="TIMESTAMP" property="createtime" />
    <result column="amount" jdbcType="DECIMAL" property="amount" />
    <result column="addressee" jdbcType="VARCHAR" property="addressee" />
    <result column="address" jdbcType="VARCHAR" property="address" />
    <result column="status" jdbcType="VARCHAR" property="status" />

    <!--   order_detail.* - >  List orderDetailList-->
    <collection property="orderDetailList" ofType="com.neuedu.mybatis.entity.OrderDetail">
      <id column="detail_id" jdbcType="INTEGER" property="detailId" />
      <result column="order_id" jdbcType="INTEGER" property="orderId" />
      <result column="product_name" jdbcType="VARCHAR" property="productName" />
      <result column="price" jdbcType="DECIMAL" property="price" />
    </collection>


  </resultMap>


  <select id="selectOne2Many"  resultMap="one2manyResultMap">
         SELECT *   FROM product_order  JOIN order_detail  ON product_order.`order_id` = order_detail.`order_id`
  </select>

</mapper>

如果是延迟同样需要collection标签映射从表信息，额外需要指定如下属性
- select: 从表查询sql的Statement ID
- column：从表查询语句中的关联条件

xml

<resultMap id="resultMap2" type="com.neuedu.entity.MyProductOrder" extends="com.neuedu.dao.ProductOrderMapper.BaseResultMap">
        <collection property="orderDetails" ofType="com.neuedu.entity.OrderDetail"
                    select="selectOrderDetailByOrderId" column="order_id">
        </collection>

    </resultMap>

    <select id="selectOrder2" resultMap="resultMap2">
    SELECT
      product_order.order_id,
      createtime,
      amount,
      addressee,
      address,
      STATUS

        FROM product_order

    </select>


    <select id="selectOrderDetailByOrderId" resultMap="com.neuedu.dao.OrderDetailMapper.BaseResultMap">
        select  detail_id, order_id, product_name, price from order_detail where  order_id = #{orderId}
    </select>

12.4 多对多的关系

数据库中的多对多关系往往可以看做两个一对多的关系，在mybatis中可以通过嵌套实现多对多的关系，collection嵌套association或者 association嵌套collection 使用

在实体中维护关系以老师为主表查询从Teacher --> List<StudentTeacher> StudentTeacher--> Student

xml

 <!--******************************************************************************************** -->
    <resultMap id="teahcerMap2" type="com.neuedu.entity.MyTeacher">
        <!--1 教师主表 -->
        <id column="teacher_id" jdbcType="INTEGER" property="teacherId" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
        <result column="salary" jdbcType="DECIMAL" property="salary" />

        <!--从表 student_teacher -->
        <collection   property="studentTeachers"   ofType="com.neuedu.entity.StudentTeacher"  >
            <result column="teacher_id" jdbcType="INTEGER" property="teacherId" />
            <result column="stu_id" jdbcType="INTEGER" property="studentId" />

            <!--关联学生信息 -->
            <association property="student" javaType="com.neuedu.entity.Student">
                <id column="stu_id" jdbcType="INTEGER" property="stuId" />
                <result column="stu_name" jdbcType="VARCHAR" property="stuName" />
                <result column="stu_no" jdbcType="VARCHAR" property="stuNo" />
                <result column="birthday" jdbcType="TIMESTAMP" property="birthday" />
                <result column="address" jdbcType="VARCHAR" property="address" />
                <result column="face" jdbcType="VARCHAR" property="face" />
            </association>
        </collection>


    </resultMap>
   
    
    <!-- 非延迟加载 -->
    <select id="selectTeachers2" resultMap="teahcerMap2">
        SELECT
          teacher.teacher_id, NAME, age, salary,
          student.stu_id, stu_name, stu_no, birthday, address, face
        FROM
          teacher
          LEFT JOIN stu_teacher_rela ON teacher.`teacher_id` = stu_teacher_rela.`teacher_id`
          LEFT JOIN student  ON stu_teacher_rela.`stu_id` = student.`stu_id`
    </select>

延迟加载的情况

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.neuedu.dao.Many2ManyMapper">

    <resultMap id="teahcerMap" type="com.neuedu.entity.MyTeacher">
        <!-- 主表 Teacher -->
        <id column="teacher_id" jdbcType="INTEGER" property="teacherId" />
        <result column="name" jdbcType="VARCHAR" property="name" />
        <result column="age" jdbcType="INTEGER" property="age" />
        <result column="salary" jdbcType="DECIMAL" property="salary" />
        
        
        <!-- 从表 StudentTeacher
                property : 从表再主表实体中的属性 student_teahcer
                ofType : 从表单行记录映射的实体
                select: 查询从表的 sql（Mapperd Statement Id）
        -->
        <collection
                property="studentTeachers"
                ofType="com.neuedu.entity.StudentTeacher"
                select="selectStudentTeacher"
                column="teacher_id"
            >

        </collection>
        
    </resultMap>
    
    
    <resultMap id="studentTeacherMap" type="com.neuedu.entity.StudentTeacher">
        <result column="teacher_id" jdbcType="INTEGER" property="teacherId" />
        <result column="stu_id" jdbcType="INTEGER" property="studentId" />

        <association property="student"
                     javaType="com.neuedu.entity.Student"
                     select="selectStudent"
                     column="stu_id">
        </association>
    </resultMap>
    


    <resultMap id="studentMap" type="com.neuedu.entity.Student" extends="com.neuedu.dao.StudentMapper.BaseResultMap" >

    </resultMap>
    
    

    <select id="selectTeachers" resultMap="teahcerMap">

        select  teacher_id, name, age, salary from teacher

    </select>

    <select id="selectStudentTeacher" resultMap="studentTeacherMap">
        SELECT teacher_id ,stu_id FROM stu_teacher_rela WHERE teacher_id = #{teacher_id}
    </select>


    <select id="selectStudent" resultMap="studentMap">
        SELECT stu_id, stu_name, stu_no, birthday, address, face FROM student WHERE stu_id = #{stu_id}
    </select>

</mapper>

12 关联查询 ​

12.1 一对一关联查询 ​

12.2 延迟加载 ​

12.3. 一对多 ​

12.4 多对多的关系 ​

12 关联查询

12.1 一对一关联查询

12.2 延迟加载

12.3. 一对多

12.4 多对多的关系