Mybatis多表连接查询（一对多，多对多)

回顾sql语句：sql语句

• 关联 - association 【多对一】
• 集合 - collection 【一对多】
  

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

多对一

实体类

public class Student {

    private int id;
    private String name;
    private Teacher teacher;
 }

public class Teacher {

    private int id;
    private String name;
    private List<Student> students;
}

dao层

Student getStudent(int id);

思路：先查询学生，再由学生tid找老师

<mapper namespace="com.wang.dao.StudentMapper">

    <select id="getStudent" resultMap="StudentTeacher">
        select * from student where id = #{id}
    </select>
    
    <resultMap id="StudentTeacher" type="com.wang.entity.Student">
        <!--复杂的属性，我们需要单独处理 对象： association 集合： collection -->
        <association property="teacher" column="tid" javaType="com.wang.entity.Teacher" select="getTeacher"/>
    </resultMap>
    
    <select id="getTeacher" resultType="com.wang.entity.Teacher">
        select * from teacher where id = #{tid}
    </select>

</mapper>

一对多

由老师查学生

<select id="getTeacher" resultMap="TeacherStudent">
    select * from mybatis.teacher where id = #{tid}
</select>

<resultMap id="TeacherStudent" type="Teacher">
    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>

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