该文章是介绍《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、嵌套查询处理
1.编写接口
public interface StudentMapper { //查问所有学生的信息以及对应老师的信息 public List<Student> getStudent();}- 编写StudentMapper.xml的查问语句（重点）
<mapper namespace="dao.StudentMapper"><!-- 思路： 1. 查问所有学生的信息 依据查问进去的学生tid，寻找对应的老师--> <select id="getStudent" resultMap="StudentTeacher"> select * from student </select> <resultMap id="StudentTeacher" type="pojo.Student"><!-- 简单的属性须要独自解决 是对象就应用association，是汇合就应用collection--><!-- select 子查问 --> <result property="name" column="student_name"/> <association property="teacher" column="tid" javaType="pojo.Teacher" select="getTeacher"/> </resultMap> <select id="getTeacher" resultType="pojo.Teacher"> select * from teacher where id=#{id} </select>3.测试类
@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、联结查询处理
1.编写接口
//依照后果嵌套查问public List<Student> getStudent2();- 编写StudentMapper.xml的查问语句（重点）
<!-- 依照后果嵌套解决--> <select id="getStudent2" 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 id="StudentTeacher2" 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>3.编写测试类
@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、嵌套查询处理
1.编写接口
Teacher getTeacher2(@Param("tid") int id);因为字段不统一，要做映射
2.次要TeacherMapper.xml的查问语句（重点）
<select id="getTeacher2" resultMap="TeacherStudent2"> select * from teacher where id=#{tid} </select> <resultMap id="TeacherStudent2" type="pojo.Teacher"> <result property="name" column="teacher_name"/> <collection property="students" javaType="ArrayList" ofType="pojo.Student" select="getStudentByTeacherId" column="id"/> </resultMap> <select id="getStudentByTeacherId" resultType="pojo.Student"> select * from student where tid=#{tid} </select>3.测试类
@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、联结查询处理
1.编写接口
//获取指定老师下的所有学生及老师的信息 Teacher getTeacher(@Param("tid") int id);因为字段不统一，要做映射
2.次要TeacherMapper.xml的查问语句（重点）
<!-- 按后果嵌套查问--> <select id="getTeacher" resultMap="TeacherStudent"> select s.id sid,s.student_name sname,t.teacher_name tname,t.id tid from student s,teacher t where s.tid=t.id and t.id=#{tid} </select> <resultMap id="TeacherStudent" type="pojo.Teacher"> <result property="id" column="tid"/> <result property="name" column="tname"/> <!-- 简单的属性须要独自解决 是对象就应用association，是汇合就应用collection javaType="" 指定的属性类型 汇合中的泛型信息，应用ofType获取--> <collection property="students" ofType="pojo.Student"> <result property="id" column="sid"/> <result property="name" column="sname"/> <result property="tid" column="tid"/> </collection> </resultMap>3.测试类
@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 id="selectBlogDetails" 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实现多表查问》的分享
- 也欢送大家交换探讨，该文章若有不正确的中央，心愿大家多多包涵。
- 你们的反对就是我最大的能源，如果对大家有帮忙给个赞哦~~~