Mybatis笔记10-11---多对一和一对多

10、多对一

多对一：

多个学生，对应一个老师对于学生这边而言，关联···多个学生关联一个老师【多对一】对于老师而言， 集合， 一个老师有很多学生【多对一】 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=utf8INSERT 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');

1.测试环境搭建

在pom.xml中导入lombokjar包

<dependencies> <!-- https://mvnrepository.com/artifact/org.projectlombok/lombok --> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.10</version> <!--作用域<scope>provided</scope>--> </dependency> </dependencies>

新建实体类Teacher,Student

//老师实体类 @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; } //学生实体类 @Data @AllArgsConstructor @NoArgsConstructor public class Student { private int id; private String name; //学生关联老师，每个学生对象中有一个老师 private Teacher teacher; }

建立实体类对应的Mapper接口

在核心配置中绑定注册我们的Mapper接口或者文件!

<!--绑定接口--> <mappers> <mapper class="com.cfeng.dao.TeacherMapper"/> </mappers>

测试查询是否成功！

@Test public void getTeacher(){ SqlSession sqlSession = MybatisUtils.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); }

2.按照查询嵌套处理

StudentMapper.xml

<!-- 思路： 1.查询学生所有信息 2.根据查询出来的学生的tid，寻找对应的老师！（子查询） --> <select id="getStudent" resultMap="StudentTeachet"> select * from student </select> <resultMap id="StudentTeachet" type="Student"> <result column="id" property="id"/> <result column="name" property="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>

3.按照结果嵌套处理

StudentMapper.xml

<!--方法二：按照结果嵌套处理--> <select id="getStudent2" resultMap="StudentTeachet2"> select s.id sid,s.name sname,t.name tname from student s,teacher t where s.tid=t.id; </select> <resultMap id="StudentTeachet2" type="Student"> <result column="sid" property="id"/> <result column="sname" property="name"/> <association property="teacher" javaType="Teacher"> <result column="tname" property="name"/> </association> </resultMap>

回顾Mysql多对一查询方式：

子查询联表查询

11、一对多处理

1、环境搭建

比如：一个老师有多个学生！对老师而言就是一对多的关系！

环境搭建步骤与多对一过程一样

实体类

//老师实体类 @Data @AllArgsConstructor @NoArgsConstructor public class Teacher { private int id; private String name; //当一个老师拥有多个学生 private List<Student> students; } //学生实体类 @Data @AllArgsConstructor @NoArgsConstructor public class Student { private int id; private String name; private int tid; }

2.按照查询嵌套处理

StudentMapper.xml

<select id="getTeacher2" resultMap="StudentTeacher2"> select * from teacher where id = #{tid} </select> <resultMap id="StudentTeacher2" type="teacher"> <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/> </resultMap> <select id="getStudentByTeacherId" resultType="Student"> select * from student where tid = #{tid} </select>

3.按照结果嵌套处理

StudentMapper.xml

<!--按结果查询--> <select id="getTeacher" resultMap="StudentTeacher"> 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> <!--column是数据库中的字段 property是实体类中的字段--> <resultMap id="StudentTeacher" type="Teacher"> <result column="tname" property="name"/> <result column="tid" property="id"/> <!--复杂的属性，我们要单独处理 对象association 集合：collection javaType=""是指定属性的类型！ 集合中泛型类信息我们用ofType=""获取 --> <collection property="students" ofType="Student"> <result column="sid" property="id"/> <result column="sname" property="name"/> <result column="tid" property="tid"/> </collection> </resultMap>

小结

关联 -【多对一】集合 - 【一对多】javaType & ofType javaType 用来指定实体类中的属性类型ofType用来指定映射到List或者集合中的pojo类型，泛型的约束类型！

注意点：

保证SQL的可读性，尽量保证通俗易懂注意一对多和多对一中，属性和字段的问题1如果问题不好排查错误，可以使用日志，建议使用Log4j

面试高频

Mysql引擎InnoDB底层原理索引索引优化！