10. 多对一处理
10.1 多对一：
多个学生，对应一个老师对于学生而言，关联，多个学生，关联一个老师【多对一】对于老师而言，集合，一个老师，有很多学生【一对多】
SQL:
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');
10.2 测试环境搭建
导入Lombok新建实体类Teacher，Student建立Mapper接口建立Mapper.xml文件在核心配置文件中绑定注册我们的Mapper接口或者文件【方式很多】测试查询是否能成功
10.3 方式一：按照查询嵌套处理
<select id="getStudent" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{id};
</select>
10.4 方式二：按照结果嵌套处理
<select id="getStudent2" resultMap="StudentTeacher2">
select s.id sid,s.name sname,t.name tname,t.id tid
from student s,teacher t
where s.tid = t.id;
</select>
<resultMap id="StudentTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
</association>
</resultMap>
回顾MySQL多对一查询方式：
子查询联表查询
