Mybatis一对多和多对一处理的深入讲解

create table teacher(

     id int not null ,

     name varchar (30) default null ,

     primary key (id)

);

insert into teacher (id, name ) values (1, '蔡老师' );

create table student(

     id int not null ,

     name varchar (30) default null ,

     tid int default null ,

     constraint fk_tid foreign key (tid) references teacher(id)

);

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

//查询所有的学生信息以及对应的老师的信息

List<Student> queryStudentAndTeacher();

@Data

@AllArgsConstructor

@NoArgsConstructor

public class Student {

     private int id;

     private String name;

     //学生需要关联一个老师

     private Teacher teacher;

}

@Data

@AllArgsConstructor

@NoArgsConstructor

public class Teacher {

     private int id;

     private String name;

}

<!--思路：

     1 .查询所有的学生

     2 .根据查询出来的学生的tid寻找对应的老师  寻找对应的老师，子查询

-->

<resultMap id= "rStuAndTea" type= "student" >

     <result property= "id" column= "id" />

     <result property= "name" column= "name" />

     <!-- 复杂的属性我们需要单独处理 

                             指定属性的类型

          对象使用association  javaType

          集合使用collection   ofType

     -->

     <association property= "teacher" column= "tid" javaType= "Teacher" select= "getTeacher" />

</resultMap>

<select id= "queryStudentAndTeacher" resultMap= "rStuAndTea" >

     select * from student

</select>

<select id= "getTeacher" resultType= "teacher" >

     select * from teacher where id = #{id}

</select>

<!--方式二  按照结果嵌套处理-->

<resultMap id= "rStuAndTea2" type= "student" >

     <result property= "id" column= "sid" />

     <result property= "name" column= "sname" />

     <association property= "teacher" javaType= "Teacher" >

         <result property= "name" column= "tname" />

     </association>

</resultMap>

<select id= "queryStudentAndTeacher2" resultMap= "rStuAndTea2" >

     select s.id sid, s.name sname, t.name tname

     from student s, teacher t

     where s.tid = t.id

</select>

//查询指定老师的信息及其所有的学生

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

@Data

@AllArgsConstructor

@NoArgsConstructor

public class Teacher {

     private int id;

     private String name;

     //一个老师拥有多个学生

     private List<Student> students;

}

@Data

@AllArgsConstructor

@NoArgsConstructor

public class Student {

     private int id;

     private String name;

     private int tid;

}

<!--按照结果嵌套查询-->

<resultMap id= "rTeaAndStu" type= "teacher" >

     <result property= "id" column= "tid" />

     <result property= "name" column= "tname" />

     <collection property= "students" ofType= "Student" >

         <result property= "id" column= "sid" />

         <result property= "name" column= "sname" />

         <result property= "tid" column= "tid" />

     </collection>

</resultMap>

<select id= "queryTeaAndStu" resultMap= "rTeaAndStu" >

     select s.id sid, s.name sname, t.name tname, t.id tid

     from student s, teacher t

     where s.tid = t.id and t.id = #{tid}

</select>

<!--按照查询嵌套处理-->

<select id= "queryTeaAndStu2" resultMap= "rTeaAndStu2" >

     select * from teacher where id = #{tid}

</select>

<resultMap id= "rTeaAndStu2" type= "teacher" >

     <collection property= "students" javaType= "ArrayList" ofType= "Student"

                 select= "queryStudentByTeacherId" column= "id" />

</resultMap>

<select id= "queryStudentByTeacherId" resultType= "Student" >

     select * from student where tid = #{tid}

</select>