8. 多对一处理

多对一的理解：

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

8.1 数据库的设计

image.png

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

8.2 搭建测试环境

1. 建立以下环境：如图

   
   
   image.png

2. 编写pojo包

   public class Student implements Serializable {
       private Integer id;
       private String name;
       private Teacher teacher;
    //构造方法和get，set方法
   }
   
   public class Teacher {
       private Integer id;
       private String name;
    //构造方法和get，set方法
   }
   
   

3. 编写dao包

   public interface StudentMapper {
       //查找所有的学生信息
       List<Student> getStudents();
   }
   

   <select id="getStudents" resultType="student">
       select * from student;
   </select>
   

4. 测试

   @Test
   public void testGetStudents(){
       SqlSession sqlSession = MybatisUtils.getSqlSession();
       StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
       List<Student> students = mapper.getStudents();
       for (Student student : students) {
           System.out.println(student);
       }
       sqlSession.close();
   }
   

5. 结果

   
   
   image.png

6. 根据结果可以知道teacher都是空，根据前面的知识回忆，说明实体类和数据库的字段名不一致导致的，怎么解决这个问题呢？通过ResultMap解决即可

   • 解决方式一：子查询的方式

     <select id="getStudents" resultMap="studentTeacher">
         select * from student;
     </select>
     
         <resultMap id="studentTeacher" type="student">
         <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
         </resultMap>
     
         <select id="getTeacher" resultType="teacher">
         select * from teacher where id=#{tid};
     </select>
     

   • 解决方式二：联表查询的方式

     <select id="getStudents" resultMap="studentTeacher">
         SELECT s.id sid,s.`name` sname,s.tid tid,t.`name` tname FROM student s INNER JOIN teacher t ON s.tid=t.id;
     </select>
     
     <resultMap id="studentTeacher" type="student">
         <result property="id" column="sid"/>
         <result property="name" column="sname"/>
         <collection property="teacher" javaType="teacher">
             <result property="id" column="tid"/>
             <result property="name" column="tname"/>
         </collection>
     </resultMap>
     

   • 结果：

     
     
     image.png