MyBatis学习系列（五）--一对多联表查询

MyBatis学习系列（五）--一对多联表查询

准备

1. 数据库及表的构建

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

表的内容如下：

student

id	name	tid
1	小明	1
2	小红	1
3	小张	1
4	小李	1
5	小王	1

teacher

id	name
1	秦老师

2. 实体类

import lombok.Data;

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

import lombok.Data;

import java.util.List;

@Data
public class Teacher {
    private int id;
    private String name;
    private List<Student> students;
}

3. DAO层Mapper接口

import org.apache.ibatis.annotations.Param;
import java.util.List;

public interface TeacherMapper {
    Teacher getTeacherById(@Param("tid") int tid);
    Teacher getTeacherById2(@Param("tid") int tid);
}

问题描述

根据老师id取出老师姓名及所有学生信息。

实现

实体类Teacher中的字段与teacher表中的列不是一一对应的，所以select标签要用resultMap属性。

<select id="getTeacherById" resultMap="TeacherStudent">
    SELECT t.id AS tid, t.name AS tname, s.id AS sid, s.name AS sname
    FROM `student` AS s, `teacher` AS t
    WHERE s.tid = t.id AND t.id = #{tid};
</select>

<resultMap id="TeacherStudent" type="com.kuang.pojo.Teacher">
    <result property="id" column="tid"></result>
    <result property="name" column="tname"></result>
    <collection property="students" ofType="com.kuang.pojo.Student">
        <result property="id" column="sid"></result>
        <result property="name" column="sname"></result>
        <result property="tid" column="tid"></result>
    </collection>
</resultMap>

实体类Teacher中的students字段是java的集合(Collection)类型，子标签collection适用此情形（注意区分上篇博客MyBatis学习系列（四）--多对一联表查询中介绍的association标签）。ofType指定泛型集合的类型参数，此例中是实体类Student。

---

上面的解决方案在sql语句中联表查询，下面这种方案采用分两次查询的方法，先根据老师id查出老师id和姓名，再从学生表中查出tid等于老师id的学生。

<select id="getTeacherById2" resultMap="TeacherStudent2">
    SELECT * FROM `teacher` WHERE id = #{tid};
</select>

<resultMap id="TeacherStudent2" type="com.kuang.pojo.Teacher">
    <result property="id" column="id"></result>
    <collection property="students" javaType="ArrayList" ofType="com.kuang.pojo.Student" select="selectStudentById" column="id">
    </collection>
</resultMap>

<select id="selectStudentById" resultType="com.kuang.pojo.Student">
    SELECT * FROM student WHERE tid = #{id};
</select>

××更多mapper.xml的配置信息，参考Mapper XML Files××