MyBatis05：一对多和多对一处理

MyBatis05：一对多和多对一处理

1、多对一

数据库文件：多个学生对应一个老师

USE mybatis;

CREATE TABLE `teacher`(
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO teacher(`id`,`name`) VALUES('1','秦老师');

CREATE TABLE `student`(
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY(`id`),
KEY `fktid`(`tid`),
CONSTRAINT `fktid` FOREIGN KEY(`tid`) REFERENCES `teacher`(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');

新建maven项目

导入jar包

实体类

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

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

Mapper接口

public interface StudentMapper {
    //获取所有学生及对应老师的信息
    public List<Student> getStudents();
}

public interface TeacherMapper {
    
}

按查询嵌套处理：子查询

1. 获取所有学生的信息
2. 根据获取的学生信息的老师ID->获取该老师的信息
   1. 学生的结果集中包含老师，做一个结果集映射，类型Student
   2. 使用association处理关联查询

StudentMapper.xml

<?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.wgg.mapper.StudentMapper">

    <select id="getStudents" resultMap="StudentTeacher">
        select  * from student
    </select>

    <resultMap id="StudentTeacher" type="Student">
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>

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

配置文件中注册mapper

<mappers>
    <mapper resource="com/wgg/mapper/StudentMapper.xml"/>
    <mapper resource="com/wgg/mapper/TeacherMapper.xml"/>
</mappers>

测试

@Test
public void testGetStudent(){
    SqlSession session = MybatisUtils.getSession();
    StudentMapper mapper = session.getMapper(StudentMapper.class);
    List<Student> students = mapper.getStudents();

    for (Student student : students) {
        System.out.print(student.getId()+"\t"+student.getName()+"\t"+student.getTeacher().getName());
        System.out.println();
    }
}

按结果嵌套处理：联表查询

1. 查询出学生的所有字段
2. 再进行关联查询

StudentMapper.xml

<?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.wgg.mapper.StudentMapper">

   <select id="getStudents2" 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="Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>

        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

</mapper>

注册Mapper

测试

@Test
public void testGetStudents2(){
    SqlSession session = MybatisUtils.getSession();
    StudentMapper mapper = session.getMapper(StudentMapper.class);
    List<Student> students = mapper.getStudents2();

    for (Student student : students) {
        System.out.print(student.getId()+"\t"+student.getName()+"\t"+student.getTeacher().getName());
        System.out.println();
    }
}

2、一对多

一个老师拥有多个学生（集合）

实体类

@Data
public class Student {
    private int id;
    private String name;
    private int tid;    //对应老师的id
}

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

TeacherMapper.xml

public interface TeacherMapper {
    //根据id获取老师，及老师下的所有学生
    public Teacher getTeacher(int id);
}

按结果嵌套处理：联表查询

TeacherMapper.xml

1. 查询老师的所有字段
2. 对字段students使用collection进行结果集映射

<?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.wgg.mapper.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="Teacher">
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>
</mapper>

注册TeacherMapper

测试

 @Test
    public void getTeacher(){
        SqlSession session = MybatisUtils.getSession();
        TeacherMapper mapper = session.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);

        System.out.println(teacher.getName());
        System.out.println(teacher.getStudents());
    }

按查询嵌套处理：子查询

TeacherMapper接口

public Teacher getTeacher2(int id);

TeacherMapper.xml

<?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.wgg.mapper.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" column="id"
                    select="getStudentByTeacherId"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="Student">
        select * from student where tid=#{id}
    </select>
</mapper>

测试

@Test
public void getTeacher2(){
    SqlSession session = MybatisUtils.getSession();
    TeacherMapper mapper = session.getMapper(TeacherMapper.class);
    Teacher teacher2 = mapper.getTeacher2(1);
    System.out.println(teacher2.getName());
    System.out.println(teacher2.getStudents());
}

小结

关联：association 一对一、多对一

集合：collection 一对多