Mybatis进阶2

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

    @Override
    public 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;
    }

    @Override
    public 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;
    }

    @Override
    public 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;
    }

   @Override
    public 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">
        select
            course.id as coures_id, course.name as course_name,
//取别名来区分两张表的字段名，不然会报错
            teacher.id as teacher_id, teacher.name as teacher_name

            from course inner join teacher
            on teacher.id=course.course_teacher_id
            where course.name=#{courseName};
    </select>
</mapper>

测试：
 

public class CourseMapperTest {
    @Test
    public 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;
    }

    @Override
    public String toString() {
        return "Teacher{" +
                "id=" + id +
                ", teacherName='" + teacherName + '\'' +
                '}';
    }


}

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

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//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_name
        from teacher inner join course on teacher.id = course.course_teacher_id
        where teacher.id=#{id};
    </select>

</mapper>

测试：

public class TeacherMapperTest {
    @Test
    public 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);
        }
    }
{

结果：