多对一、一对多处理

创建数据库

create table teacher(
id int not null primary key,
name varchar(20)
);


insert into teacher values(1,'c老师');
insert into teacher values(2,"x老师");


CREATE table student(
id int primary key,
name varchar(20),
tid int,
key fk_tid(tid),
constraint fk_tid 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,"小亮",2);
insert into student values(5,"小黑",2);

表之间的关系：多个学生对应一个老师（关联），一个老师对应多个学生（集合）

多对一处理

目的：查询所有学生的信息，以及对应的老师的名字

sql语句：select student.id,student.name,teacher.name from student,teacher where student.tid = teacher.id;

创建实体类

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

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    private int id;
    private String name;
    
    private Teacher teacher;
}

实现方式：

1 按照查询嵌套处理（不易理解）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.StudentMapper">
    
    
    <!--查询所有学生信息，根据查询出来的学生的tid，再到教师表查询对应的姓名-->
    <select id="getAllStudent" resultMap="student_teacher">
        select * from mybatis.student;
    </select>
    <resultMap id="student_teacher" type="entity.Student">
        <result column="id" property="id"/>
        <result column="name" property="name"/>
        <!--复杂的属性需要单独处理，对象用association，集合用collection-->
        <association property="teacher" column="tid" javaType="entity.Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="entity.Teacher">
        select * from mybatis.teacher where id = #{id};
    </select>
    

</mapper>

2 按照结果嵌套处理

    <select id="getAllStudent2" resultMap="student_teacher2">
        select s.id sid, s.name sname,t.name tname from student s ,teacher t
        where s.tid = t.id
    </select>
    <resultMap id="student_teacher2" type="entity.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="entity.Teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

一对多

目的：查询某个老师下的所有学生

sql语句：select t.id ,t.name, s.id,s.name from teacher t,student s where s.tid = t.id and t.id = 1

创建实体类：

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

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

    //一个老师对应多个学生
    private List<Student> students;
}

接口

public interface TeacherMapper {

    Teacher getTeacher(@Param("tid") int id);
}

1 按照结果嵌套处理

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="dao.TeacherMapper">


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


</mapper>

2 按照查询嵌套处理

<select id="getTeacher2" resultMap="teacher_student2">
    select * from teacher where id = #{tid};
</select>
    <resultMap id="teacher_student2" type="entity.Teacher">
        <collection property="students"  column="id" javaType="ArrayList" ofType="entity.Student" select="getStudents"/>
    </resultMap>
    <select id="getStudents" resultType="entity.Student">
        select * from student where tid = #{tid};
    </select>