对与association和collection的理解：

文章目录

前言
一、多对一关系 —— association
二、一对多关系 —— collection

前言

对与association和collection的理解：
我们经常会遇到两组对象一对多或者多对一的关系。

使用mybatis时，使用association和collection可以实现字段扩展，子表相关功能。

一对一关系（多对一关系）：

例如：
多对一：一个班50名都有一个班主任老师，即多名同学关联一位老师。
一对多：一名班主任老师管理班里50个同学，即一个集合概念。

针对于上述情况，若想查询某些同学共有的老师是谁，或者一名老师带了多少位同学，则需进行老师信息表teacher与学生信息表student联表查询，就会用到下面介绍的association和collection关键词。
一、多对一关系 —— association

CREATE TABLE `teacher` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师'); 

CREATE TABLE `student` (
  `id` INT(10) NOT NULL,
  `name` VARCHAR(30) DEFAULT NULL,
  `tid` INT(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

对于接口的实现：

基于子查询的：

SELECT student.id, student.name, student.tid
FROM student
WHERE student.tid = ( SELECT teacher.id FROM teacher )
1
2
3

基于结果查询的：

SELECT s.id,s.name,s.tid,t.name FROM student AS s,teacher AS t
WHERE t.id = s.tid

<?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接口 -->
<mapper namespace="com.ali.mapper.StudentMapper">

<!--    1.按照查询嵌套处理->子查询 -->
    <select id="getStudents" resultMap="studentMap" resultType="com.ali.pojo.Student">
        select * from mybatis.student
    </select>
    <resultMap id="studentMap" type="com.ali.pojo.Student">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <association property="teacher" column="tid" javaType="com.ali.pojo.Teacher" select="getTeachers"></association>
    </resultMap>
    <select id="getTeachers" resultType="com.ali.pojo.Teacher">
        select * from mybatis.teacher where id = #{id}
    </select>

<!--    2.按照结果查询->嵌套查询-->
    <select id="getStudents" resultMap="studentMapper">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from mybatis.student s,mybatis.teacher t
        where s.tid = t.id;
    </select>
    <resultMap id="studentMapper" type="com.ali.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <result property="tid" column="stid"></result>
        <association property="teacher" javaType="com.ali.pojo.Teacher">
            <result property="id" column="tid"></result>
            <result property="name" column="tname"></result>
        </association>
    </resultMap>
</mapper>

一对多关系：

<?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接口 -->
<mapper namespace="com.ali.mapper.TeacherMapper">

<!--    1.基于子查询实现-->
    <select id="getTeacher1" resultMap="teacherMap">
        select * from mybatis.teacher
        where mybatis.teacher.id = #{id}
    </select>
    <resultMap id="teacherMap" type="com.ali.pojo.Teacher">
        <result property="name" column="name"></result>
        <collection property="students" column="id" javaType="ArrayList" ofType="com.ali.pojo.Student" select="studentMap">
        </collection>
    </resultMap>
    <select id="studentMap" resultType="com.ali.pojo.Student">
        select * from mybatis.student where tid = #{id};
    </select>
    
<!--    2. 基于结果查询实现-->
    <select id="getTeacher" resultMap="teacherInfo">
        select s.id,s.name,t.id tid,t.name
        from mybatis.student s, mybatis.teacher t
        where t.id = s.tid and t.id = #{id}
    </select>
    <resultMap id="teacherInfo" type="com.ali.pojo.Teacher">
        <result property="id" column="id"></result>
        <result property="name" column="name"></result>
        <collection property="students" ofType="com.ali.pojo.Student">
            <result property="id" column="id"></result>
            <result property="name" column="name"></result>
            <result property="tid" column="tid"></result>
        </collection>
    </resultMap>
</mapper>