MyBatis如何实现多表查询（多对一、一对多） MyBatis如何实现多表查询（多对

MyBatis实现多表查询
一、多对一查询
数据库的准备

文章图片

创建两张表，一张老师表，一张学生表
将老师主键id关联学生外键tid
创建sql的语句

create table teacher(id intprimary key, teacher_name varchar(30) not null)insert into teacher(id,teacher_name) values (1,'毛老师')create table student(id intprimary key, student_name varchar(30) not null, tid int default null)//建立主外键关联alter table student add constraint teacher_student_id foreign key (tid) references teacher(id)insert into student values (1,'小明',1)insert into student values (2,'小毛',1)insert into student values (3,'小红',1)insert into student values (4,'大黄',1)insert into student values (5,'超儿',1)

项目结构

文章图片

使用Lombok插件，创建实体类。
（提高整洁度，主要想toulan）

@Datapublic class Student {private int id; private String name; //学生需要关联一个老师private Teacher teacher; }

@Datapublic class Teacher {private int id; private String name; }

1、嵌套查询处理
编写接口

public interface StudentMapper {//查询所有学生的信息以及对应老师的信息public List getStudent(); }

2. 编写StudentMapper.xml的查询语句（重点）

select * from studentselect * from teacher where id=#{id}

测试类

@Testpublic void getStudent(){SqlSession sqlSession = Mybatisutil.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List studentList = mapper.getStudent(); for (Student student : studentList) {System.out.println(student); }sqlSession.close(); }

测试结果

文章图片

2、联合查询处理
编写接口

//按照结果嵌套查询public List getStudent2();

2. 编写StudentMapper.xml的查询语句（重点）

select s.id sid,s.student_name sname,t.teacher_name tnamefrom student s,teacher twhere s.tid=t.id

编写测试类

@Testpublic void getStudent(){SqlSession sqlSession = Mybatisutil.getSqlSession(); StudentMapper mapper = sqlSession.getMapper(StudentMapper.class); List studentList = mapper.getStudent2(); for (Student student : studentList) {System.out.println(student); }sqlSession.close(); }

测试结果

文章图片

二、一对多查询
更改实体类

@Datapublic class Student {private int id; private String name; private int tid; }

@Datapublic class Teacher {private int id; private String name; //一个老师拥有多个学生private List students; }

1、嵌套查询处理编写接口

Teacher getTeacher2(@Param("tid") int id);

由于字段不一致，要做映射
主要TeacherMapper.xml的查询语句（重点）

select * from teacher where id=#{tid}select * from student where tid=#{tid}

测试类

@Testpublic void getTeacher(){SqlSession sqlSession = Mybatisutil.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher2(1); System.out.println(teacher); sqlSession.close(); }

测试结果：

文章图片

Teacher(id=0, name=毛老师, students=[Student(id=1, name=null, tid=1), Student(id=2, name=null, tid=1), Student(id=3, name=null, tid=1), Student(id=4, name=null, tid=1), Student(id=5, name=null, tid=1)])

2、联合查询处理
编写接口

//获取指定老师下的所有学生及老师的信息Teacher getTeacher(@Param("tid") int id);

由于字段不一致，要做映射
主要TeacherMapper.xml的查询语句（重点）

select s.id sid,s.student_name sname,t.teacher_name tname,t.id tidfrom student s,teacher twhere s.tid=t.id and t.id=#{tid}

测试类

@Testpublic void getTeacher(){SqlSession sqlSession = Mybatisutil.getSqlSession(); TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class); Teacher teacher = mapper.getTeacher(1); System.out.println(teacher); sqlSession.close(); }

测试结果：

文章图片

Teacher(id=1, name=毛老师, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小毛, tid=1), Student(id=3, name=小红, tid=1), Student(id=4, name=大黄, tid=1), Student(id=5, name=超儿, tid=1)])

总结：
本章就使用了简单的两张表联合查询，介绍简单的使用，更复杂的多表联合主要在编写sql的时候难度大点，或者是嵌套查询要更严谨点
官方文档也给了详细的非常复杂的多表查询如下： mybatis，这么复杂的看的我头疼

selectB.id as blog_id,B.title as blog_title,B.author_id as blog_author_id,A.id as author_id,A.username as author_username,A.password as author_password,A.email as author_email,A.bio as author_bio,A.favourite_section as author_favourite_section,P.id as post_id,P.blog_id as post_blog_id,P.author_id as post_author_id,P.created_on as post_created_on,P.section as post_section,P.subject as post_subject,P.draft as draft,P.body as post_body,C.id as comment_id,C.post_id as comment_post_id,C.name as comment_name,C.comment as comment_text,T.id as tag_id,T.name as tag_namefrom Blog Bleft outer join Author A on B.author_id = A.idleft outer join Post P on B.id = P.blog_idleft outer join Comment C on P.id = C.post_idleft outer join Post_Tag PT on PT.post_id = P.idleft outer join Tag T on PT.tag_id = T.idwhere B.id = #{id}

在我们编写的时候注意点：

不要忘记注册Mapper.xml
在初学的时候尽量不要给实体类取别名，为了不要混淆，加深理解
实体类字段要和数据库字段一致，如果不一致，那就要用result标签做映射
复杂的属性需要单独处理，是对象就使用association，是集合就使用collection来映射
javaType="" 指定的属性类型|
集合中的泛型信息，使用ofType获取
多注意复杂属性的嵌套使用

JavaType & ofType