Mybatis进阶1-CSDN博客

Mybatis入门-CSDN博客

Mybatis入门2-CSDN博客

我们接下来要学习Mybatis的高级查询

我们先在数据库中准备我们需要的数据表

teacher表

课程表：与教师表是一对多的关系，所以有一个外键字段

 

学生表

 

由于学生表和课程表是多对多的关系，所以我们创建一个虚拟表

coures_student表

 在Maven工厂的POJO包下创建对应的类，外键字段的成员变量可以不写，因为没啥用

public class Teacher {private int id ;private String teacherName;public Teacher() {}public Teacher(int id, String teacherName) {this.id = id;this.teacherName = teacherName;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getTeacherName() {return teacherName;}public void setTeacherName(String teacherName) {this.teacherName = teacherName;}@Overridepublic String toString() {return "Teacher{" +"id=" + id +", teacherName='" + teacherName + '\'' +'}';}}
public class Student {private int id;private String studentName;private int age;public Student() {}public Student(int id, String studentName, int age) {this.id = id;this.studentName = studentName;this.age = age;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getStudentName() {return studentName;}public void setStudentName(String studentName) {this.studentName = studentName;}public int getAge() {return age;}public void setAge(int age) {this.age = age;}@Overridepublic String toString() {return "Student{" +"id=" + id +", studentName='" + studentName + '\'' +", age=" + age +'}';}
}
public class Course {private int id;private String courseName;public Course() {}public Course(int id, String courseName) {this.id = id;this.courseName = courseName;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getCourseName() {return courseName;}public void setCourseName(String courseName) {this.courseName = courseName;}@Overridepublic String toString() {return "Course{" +"id=" + id +", courseName='" + courseName + '\'' +'}';}
}

Mybatis多表查询的套路

1.基于需求写sql语句

2.基于sql语句的查询结果，分析类与类之间的关联（建立实体类和实体类的关联）

3.在映射文件中，基于sql查询结果，配置映射关联

我们先来练习一对一查询，通过一个课程的名字来查询叫这门课程老师的信息

接口的方法：

public interface CourseMapper {/*** 通过课程名字找课程和老师的信息* @param courseName 课程名* @return 返回Coures类对象*/public Course findCourseByName(String courseName);
}

第一步：

编写sql语句

select course.id as coures_id, course.name, course.course_teacher_id, teacher.id as teacher_id, teacher.name
from course inner join teacher on teacher.id=course.course_teacher_id where course.name='java';

第二步： 基于sql语句的查询结果，分析类与类之间的关联（建立实体类和实体类的关联）

1对1查询结果：在Course类中添加新属性：Teacher对象

public class Course {private int id;private String courseName;private Teacher teacher;//添加一个老师对象public Course() {}public Course(int id, String courseName) {this.id = id;this.courseName = courseName;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getCourseName() {return courseName;}public void setCourseName(String courseName) {this.courseName = courseName;}public Teacher getTeacher() {return teacher;}public void setTeacher(Teacher teacher) {this.teacher = teacher;}@Overridepublic String toString() {return "Course{" +"id=" + id +", courseName='" + courseName + '\'' +'}';}
}

第三步：在映射文件中，基于sql查询结果，配置映射关联

<mapper namespace="com.hhh.dao.CourseMapper"><!--resultMap标签：解决查询结果字段名与实体类属性名不一致的问题；解决多表查询关联映射--><resultMap id="courseMap" type="com.hhh.pojo.Course"><!--配置：查询结果和Course类的映射关联--><id column="coures_id" property="id"/><result column="course_name" property="courseName"/><!--配置：1对1查询配置：查询结果与Teacher类的关联映射--><association property="teacher" javaType="com.hhh.pojo.Teacher" autoMapping="true"><id column="teacher_id" property="id"/><result column="teacher_name" property="teacherName"/></association></resultMap><select id="findCourseByName" resultMap="courseMap">selectcourse.id as coures_id, course.name as course_name,
//取别名来区分两张表的字段名，不然会报错teacher.id as teacher_id, teacher.name as teacher_namefrom course inner join teacheron teacher.id=course.course_teacher_idwhere course.name=#{courseName};</select>
</mapper>

测试：
 

public class CourseMapperTest {@Testpublic void testFindCourseByName(){SqlSession sqlSession = MybatisUtil.openSession();CourseMapper mapper = sqlSession.getMapper(CourseMapper.class);Course java = mapper.findCourseByName("java");System.out.println("课程信息为"+java);Teacher teacher = java.getTeacher();System.out.println("教师信息为"+teacher);}
}

接下来练习一对多查询，根据教师id查询其信息和他教课程的信息

第一步： 编写sql语句

select teacher.id, teacher.name, course.id, course.name 
from teacher inner join course 
on teacher.id = course.course_teacher_id 
where teacher.id=1;

需要注意的是这个两行数据是一个Teacher对象

 第二步：基于sql语句的查询结果，分析类与类之间的关联（建立实体类和实体类的关联）

一对多查询结果：在Teacher类添加新属性：List<Course>集合

public class Teacher {private int id ;private String teacherName;List<Course> couerses;public List<Course> getList() {return couerses;}public void setList(List<Course> list) {this.couerses = list;}public Teacher() {}public Teacher(int id, String teacherName) {this.id = id;this.teacherName = teacherName;}public int getId() {return id;}public void setId(int id) {this.id = id;}public String getTeacherName() {return teacherName;}public void setTeacherName(String teacherName) {this.teacherName = teacherName;}@Overridepublic String toString() {return "Teacher{" +"id=" + id +", teacherName='" + teacherName + '\'' +'}';}}

第三步：在映射文件中，基于sql查询结果，配置映射关联

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapperPUBLIC "-//mybatis.org//DTD Mapper 3.0//EN""http://mybatis.org/dtd/mybatis-3-mapper.dtd"><mapper namespace="com.hhh.dao.TeacherMapper"><resultMap id="teacherMap" type="com.hhh.pojo.Teacher" autoMapping="true"><!--配置：查询结果和Teacher类的映射--><id column="teacher_id" property="id"/><result column="teacher_name" property="teacherName"/><!--配置：查询结果和Course类的映射-->      ofType是List元素的类型<collection property="couerses" javaType="java.util.List" ofType="com.hhh.pojo.Course"><id column="course_id" property="id"/><result column="couerse_name" property="courseName"/></collection></resultMap><select id="findTeacherById" resultMap="teacherMap">  取别名是为了区别，不然会报错select teacher.id as teacher_id, teacher.name as teacher_name,course.id as course_id, course.name as couerse_namefrom teacher inner join course on teacher.id = course.course_teacher_idwhere teacher.id=#{id};</select></mapper>

测试：

public class TeacherMapperTest {@Testpublic void testFindTeacherById(){SqlSession sqlSession = MybatisUtil.openSession();TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);Teacher teacherById = mapper.findTeacherById(1);System.out.println("教师的信息为"+teacherById);List<Course> list = teacherById.getList();for (Course course : list) {System.out.println("所教的课程信息为"+course);}}
{

结果：