多对一:例如王者荣耀对抗路,吃鸡单人四排,
这里列举学生与老师微妙的关联关系:
数据库设计:
create table teacher(
id int(10) primary key ,
name varchar(20) default null
);
insert into teacher(id, name) VALUES (1,'宋老师')
create table student(
id int(10) primary key ,
name varchar(20) not null ,
tid int(10) ,
foreign key (tid) references teacher(id)
);
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');
insert into student(id, name, tid) VALUES ('6','小黄','1');
select * from student;
学生外键tid对应老师id
环境搭建
导入lombok(方便偷懒),懂得都懂
实体类对应数据库
编写实体接口(dao类)
相应的xml配置文件
mapper映射
设计查询语句
联表查询
select s.id,s.name,t.name from student s,teacher t where s.tid=t.id
设计xml配置查询
<select id="getStu" resultMap="stuTeacher">
select * from testweb.student
</select>
<resultMap>
<result proprety="id" column="id"/>
<result property="name" column="name"/>
<association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from testweb.teacher where id= #{id}
</select>
分析:学生类中有个teacher类属性;不是普通属性,不能用普通查询,所以需要切换,嵌套子查询,通过tid查出老师name
通过结果嵌套处理
<select id="getStu1" resultMap="ST" >
select s.id sid,s.name sname,t.name tname,t.id tid from testweb.teacher t,testweb.student s where s.tid=t.id
</select>
<resultMap id="ST" type="student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<association property="teacher" javaType="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"></result>
</association>
</resultMap>