Mybatis之多表查询

多对一

package com.lei.pojo;

public class Student {
    private int id;
    private String name;
    private 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.lei.pojo;

public class Teacher {
    private int id;
    private String 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.lei.dao;

import com.lei.pojo.Student;

import java.util.List;

public interface StudentMapper {

    public List<Student> getStudent();
    public List<Student> getStudent2();
}

以下是两种查询方法：

<?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.lei.dao.StudentMapper">

    <select id="getStudent" resultMap="StudentTeacher">
        select * from mybatis.student;
    </select>
    <resultMap id="StudentTeacher" type="com.lei.pojo.Student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
<!--        复杂属性，需要单独处理-->
<!--        对象：association-->
<!--        集合：collection-->
        <association property="teacher" column="tid" javaType="com.lei.pojo.Teacher" select="getTeacher"/>

    </resultMap>
    <select id="getTeacher" resultType="com.lei.pojo.Teacher">
        select * from teacher where id = #{id}
    </select>

<!--    ===========================================================================================-->

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

    <resultMap id="StudentTeacher2" type="com.lei.pojo.Student">
         <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="com.lei.pojo.Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

</mapper>

一对多

package com.lei.pojo;

import java.util.List;

public class Teacher {
    private int id;
    private String name;
    private List<Student> students;

    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 List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

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

package com.lei.pojo;

public class Student {
    private int id;
    private String name;
    private int tid;

    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 int getTid() {
        return tid;
    }

    public void setTid(int tid) {
        this.tid = tid;
    }

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

package com.lei.dao;

import com.lei.pojo.Teacher;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface TeacherMapper {

    //List<Teacher> getTeacher();
    //获取指定老师下所有学生及老师信息
    Teacher getTeacher(int id);
    Teacher getTeacher2(int id);
}

<?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.lei.dao.TeacherMapper">
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.id sid,s.name sname,t.name tname,t.id tid from student s,teacher t where s.tid=t.id and t.id=#{id};
    </select>
    <resultMap id="TeacherStudent" type="com.lei.pojo.Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname"/>
<!--        集合中的泛型参数，使用ofType获取-->
        <collection property="students" ofType="com.lei.pojo.Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="id"/>
        </collection>
    </resultMap>

<!--     ===========================================================================================-->
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id=#{id}
    </select>
    <resultMap id="TeacherStudent2" type="com.lei.pojo.Teacher">
        <collection property="students" javaType="ArrayList" ofType="com.lei.pojo.Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>
    <select id="getStudentByTeacherId" resultType="com.lei.pojo.Student">
        select * from student where tid = #{id}
    </select>

</mapper>