MyBatis實現(xiàn)多表查詢

一、多對一查詢

數(shù)據(jù)庫的準(zhǔn)備

創(chuàng)建兩張表，一張老師表，一張學(xué)生表

將老師主鍵id關(guān)聯(lián)學(xué)生外鍵tid

創(chuàng)建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)//建立主外鍵關(guān)聯(lián)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)

項目結(jié)構(gòu)

使用Lombok插件，創(chuàng)建實體類。

（提高整潔度，主要想toulan）

@Datapublic class Student { private int id; private String name; //學(xué)生需要關(guān)聯(lián)一個老師 private Teacher teacher;}

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

1、嵌套查詢處理

編寫接口

public interface StudentMapper { //查詢所有學(xué)生的信息以及對應(yīng)老師的信息 public List<Student> getStudent();}

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

<mapper namespace='dao.StudentMapper'><!-- 思路：1. 查詢所有學(xué)生的信息根據(jù)查詢出來的學(xué)生tid，尋找對應(yīng)的老師--> <select resultMap='StudentTeacher'> select * from student </select> <resultMap type='pojo.Student'><!--復(fù)雜的屬性需要單獨處理 是對象就使用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(); }

測試結(jié)果

2、聯(lián)合查詢處理

編寫接口

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

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

<!-- 按照結(jié)果嵌套處理--> <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(); }

測試結(jié)果

二、一對多查詢

更改實體類

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

@Datapublic class Teacher { private int id; private String name; //一個老師擁有多個學(xué)生 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(); }

測試結(jié)果：

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、聯(lián)合查詢處理

編寫接口

//獲取指定老師下的所有學(xué)生及老師的信息 Teacher getTeacher(@Param('tid') int id);

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

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

<!-- 按結(jié)果嵌套查詢--> <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'/><!--復(fù)雜的屬性需要單獨處理 是對象就使用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(); }

測試結(jié)果：

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)])總結(jié)：

本章就使用了簡單的兩張表聯(lián)合查詢，介紹簡單的使用，更復(fù)雜的多表聯(lián)合主要在編寫sql的時候難度大點，或者是嵌套查詢要更嚴(yán)謹(jǐn)點

官方文檔也給了詳細(xì)的非常復(fù)雜的多表查詢?nèi)缦拢?mybatis，這么復(fù)雜的看的我頭疼

<!-- 非常復(fù)雜的語句 --><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 在初學(xué)的時候盡量不要給實體類取別名，為了不要混淆，加深理解 實體類字段要和數(shù)據(jù)庫字段一致，如果不一致，那就要用result標(biāo)簽做映射 復(fù)雜的屬性需要單獨處理，是對象就使用association，是集合就使用collection來映射 javaType='' 指定的屬性類型| 集合中的泛型信息，使用ofType獲取 多注意復(fù)雜屬性的嵌套使用

JavaType & ofType

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

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