【 MyBatis 】两种方式实现多对一关联查询：子查询、联表查询

什么是多对一？举个栗子

假如现在数据库中有两个表，一个是学生表student，一个是老师表teacher，现在的对应关系就是多个学生对应一个老师

当我们在查询学生信息的时候，根据学生和老师的字段的关联，同时查出每个学生对应的老师的信息。

具体代码实现

两个实体类

学生类和老师类

package com.robot.pojo;

public class Student {
    private int id;
    private String name;
    private Teacher teacher;

    public Student() {
    }

    public Student(int id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                '}';
    }
}

package com.robot.pojo;

public class Teacher {
    private int id;
    private String name;

    public Teacher() {
    }

    public Teacher(int id, String name) {
        this.id = id;
        this.name = name;
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

 两个接口

package com.robot.dao;

import com.robot.pojo.Student;

import java.util.List;

public interface StudentMapper {

    public List<Student> getStudent();
}

package com.robot.dao;

import com.robot.pojo.Teacher;
import org.apache.ibatis.annotations.Param;

public interface TeacherMapper {

    Teacher getTeacher(@Param("tid")int id);
}

编写xml，实现查询

第一种方式：子查询

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.robot.dao.StudentMapper">

    <select id="getStudent" resultMap="StudentToTeacher">
        select * from student
    </select>

    <resultMap id="StudentToTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--        复杂的属性需要单独处理，对象：association 集合：collection-->
        <association property="teacher" column="tid" javaType="teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select * from mybatis.teacher where id=#{id}
    </select>
</mapper>

第二种方式：联表查询（一般常用这种）

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.robot.dao.StudentMapper">

    <select id="getStudent" resultMap="StudentTeacher">
        select s.id sid, s.name sname, t.id tid, t.name tname from student s, teacher t where s.tid = t.id
    </select>

    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="teacher">
            <result property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>

</mapper>

修改核心配置文件

修改核心配置文件中的mappers映射，映射到对应的两个xml文件

<mappers>
    <mapper resource="com/robot/dao/StudentMapper.xml"/>
</mappers>

测试

@Test
public void getStudentTest() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
    List<Student> studentList = studentMapper.getStudent();
    for (Student student : studentList) {
        System.out.println(student);
    }
    sqlSession.close();
}

预期的结果就是通过查询学生的信息，从而将每个学生对应的老师的信息也查询出来

 以上都是在搭建好MyBatis的运行环境后，才能测试成功

总结

联表查询是根据结果再进行查询，比较容易理解，而且主要使用SQL语句，可以先在命令行中进行调试，成功后再编写xml

子查询更不容易理解，而且如果出错的话，也不容易调错排查

所以还是使用联表查询更容易些