MyBatis如何实现多表查询（多对一、一对多）

create table teacher(
   id int  primary key,
	teacher_name varchar(30) not null
)

insert into teacher(id,teacher_name) values (1,'毛老师')

create table student(
  id int  primary key,
	student_name varchar(30) not null,
	tid int default null
)

//建立主外键关联
alter table student add constraint teacher_student_id foreign key (tid) references teacher(id)

insert into student values (1,'小明',1)
insert into student values (2,'小毛',1)
insert into student values (3,'小红',1)
insert into student values (4,'大黄',1)
insert into student values (5,'超儿',1)

@Data
public class Student {
    private int id;
    private String name;
    //学生需要关联一个老师
    private Teacher teacher;
}

@Data
public class Teacher {
    private int id;
    private String name;
}

public interface StudentMapper {
    //查询所有学生的信息以及对应老师的信息
    public List getStudent();
}

    
    select * from student
    
    


        
        
    
    
        select * from teacher where id=#{id}
    

    @Test
    public void getStudent(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

//按照结果嵌套查询
public List getStudent2();

    
    select s.id sid,s.student_name sname,t.teacher_name tname
    from student s,teacher t
    where s.tid=t.id
    
    
        
        
        
            
        
    

    @Test
    public void getStudent(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List studentList = mapper.getStudent2();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

@Data
public class Teacher {
    private int id;
    private String name;
    //一个老师拥有多个学生
    private List students;
}

Teacher getTeacher2(@Param("tid") int id);

            select * from teacher where id=#{tid}
    
    
        
        
    
    
        select * from student where tid=#{tid}
    

   @Test
    public void getTeacher(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher2(1);
        System.out.println(teacher);
        sqlSession.close();
    }

Teacher(id=0, name=毛老师, students=[Student(id=1, name=null, tid=1), Student(id=2, name=null, tid=1), Student(id=3, name=null, tid=1), Student(id=4, name=null, tid=1), Student(id=5, name=null, tid=1)])

//获取指定老师下的所有学生及老师的信息
    Teacher getTeacher(@Param("tid") int id);

    
        select s.id sid,s.student_name sname,t.teacher_name tname,t.id tid
        from student s,teacher t
        where s.tid=t.id and t.id=#{tid}
    
    
        
        
        
        
            
            
            
        
    

   @Test
    public void getTeacher(){

        SqlSession sqlSession = Mybatisutil.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }

Teacher(id=1, name=毛老师, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小毛, tid=1), Student(id=3, name=小红, tid=1), Student(id=4, name=大黄, tid=1), Student(id=5, name=超儿, tid=1)])

  select
       B.id as blog_id,
       B.title as blog_title,
       B.author_id as blog_author_id,
       A.id as author_id,
       A.username as author_username,
       A.password as author_password,
       A.email as author_email,
       A.bio as author_bio,
       A.favourite_section as author_favourite_section,
       P.id as post_id,
       P.blog_id as post_blog_id,
       P.author_id as post_author_id,
       P.created_on as post_created_on,
       P.section as post_section,
       P.subject as post_subject,
       P.draft as draft,
       P.body as post_body,
       C.id as comment_id,
       C.post_id as comment_post_id,
       C.name as comment_name,
       C.comment as comment_text,
       T.id as tag_id,
       T.name as tag_name
  from Blog B
       left outer join Author A on B.author_id = A.id
       left outer join Post P on B.id = P.blog_id
       left outer join Comment C on P.id = C.post_id
       left outer join Post_Tag PT on PT.post_id = P.id
       left outer join Tag T on PT.tag_id = T.id
  where B.id = #{id}