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=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);
10.1 多对一查询
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
//学生需要关联一个老师
private Teacher teacher;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
}
-
按照查询嵌套处理(类似子查询)
-
编写接口
//查询所有的学生信息以及对应的老师信息 List<Student> getStudent();
-
编写Mapper.xml
<select id="getStudent" resultMap="StudentTeacher"> select * from mybatis.student </select> <resultMap id="StudentTeacher" type="Student"> <result property="id" column="id"/> <result property="name" column="name"/> <!--复杂的属性需要单独处理 对象:association 集合:collection--> <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="Teacher"> select * from mybatis.teacher where id = #{tid} </select>
-
测试
-
-
按照结果嵌套处理(常用,类似联表查询)
Mapper.xml
<select id="getStudent" resultMap="StudentTeacher"> select s.id sid,s.name sname,t.name tname from mybatis.student s,mybatis.teacher t where s.tid = t.id </select> <resultMap id="StudentTeacher" type="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <association property="teacher" javaType="Teacher"> <result property="name" column="tname"/> </association> </resultMap>
10.2 一对多查询
实体类
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
private int id;
private String name;
private int tid;
}
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生
private List<Student> students;
}
-
按照查询嵌套处理(类似子查询)
-
编写接口
//获取指定老师下的所有学生及老师信息 Teacher getTeacher(@Param("tid") int id);
-
编写Mapper.xml
<select id="getTeacher" resultMap="TeacherStudent"> select * from mybatis.teacher where id = #{tid} </select> <resultMap id="TeacherStudent2" type="Teacher"> <collection property="students" column="id" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId"/> </resultMap> <select id="getStudentByTeacherId" resultType="Student"> select * from mybatis.student where tid = #{tid} </select>
-
测试
-
-
按照结果嵌套处理(常用,类似联表查询)
Mapper.xml
<select id="getTeacher" resultMap="TeacherStudent"> select s.id sid,s.name sname,t.name tname,t.id tid from mybatis.student s,mybatis.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"/> <!--javaType:指定属性的类型 ofType:集合中的泛型信息--> <collection property="students" ofType="Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap>
小结
- 关联 - association【多对一】
- 集合 - collection【一对多】
- javaType & ofType
- javaType用来指定实体类中属性的类型
- ofType用来指定映射到List或者集合中的pojo类型,泛型中的约束类型