MyBatis基础四（多对一与一对多）-优快云博客

本文链接：https://blog.youkuaiyun.com/await_9/article/details/146716661

测试环境搭建

多个学生归一个班主任管。（关联：多对一）

一个班主任管理多个学生。（集合：一对多）

创建学生表与老师表。

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');

1、导入Lombok

2、编写实体类

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

@Data
public class Student {
    private int id;
    private String name;
    //学生关联老师，此处用老师作为一个对象
    private Teacher teacher;
}

3、编写接口

public interface TeacherMapper {
//根据id查询教师信息
    @Select("select * from teacher where id=#{tid}")
    Teacher getTeacher(@Param("tid") int id);
}

public interface StudentMapper {
}

4、编写接口的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.serenity.dao.TeacherMapper">

</mapper>

5、在核心配置文件中绑定注册接口或者文件。

测试：

@Test
    public void getTeacherTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher(1);
        System.out.println(teacher);
        sqlSession.close();
    }

文件夹显示：

多对一的处理

查询所有的学生信息以及对应的老师的信息。

编写接口：

编写接口对应的xml

测试：

//查询所有学生的信息以及对应老师的信息
    @Test
    public void getStudentTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

测试结果：教师一行为null

1、查询所有的学生信息（以上）

按照子查询嵌套处理

2、根据查询出来的学生的tid，寻找对应的老师（以下）

编写学生接口的xml文件：

<select id="getStudent" resultMap="StudentTeacher" >
        select * from student
    </select>
    <resultMap id="StudentTeacher" type="Student">
        <result property="id"   column="id"/>
        <result property="name"   column="name"/>
        <association property="teacher" column="tid" javaType="Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="Teacher">
        select * from teacher where id=#{tid}
    </select>

测试：

 //查询所有学生的信息以及对应老师的信息
    @Test
    public void getStudentTest(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = mapper.getStudent();
        for (Student student : studentList) {
            System.out.println(student);
        }
        sqlSession.close();
    }

按照结果嵌套处理

编写学生接口的xml文件：

<select id="getStudent2" resultMap="StudentTeacher2">
        select s.id as sid,s.name as sname,t.name as tname
        from student s,teacher t
        where s.tid=t.id
    </select>
    <resultMap id="StudentTeacher2" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <association property="teacher" javaType="Teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

测试：

一对多的处理

一个班主任管理多个学生，对于老师而言，就是一对多的关系。

1、环境搭建

2、修改实体类

编写教师接口的xml文件：查询教师的信息

<mapper namespace="com.serenity.dao.TeacherMapper">
    <select id="getTeacher" resultType="Teacher">
        select * from mybatis.teacher;
    </select>
</mapper>

测试：

 @Test
    public void test(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = mapper.getTeacher();
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
        sqlSession.close();
    }

获取指定老师管理的所有学生及学生信息（以下）

根据结果嵌套查询

编写接口：

编写教师接口的xml文件：

<select id="getTeacher2" resultMap="TeacherStudent">
        select t.id as tid,t.name as tname,s.id as sid,s.name as sname
        from student s,teacher t
        where s.tid=t.id
        and t.id=#{tid}
    </select>
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid"/>
        <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>

测试：

根据子查询嵌套处理

编写接口;

编写教师接口的xml：

<select id="getTeacher3" resultMap="TeacherStudent2">
        select * from teacher where id=#{tid}
    </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=#{tid}
    </select>

编写测试类：

@Test
    public void getTeacherTest2(){
        SqlSession sqlSession = MybatisUtils.getSqlSession();
        TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
        Teacher teacher = mapper.getTeacher3(1);
        System.out.println(teacher);
        sqlSession.close();
    }