MyBatis如何實現多表查詢（多對一、一對多）

MyBatis實現多表查詢

一、多對一查詢

數據庫的準備

創建兩張表，一張老師表，一張學生表

將老師主鍵id關聯學生外鍵tid

創建sql的語句

create table teacher( id int primary key,teacher_name varchar(30) not null)insert into teacher(id,teacher_name) values (1,’毛老師’)create table student( id int primary 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<Student> getStudent();}

2. 編寫StudentMapper.xml的查詢語句（重點）

<mapper namespace='dao.StudentMapper'><!-- 思路：1. 查詢所有學生的信息根據查詢出來的學生tid，尋找對應的老師--> <select resultMap='StudentTeacher'> select * from student </select> <resultMap type='pojo.Student'><!--復雜的屬性需要單獨處理 是對象就使用association，是集合就使用collection--><!-- select 子查詢 --><result property='name' column='student_name'/><association property='teacher' column='tid' javaType='pojo.Teacher' select='getTeacher'/> </resultMap> <select resultType='pojo.Teacher'>select * from teacher where id=#{id} </select>

測試類

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

測試結果

2、聯合查詢處理

編寫接口

//按照結果嵌套查詢public List<Student> getStudent2();

2. 編寫StudentMapper.xml的查詢語句（重點）

<!-- 按照結果嵌套處理--> <select resultMap='StudentTeacher2'> select s.id sid,s.student_name sname,t.teacher_name tname from student s,teacher t where s.tid=t.id </select> <resultMap type='pojo.Student'><result property='id' column='sid'/><result property='name' column='sname'/><association property='teacher' javaType='pojo.Teacher'> <result property='name' column='tname'/></association> </resultMap>

編寫測試類

@Test public void getStudent(){SqlSession sqlSession = Mybatisutil.getSqlSession();StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);List<Student> 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<Student> students;}

1、嵌套查詢處理 編寫接口

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

由于字段不一致，要做映射

主要TeacherMapper.xml的查詢語句（重點）

<select resultMap='TeacherStudent2'> select * from teacher where id=#{tid} </select> <resultMap type='pojo.Teacher'><result property='name' column='teacher_name'/><collection property='students' javaType='ArrayList' ofType='pojo.Student' select='getStudentByTeacherId' column='id'/> </resultMap> <select resultType='pojo.Student'>select * from student where tid=#{tid} </select>

測試類

@Test public 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 resultMap='TeacherStudent'>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} </select> <resultMap type='pojo.Teacher'><result property='id' column='tid'/><result property='name' column='tname'/><!--復雜的屬性需要單獨處理 是對象就使用association，是集合就使用collectionjavaType='' 指定的屬性類型集合中的泛型信息，使用ofType獲取--><collection property='students' ofType='pojo.Student'> <result property='id' column='sid'/> <result property='name' column='sname'/> <result property='tid' column='tid'/></collection> </resultMap>

測試類

@Test public 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，這么復雜的看的我頭疼

<!-- 非常復雜的語句 --><select resultMap='detailedBlogResultMap'> select B.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_name from Blog B left outer join Author A on B.author_id = A.id left outer join Post P on B.id = P.blog_id left outer join Comment C on P.id = C.post_id left outer join Post_Tag PT on PT.post_id = P.id left outer join Tag T on PT.tag_id = T.id where B.id = #{id}</select>

在我們編寫的時候注意點：

不要忘記注冊Mapper.xml 在初學的時候盡量不要給實體類取別名，為了不要混淆，加深理解 實體類字段要和數據庫字段一致，如果不一致，那就要用result標簽做映射 復雜的屬性需要單獨處理，是對象就使用association，是集合就使用collection來映射 javaType='' 指定的屬性類型| 集合中的泛型信息，使用ofType獲取 多注意復雜屬性的嵌套使用

JavaType & ofType

JavaType 用來指定實體類中屬性的類型 ofType 用來指定映射到List或者集合中的實體類pojo類型，泛型中的約束類型

到此這篇關于MyBatis如何實現多表查詢（多對一、一對多）的文章就介紹到這了,更多相關MyBatis多表查詢內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！