多对一:
- 多个学生,对应一个老师
- 对于学生这边而言,关联…多个学生,关联一个老师【多对一】
- 对于老师而言,集合,一个老师,有很多学生【一对多】
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);
测试环境搭建
1.导入lombok
2.新建实体类Teacher,Student
3.建立Mapper接口
4.建立Mapper.xml文件
5.在核心配置文件中绑定注册我们的Mapper接口或文件!【方式很多,随心选】
6.测试查询是否能够成功!
按照查询嵌套处理
<!--
思路:
1.查询所有的学生信息
2.根据查询出来的学生的tid,寻找对应的老师! 子查询
-->
<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"/>
<!--复杂的属性,我们需要单独处理,对象:accociation,集合:collection-->
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from mybatis.teacher where id = #{id};
</select>
按照结果嵌套处理
<!--按照结果嵌套处理-->
<select id="getStudent2" resultMap="StudentTeacher2">
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="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>
回顾Mysql多对一查询方式:
- 子查询
- 联表查询