MyBatis 学习记录：SQL 查询多对一和一对多处理

环境搭建，创建数据库：

CREATE TABLE `teacher`(
`id` INT(10) NOT NULL,
`name` varchar(30) DEFAULT null,
primary key(`id`)
)engine=innodb default charset=utf8mb4;

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=utf8mb4;

insert into `student` (`id`, `name`, `tid`) values 
(1,'张三', 1),
(2,'李四', 1),
(3,'王五', 1),
(4,'赵六', 1),
(5,'孙七', 1);

使用 MyBatis：

多对一用 association 标签来映射

实体类：

// 这里使用了 lombok 来简化
@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 {
    List<Student> getStudent();
}

按照查询嵌套处理：

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

按照结果嵌套处理：

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

一对多用 collection 标签来映射

实体类：

@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<Student> studentList;
}

接口：

public interface TeacherMapper {
    Teacher getTeacher(@Param("tid") int id);
}

按照结果嵌套处理：

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

按照查询嵌套处理：

<select id="getTeacher" resultMap="Teacher">
    select * from teacher where id=#{tid}
</select>
<resultMap id="Teacher" type="teacher">
    <collection property="studentList" column="id" javaType="ArrayList" ofType="student" select="getStudent"/>
</resultMap>
<select id="getStudent" resultType="student">
    select * from student where tid=#{tid}
</select>

按照结果嵌套处理可能会更简单一些