[Mybatis]10.多对一和一对多

• 多个学生对应一个老师
• 对于学生而言，（关联）多个学生关联一个老师
• 对于老师而言，（集合）一个老师有很多学生

SQL

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);

1.多对一处理

按照查询嵌套处理

    <select id="getStudentByTid" resultMap="StudentsTeacher">
        select * from student;
    </select>
    <resultMap id="StudentsTeacher" type="Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id = #{id}
    </select>

按照结果嵌套处理

    <select id="getStudentByTid" resultMap="StudentsTeacher1">
        select s.id sid,s.name sname,t.name tname from teacher t,student s where s.tid = t.id;
    </select>
    
    <resultMap id="StudentsTeacher1" type="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>

        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>

    </resultMap>

2.一对多处理

比如：一个老师有用多个学生

按照结果嵌套处理

    <select id="getTeacher" resultMap="TeacherStudent">
        select s.name sname,s.id sid,t.id tid,t.name tname from student s,teacher t where s.tid = t.id and t.id = #{tid};
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="name" column="sname"/>
            <result property="id" column="sid"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

按照查询嵌套处理

    <select id="getTeacher2" resultMap="TeacherStudent1">
        select * from teacher where id = #{tid}
    </select>
    <resultMap id="TeacherStudent1" type="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
        
        <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudent" />

    </resultMap>


    <select id="getStudent" resultType="Student">
        select * from student where tid = #{tid}
    </select>