开启掘金成长之旅！这是我参与「掘金日新计划 · 12 月更文挑战」的第28天，点击查看活动详
多对一概述
什么是多对一处理？
在我们的现实生活中，实际存在的场景是非常复杂的，单单仅靠着一对一的关系是无法满足的。通过一个例子来瞅瞅什么是多对一，假如在一个班级里面会有很多个同学和各科的代课老师，那么在上语文课的时候，对于学生而言，班级里的所有学生在上语文课时，都是一个语文老师进行授课，这就是多对一的场景。接下来我们使用实际的代码来进一步了解。
搭建数据库环境
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');
新建实体类 Teacher，Student
@Data
public class Teacher {
private int id;
private String name;
}
@Data
public class Student {
private int id;
private String name;
//关联老师
private Teacher teacher;
}
建立Mapper接口
public interface TeacherMapper {
@Select("select * from teacher where id=#{tid}")
Teacher getTeacher(@Param("tid") int id);
}
public interface StudentMapper {
List<Student> getStudent();
List<Student> getStudent2();
}
建立Mapper.XML文件
StudentMapper:
按照查询嵌套处理
<?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="com.zhao.dao.StudentMapper">
<!-- 按照查询嵌套处理-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"></result>
<result property="name" column="name" />
<!-- 复杂的属性需要单独处理 对象：association 集合：collection -->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher" />
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{tid}
</select>
</mapper>
测试运行
public void testStudent(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
List<Student> students=mapper.getStudent();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
按照结果嵌套处理
<?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="com.zhao.dao.StudentMapper">
<!-- 按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
SELECT s.`id` sid,s.`name` sname,t.`name` tname
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="name" column="tname" />
</association>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{tid}
</select>
</mapper>
测试运行
public void testStudent2(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
StudentMapper mapper=sqlSession.getMapper(StudentMapper.class);
List<Student> students=mapper.getStudent2();
for (Student student : students) {
System.out.println(student);
}
sqlSession.close();
}
一对多
还是以多对一的场景为例子，这次站到老师的角度思考，一个代课老师会教授很多个学生知识，所以老师和学生的关系是一对多的关系。
数据库环境与多对一是相同的，只是实体类，对应的接口和Mapper文件有所不同。
实体类
@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> students;
}
Mapper接口
public interface TeacherMapper {
//获取指定老师下的所有学上以及老师信息
Teacher getTeacher(int id);
Teacher getTeacher2(int id);
}
public interface StudentMapper {
}
建立Mapper.XML文件
TeacherMapper:
按照结果嵌套查询
<?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="com.zhao.dao.TeacherMapper">
<!-- 按照结果嵌套查询-->
<select id="getTeacher" resultMap="TeacherStudent">
SELECT s.`id` sid,s.`name` sname,t.`name` tname,t.`id` tid
FROM student s,teacher t
WHERE s.`tid`=t.`id` and t.id=#{tid};
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid" />
<result property="name" column="tname" />
<collection property="students" ofType="Student">
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
</mapper>
测试运行
public void test(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);
Teacher teacher=mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
按照查询嵌套处理
<?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="com.zhao.dao.TeacherMapper">
<select id="getTeacher2" resultMap="TeacherStudent2">
select * from teacher where id=#{tid}
</select>
<resultMap id="TeacherStudent2" type="Teacher">
<collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentBygetTeacherId" />
</resultMap>
<select id="getStudentBygetTeacherId" resultType="Student" >
select * from student where tid=#{tid}
</select>
</mapper>
测试运行
public void test(){
SqlSession sqlSession= MybatisUtils.getSqlSession();
TeacherMapper mapper=sqlSession.getMapper(TeacherMapper.class);
Teacher teacher=mapper.getTeacher2(1);
System.out.println(teacher);
sqlSession.close();
}
小结
-
关联 - association 【多对一】
-
集合 - collection 【一对多】
-
javaType & ofType
-
JavaType 用来指定实体类中属性的类型 -
ofType 用来指定映射到List或者集合中的 pojo类型，泛型中的约束类型！
