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.实体类
@Data
public class Student {
private int id;
private String name;
//学生需要关联一个老师
private Teacher teacher;
}
@Data
public class Teacher {
private int id;
private String name;
}
按照查询嵌套处理
子查询
public interface StudentMapper {
//查询所有的学生信息,以及对应的老师信息
List<Student> getStudent();
Teacher getTeacher(@Param("id") int id);
}
<!--
思路:
1.查询所有的学生信息
2.根据查询出来的tid,寻找对应的老师,子查询
-->
<select id="getStudent" resultMap="stuGetTeacher">
select * from mybatis.student;
</select>
<resultMap id="stuGetTeacher" type="Student">
<!--复杂的属性,我们需要单独处理,对象用association,集合用collection-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select *from mybatis.teacher where id=#{id};
</select>
按照结果嵌套处理
连表查询
public interface StudentMapper {
//查询所有的学生信息,以及对应的老师信息
List<Student> getStudent1();
}
<select id="getStudent1" resultMap="stuGetTeacher2">
select stu.id sid,stu.name sname,tea.name tname from mybatis.student stu,mybatis.teacher tea where stu.tid=tea.id;
</select>
<resultMap id="stuGetTeacher2" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>