Mybatis05-一对多和多对一处理

多对一和一对多

多对一

多对一的理解：

• 多个学生对应一个老师

• 如果对于学生这边，就是一个多对一的现象，即从学生这边关联一个老师！

结果映射（resultMap）：

• association

• • 一个复杂类型的关联；许多结果将包装成这种类型
• • 嵌套结果映射 —— 关联可以是 resultMap 元素，或是对其它结果映射的引用

• collection

• • 一个复杂类型的集合
• • 嵌套结果映射 —— 集合可以是 resultMap 元素，或是对其它结果映射的引用

以下使用两种方式实现以下sql语句：

select s.id ,s.name ,t.name from student s,teacher t where s.tid=t.id

1、按照查询嵌套处理

类似子查询

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

2. 实体类STudent和Teacher

public class Teacher {private int id;private String name;public Teacher(){}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Teacher{" +"id=" + id +", name='" + name + '\'' +'}';}
}

public class Student {private int id;public String getName() {return name;}public void setName(String name) {this.name = name;}private String name;private Teacher teacher;//学生需要关联一个老师public Student(){}public int getId() {return id;}public void setId(int id) {this.id = id;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}@Overridepublic String toString() {return "Student{" +"id=" + id +", name='" + name + '\'' +", teacher=" + teacher +'}';}
}

3. StudentMapper接口和TeacherMapper

package com.study.dao;
import com.study.pojo.Student;
import java.util.List;public interface StudentMapper {//查询所有的学生信息，以及对应的老师的信息public List<Student> getStudent();
}

public interface TeacherMapper {}

4. StudentMapper.xml映射文件

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--namespace绑定一个对应的Dao/Mapper接口的全限定名-->
<mapper namespace="com.study.dao.StudentMapper"><resultMap id="StudentTeacher" type="Student"><!--主键可使用<id>--><result property="id" column="id"/><result property="name" column="name"/><!--复杂的属性，需要单独处理——引用类型：<association>  集合：<collection>--><association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/></resultMap><select id="getStudent" resultMap="StudentTeacher">select * from student</select><select id="getTeacher" resultType="Teacher">select * from teacher where id=#{id}</select>
</mapper>

5. 核心配置文件

<mappers><mapper class="com.study.dao.TeacherMapper"/><mapper class="com.study.dao.StudentMapper"/>
</mappers>

6. 测试

@Test
public void testStudent(){SqlSession sqlSession = MybatisUtils.getSqlSession();StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> studentList = mapper.getStudent();//动态代理产生一个实现Mapper接口的对象并赋值个，将该对象赋值给接口的引用for (Student student : studentList) {System.out.println(student);}sqlSession.close();
}

2、按结果嵌套处理

1. StudentMapper接口

   public List<Student> getStudent2();
   

2. StudentMapper.xml

   <!--===========按照结果嵌套查询==============--><!--按查询结果嵌套处理思路：1. 直接查询出结果，进行结果集的映射-->
   <select id="getStudent2" resultMap="StudentTeacher2">select s.id sid,s.name sname,t.name tnamefrom student s,teacher twhere 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>

3. 测试

   @Test
   public void testStudent(){SqlSession sqlSession = MybatisUtils.getSqlSession();StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> studentList = mapper.getStudent2();//动态代理产生一个实现Mapper接口的对象并赋值个，将该对象赋值给接口的引用for (Student student : studentList) {System.out.println(student);}sqlSession.close();
   }

小结

• 按照查询进行嵌套处理就像SQL中的子查询
• 按照结果进行嵌套处理就像SQL中的联表查询

一对多

1. 实体类

   package com.study.pojo;public class Student {private int id;public String getName() {return name;}public void setName(String name) {this.name = name;}private String name;public int getTid() {return tid;}public void setTid(int tid) {this.tid = tid;}private int tid;public Student(){}public int getId() {return id;}public void setId(int id) {this.id = id;}@Overridepublic String toString() {return "Student{" +"id=" + id +", name='" + name + '\'' +", tid=" + tid +'}';}
   }
   

   package com.study.pojo;import java.util.List;public class Teacher {private int id;private String name;private List<Student> students;//一个老师拥有多个学生public List<Student> getStudents() {return students;}public void setStudents(List<Student> students) {this.students = students;}public Teacher(){}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getName() {return name;}public void setName(String name) {this.name = name;}@Overridepublic String toString() {return "Teacher{" +"id=" + id +", name='" + name + '\'' +", students=" + students +'}';}
   }
   

2. TeacherMapper接口

   List<Teacher> getTeacher();
   

3. TeacherMapper.xml

   <select id="getTeacher" resultType="Teacher">select * from teacher;
   </select>
   

4. 核心配置文件中注册Mapper

5. 测试环境正常

   @Test
   public void test(){SqlSession sqlSession = MybatisUtils.getSqlSession();TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);List<Teacher> teacherList = mapper.getTeacher();for (Teacher teacher : teacherList) {System.out.println(teacher);}sqlSession.close();
   }
   

   1、按结果嵌套查询

   1. TeacherMapper接口`

      Teacher getTeacherById(@Param("tid")int id);
      

   2. TeaccherMapper.xml——按结果嵌套查询

      <!--==================按结果嵌套查询================-->
      <select id="getTeacherById" resultMap="TeacherStudent">select t.name tname,t.id tid,s.id sid,s.name snamefrom student s,teacher twhere s.tid=t.id and t.id=#{tid}
      </select><resultMap id="TeacherStudent" type="Teacher"><result property="id" column="tid"/><result property="name" column="tname"/><!--复杂的属性，需要单独处理——引用类型：<association>  javaType:指定的属性的类型集合：<collection>   集合中的泛型信息，我们使用ofType获取--><!--Teacher实体类中有一个名为students的引用类型的List集合，将List中Student对象的各属性与sql语句返回的字段进行映射--><collection property="students" ofType="Student"><result property="id" column="sid"/><result property="name" column="sname"/><result property="tid" column="tid"/></collection>
      </resultMap>
      

   3. 测试

      @Test
      public void getTeacherById(){SqlSession sqlSession = MybatisUtils.getSqlSession();TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);Teacher teacher = mapper.getTeacherById(1);System.out.println(teacher);sqlSession.close();
      }输出：
      Opening JDBC Connection
      Created connection 1278254413.
      ==>  Preparing: 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=?
      ==> Parameters: 1(Integer)
      <==    Columns: sid, sname, tname, tid
      <==        Row: 1, 小明, 秦老师, 1
      <==        Row: 2, 小红, 秦老师, 1
      <==        Row: 3, 小张, 秦老师, 1
      <==        Row: 4, 小李, 秦老师, 1
      <==        Row: 5, 小王, 秦老师, 1
      <==      Total: 5
      Teacher{id=1, name='秦老师', students=[Student{id=1, name='小明', tid=1}, Student{id=2, name='小红', tid=1}, Student{id=3, name='小张', tid=1}, Student{id=4, name='小李', tid=1}, Student{id=5, name='小王', tid=1}]}
      Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@4c309d4d]
      Returned connection 1278254413 to pool.
      

   2、按照查询嵌套处理

   1. TeacherMapper

      Teacher getTeacherById2(@Param("tid")int id);
      

   2. TeacherMapper.xml

      <!--===========按照查询嵌套处理================-->
      <select id="getTeacherById2" resultMap="TeacherStudent2">select * from mybatis.teacher where id=#{tid}
      </select><resultMap id="TeacherStudent2" type="Teacher"><collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/><!--这里的column="id"是teacher表中的id-->
      </resultMap><select id="getStudentByTeacherId" resultType="Student"><!--这里的id是上文中的id-->select * from student where tid=#{id}
      </select>

   3. 测试

          @Testpublic void getTeacherById2() {SqlSession sqlSession = MybatisUtils.getSqlSession();TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);Teacher teacher = mapper.getTeacherById2(1);System.out.println(teacher);sqlSession.close();}输出：
      Opening JDBC Connection
      Created connection 931675031.
      ==>  Preparing: select * from mybatis.teacher where id=?
      ==> Parameters: 1(Integer)
      <==    Columns: id, name
      <==        Row: 1, 秦老师
      ====>  Preparing: select * from student where tid=?
      ====> Parameters: 1(Integer)
      <====    Columns: id, name, tid
      <====        Row: 1, 小明, 1
      <====        Row: 2, 小红, 1
      <====        Row: 3, 小张, 1
      <====        Row: 4, 小李, 1
      <====        Row: 5, 小王, 1
      <====      Total: 5
      <==      Total: 1
      Teacher{id=0, name='秦老师', students=[Student{id=1, name='小明', tid=1}, Student{id=2, name='小红', tid=1}, Student{id=3, name='小张', tid=1}, Student{id=4, name='小李', tid=1}, Student{id=5, name='小王', tid=1}]}
      Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@37883b97]
      Returned connection 931675031 to pool.
      

小结

• 关联：association【多对一】

• 集合：collection 【一对多】

• association是用于一对一和多对一，而collection是用于一对多的关系

• javaType & ofType

• • JavaType是用来指定pojo中属性的类型
• • ofType指定的是映射到list集合属性中pojo的类型。

注意说明：

1、保证SQL的可读性，尽量通俗易懂

2、根据实际要求，尽量编写性能更高的SQL语句

3、注意属性名和字段不一致的问题

4、注意一对多和多对一 中：字段和属性对应的问题

5、尽量使用Log4j，通过日志来查看自己的错误

面试高频：

• Mysql引擎

• InnoDB底层原理

• 索引

d=4, name=‘小李’, tid=1}, Student{id=5, name=‘小王’, tid=1}]}
Closing JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@37883b97]
Returned connection 931675031 to pool.
```

小结

• 关联：association【多对一】

• 集合：collection 【一对多】

• association是用于一对一和多对一，而collection是用于一对多的关系

• javaType & ofType

• • JavaType是用来指定pojo中属性的类型
• • ofType指定的是映射到list集合属性中pojo的类型。

注意说明：

1、保证SQL的可读性，尽量通俗易懂

2、根据实际要求，尽量编写性能更高的SQL语句

3、注意属性名和字段不一致的问题

4、注意一对多和多对一 中：字段和属性对应的问题

5、尽量使用Log4j，通过日志来查看自己的错误

面试高频：

• Mysql引擎

• InnoDB底层原理

• 索引

• 索引优化