MyBatis_查询中的多对一与一对多

Mudrock__

已于 2022-05-02 17:10:19 修改

阅读量227

点赞数

分类专栏： MyBatis 文章标签： database

于 2022-05-02 17:08:56 首次发布

本文链接：https://blog.csdn.net/Mudrock__/article/details/124483080

版权

MyBatis 专栏收录该内容

13 篇文章 0 订阅

订阅专栏

本文详细介绍了如何在Java中使用MyBatis进行教师表和学生表的多对一关系建模，包括Teacher和Student类的设计，以及StudentMapper接口和XML文件的配置。展示了两种查询方法，适用于不同场景的SQL语句和结果映射。

摘要由CSDN通过智能技术生成

建表

教师表

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, '吴老师');
INSERT INTO teacher(`id`, `name`) VALUES (2, '风老师');

学生表

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', '小李', '2');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '2');

注：注意先创建教师表再创建学生表，因为教师表的id字段为学生表的tid字段的外键。此外建议先建表再插入，因为mysql处理速度很快，可能尚未建完表就开始进行插入数据，导致建表失败

建成后的表如图

多对一

Student类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    int ID;
    String Name;
    //学生与老师进行关联
    Teacher teacher;
}

Teacher类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    int ID;
    String Name;
}

StudentMapper类

public interface StudentMapper {
    List<Student> getStudent01();
    List<Student> getStudent02();
}

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="cn.alan.Mapper.StudentMapper">
    <!--方式一-->
    <!--1.查询所有的学生-->
    <!--2.根据学生的tid查询对应的老师-->
    <select id="getStudent01" resultMap="ST01">
        select *
        from mybatis.student;
    </select>
    <!--若无属性名与字段名不一致的情况 则无需在map中一一指明属性对应字段 与字段同名的属性将被自动映射并赋值-->
    <resultMap id="ST01" type="cn.alan.POJO.Student">
        <!--teacher是一个复杂的属性 需要单独处理-->
        <!--association 对象 collection 集合-->
        <!--teacher为对象 故使用association标签而不是collection标签-->
        <!--将student表中的tid字段映射给teacher属性 为getTeacher查询提供参照-->
        <association property="teacher" column="tid" javaType="cn.alan.POJO.Teacher" select="getTeacher"/>
    </resultMap>
    <select id="getTeacher" resultType="cn.alan.POJO.Teacher">
        select *
        from mybatis.teacher
        where id = #{tid};# 实际上{}内所填的值是无所谓的 mybatis大多数情况下都能正确匹配到
    </select>

    <!--方式二-->
    <!--需在map中一一指明属性对应字段-->
    <!--查询涉及了多个表 具有重名字段 故需要取别名以区分-->
    <select id="getStudent02" resultMap="ST02">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from mybatis.student s,
             mybatis.teacher t
        where s.tid = t.id;
    </select>
    <resultMap id="ST02" type="cn.alan.POJO.Student">
        <result property="ID" column="sid"/>
        <result property="Name" column="sname"/>
        <association property="teacher" javaType="cn.alan.POJO.Teacher">
            <result property="ID" column="tid"/>
            <result property="Name" column="tname"/>
        </association>
    </resultMap>
</mapper>

编写测试类

    @Test
    public void getStudent01(){
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);

        List<Student> studentList = studentMapper.getStudent01();
        for (Student student : studentList) {
            System.out.println(student);
        }

        sqlSession.close();
    }

运行效果

一对多

Student类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Student {
    int ID;
    String Name;
}

Teacher类

@Data
@AllArgsConstructor
@NoArgsConstructor
public class Teacher {
    int ID;
    String Name;
    List<Student> students;
}

TeacherMapper类

public interface TeacherMapper {
    List<Teacher> getStudentByTeacher01(int id);
    List<Teacher> getStudentByTeacher02(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="cn.alan.Mapper.TeacherMapper">
    <!--方式一-->
    <select id="getStudentByTeacher01" resultMap="ST01" parameterType="int">
        select *
        from mybatis.teacher
        where id = #{id};
    </select>

    <resultMap id="ST01" type="cn.alan.POJO.Teacher">
        <!--若无此标签 查询结果中Teacher的ID等于0-->
        <!--因为id字段在collection标签中被映射给了students属性 ID属性默认未获得映射-->
        <result property="ID" column="id"/>
        <!--students为List集合 故使用collection标签而不是association标签-->
        <!--属性students返回的是List中的泛型Student 故使用ofType=Student而不是javaType=ArrayList-->
        <collection property="students" column="id" ofType="cn.alan.POJO.Student" select="getST"/>
    </resultMap>

    <select id="getST" resultType="cn.alan.POJO.Student">
        select *
        from mybatis.student
        where tid = #{id};
    </select>

    <!--方式二-->
    <select id="getStudentByTeacher02" resultMap="ST02" parameterType="int">
        select s.id sid, s.name sname, t.id tid, t.name tname
        from mybatis.student s,
             mybatis.teacher t
        where s.tid = t.id
          and t.id = #{id};
    </select>

    <resultMap id="ST02" type="cn.alan.POJO.Teacher">
        <result property="ID" column="tid"/>
        <result property="Name" column="tname"/>
        <collection property="students" ofType="cn.alan.POJO.Student">
            <result property="ID" column="sid"/>
            <result property="Name" column="sname"/>
        </collection>
    </resultMap>
</mapper>

编写测试类

    @Test
    public void getStudentByTeacher01() {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);

        List<Teacher> teacherList = teacherMapper.getStudentByTeacher01(1);
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }

        sqlSession.close();
    }

    @Test
    public void getStudentByTeacher02() {
        SqlSession sqlSession = mybatisUtils.getSqlSession();
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);

        List<Teacher> teacherList = teacherMapper.getStudentByTeacher02(2);
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }

        sqlSession.close();
    }

运行结果

Mudrock__

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyBatis_查询中的多对一与一对多

建表教师表CREATE TABLE `teacher` (`id` INT(10) NOT NULL,`name` VARCHAR(30) DEFAULT NULL,PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8INSERT INTO teacher(`id`, `name`) VALUES (1, '吴老师');INSERT INTO teacher(`id`, `name`) VALUES (2, '风老师');学生
复制链接

扫一扫

专栏目录