Mybatis一对多查询

架构

对于老师而言就是一对多

环境搭建

数据库

CREATE TABLE `student`(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(20) DEFAULT NULL 
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES (1,"李老师");

CREATE TABLE `student` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
`name` VARCHAR(20) DEFAULT NULL ,
`tid` INT(4) NOT NULL,
KEY `fk_tid` (`tid`),
CONSTRAINT fk_tid FOREIGN KEY(`tid`) REFERENCES `teacher`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO student VALUES
(1,"小红",1),
(2,"小绿",1),
(3,"小灯",1),
(4,"小勿",1),
(5,"小闯",1);

实体类

Student

package pojo;

import lombok.Data;

@Data
public class Student {
    private int id;
    private String name;
    private int tid;
}

Teacher

package pojo;

import lombok.Data;

import java.util.List;

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

    private List<Student> Students;
}

接口

StudentMapper

package dao;

import pojo.Student;

import java.util.List;

public interface StudentMapper {

}

TeacherMapper

package dao;

import pojo.Teacher;

public interface TeacherMapper {
    Teacher getTeacher(int id);

    Teacher getTeacher2(int id);
}

Mapper配置文件
StudentMapper.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="dao.StudentMapper">

</mapper>

TeacherMapper.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="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 tid=#{id};
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result column="tid" property="id" />
        <result column="tname" property="name" />
        <collection property="Students" ofType="Student">
            <result property="id" column="sid" />
            <result property="name" column="sname" />
            <result property="tid" column="tid" />
        </collection>
    </resultMap>

    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id=#{id};
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="Students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id" />
    </resultMap>
    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid=#{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="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 tid=#{id};
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result column="tid" property="id" />
        <result column="tname" property="name" />
        <collection property="Students" ofType="Student">
            <result property="id" column="sid" />
            <result property="name" column="sname" />
            <result property="tid" column="tid" />
        </collection>
    </resultMap>
</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="dao.TeacherMapper">
    <select id="getTeacher2" resultMap="TeacherStudent2">
        select * from teacher where id=#{id};
    </select>
    <resultMap id="TeacherStudent2" type="Teacher">
        <collection property="Students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id" />
    </resultMap>
    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid=#{id};
    </select>
</mapper>