多对一:
-
多个学生,对应一个老师
-
对于学生而言, 关联 ,多个学生,关联一个老师【多对一】
-
对于老师而言, 集合 ,一个老师有很多学生【一对多】
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');
测试环境搭建
-
导入lombok
-
新建实体类Teacher,Student
-
建立Mapper接口
-
建立Mapper.xml文件
-
在核心配置文件中绑定注册我们的Mapper接口或者文件【方式很多,自选】
-
测试查询是否能够成功!
Student.java
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
Teacher.java
@Data
public class Teacher {
private int id;
private String name;
}
按照查询嵌套处理
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gt.dao.StudentMapper">
<!--
思路:
1. 查询所有的学生信息
2. 根据查询出来的学生的tid,寻找对应的老师 子查询
-->
<select id="getStudentList" resultMap="studentTeacherMap">
select stu.id,stu.name,stu.tid from student stu;
</select>
<resultMap id="studentTeacherMap" type="com.gt.pojo.Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--
复杂的属性,我们需要单独处理
对象:association
集合:collection
-->
<association property="teacher" column="tid" javaType="com.gt.pojo.Teacher" select="getTeacherList"/>
</resultMap>
<select id="getTeacherList" resultType="com.gt.pojo.Teacher">
select te.id,te.name from teacher te;
</select>
</mapper>
按照结果嵌套处理
<select id="getStudentList2" resultMap="studentTeacherMap2">
select s.id sId, s.name sName, t.name tName from student s,teacher t where s.tid=t.id;
</select>
<resultMap id="studentTeacherMap2" type="com.gt.pojo.Student">
<result property="id" column="sId"/>
<result property="name" column="sName"/>
<association property="teacher" javaType="com.gt.pojo.Teacher">
<result property="name" column="tName"/>
</association>
</resultMap>
回顾mysql多对一查询方式:
-
子查询
-
链表查询