MyBatis多对一、多对一处理

• 一个班级有多个学生
• 学生角度：（关联）多个学生关联一个班级
• 班级角度：（集合）一个班级集合了多个学生

• 数据库建表

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 `students` (
  `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 `students` (`id`, `name`, `tid`) VALUES ('1', '小明', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('2', '小红', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('3', '小张', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('4', '小李', '1'); 
INSERT INTO `students` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

• 建实体类

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Students {
    private  int id;
    private   String   name;
    //多个学生对应一个老师
    private Teacher teacher;
}

• 建Mapper接口和mapper.xml
• 可以将mapper.xml放到项目的resources下同包目录下，dao包中只保留Mapper接口，编译后同包目录下的文件都输出到target文件中
• 在核心配置文件中注册mapper接口
• 测试查询是否成功

• 按照查询进行嵌套处理：子查询
• 题目：查询所有的学生消息（附带老师信息）

    
    
        
        
        
        
        
    
    
        select * from students ;
    
    
        select * from teacher where id = #{tid};
    

 
按照结果进行嵌套处理：关联查询
 

    SELECT s.id sid,s.name sname ,t.id tid,t.name tname FROM students s,teacher t WHERe s.tid = t.id


    
    
    
        
        
    

• 题目：查询一个老师代课的学生消息

• 实体类修改

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Students {
    private  int id;
    private   String   name;
    private int tid;
}

@Data
@NoArgsConstructor
@AllArgsConstructor
public class Teacher {
    private int id;
    private String name;
    //一个老师关联多个学生
    private List students;
}

• 按查询进行嵌套处理，子查询方式

 
    
        select * from teacher where id = #{id}
    
    
        
        
        
        
    
    
        select * from students where tid = #{id}
    

• 按结果进行嵌套处理，关联查询

    

  SELECT s.id sid,s.name sname ,t.id tid,t.name tname FROM students s,teacher t WHERe s.tid = t.id
    and t.id = #{id}


    
        
        
        
        
            
            
        
    
  

• 测试

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

• 小结

collection:一对多

association：多对一

javaType=“”;指定实体类中属性的类型
ofType=“”;指定映射到集合中的实体类型，集合中的泛型属性

• 注意

sql的可读性

一对多和多对一，属性和字段的映射关系

可以使用日志来排错