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

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 getStudent();}

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

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

测试类

@Test public 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 tname from student s,teacher t where s.tid=t.id

编写测试类

@Test public 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&http://lt;Student> students;}

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

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

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

主要TeacherMapper.xml的查询语句（重点）

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

测试类

@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 s.id sid,s.student_name sname,t.teacher_name tname,t.id tihttp://d from student s,teacher t where s.tid=t.id and t.id=#{tid}

测试类

@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 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}

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

不要忘记注册Mapper.xml

在初学的时候尽量不要给实体类取别名，为了不要混淆，加深理解

实体类字段要和数据库字段一致，如果不一致，那就要用result标签做映射

复杂的属性需要单独处理，是对象就使用association，是集合就使用collection来映射

javaType="" 指定的属性类型

集合中的泛型信息，使用ofType获取

​ 多注意复杂属性的嵌套使用

JavaType & ofType

JavaType 用来指定实体类中属性的类型ofType 用来指定映射到List或者集合中的实体类pojo类型，泛型中的约束类型

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。