mybatis基于注解的表关联查询(一)
sql中表之间关系
一对一,一对多,多对多
举个例子
数据库如下
<!--学生表-->
create table student
(
id int PRIMARY key auto_increment,
name varchar(50),
age int
);
<!--课程表-->
create table course
(
id int PRIMARY key auto_increment,
name varchar(50)
);
<!--成绩表-->
create table score
(
id int PRIMARY key auto_increment,
sid int references student (id),
cid int references course (id),
grade FLOAT
);
insert into student values(1,'zs',18);
insert into course values (1,'java');
insert into course values (2,'.Net');
insert into course values (3,'.PHP');
insert into score values(1,1,1,100.0);
insert into score values (2,1,2,98.0);
insert into score values (3,1,3,96.0);
select * from student ;
select * from score ;
select * from course ;
处理以下业务
1,查询每门课的成绩
一对多
<!--定义vo对象-->
@Data
@AllArgsConstructor
@NoArgsConstructor
public class CourseScoreVo {
private Integer id;
private String name;
private List<Score> score;
}
<!--根据课程Id查询成绩-->
@Select("select * from score where cid=#{cid}")
List<Score> selectByCId(int cid);
<!--查询课程连接-根据课程Id查询成绩的sql-->
@Select("select * from course ")
@Results({
@Result(column = "id",property = "id"),
<!--公共列-->
@Result(column = "id" ,property = "score",one = @One(select = "com.zy.mapper.ScoreMapper.selectByCId"))
})
List<CourseScoreVo> selectCourseScore();
#### 查询结果如下
CourseScoreVo(id=1, name=java, score=[Score(id=1, sid=1, cid=1, grade=100.0), Score(id=4, sid=2, cid=1, grade=100.0)])
CourseScoreVo(id=2, name=.Net, score=[Score(id=2, sid=1, cid=2, grade=98.0), Score(id=5, sid=2, cid=2, grade=98.0)])
CourseScoreVo(id=3, name=.PHP, score=[Score(id=3, sid=1, cid=3, grade=96.0), Score(id=6, sid=2, cid=3, grade=96.0)])
注:1.公共列的Id是主sql查询出的Id列而不是要连接的SQL语句的Id列
2.公共列的Id列的值将被当成参数传入连接的sql语句
3.one=@One是将连接的sql语句的返回值与vo对象中的(score)储存对象一一对应
many=@Many是将连接的sql语句的返回值与vo对象中的(score)储存对象交叉对应
总结:
一对多
一对一原理一致:通过公共列传参到连接的sql把返回值与通过property=“?” 进行对应装进vo对象
个人理解,不足请谅解并指出