• 日期: 2024-10-27
  • 标签: 常规

MyBatis 关联查询实现多表数据关联 - 老师、宿舍、学生、班级和签到表

本文将介绍如何使用 MyBatis 进行关联查询，以实现多表数据关联。例如，我们有老师表、宿舍表、学生表、班级表和签到表，并希望通过 MyBatis 查询获取所有老师及其所带班级的学生信息，包括学生所属宿舍和签到记录。

1. 定义实体类

首先需要定义与表对应的实体类，包括老师表 (Teacher)、宿舍表 (Dormitory)、学生表 (Student)、班级表 (Class) 和签到表 (Sign)。注意实体类的属性名称要与表字段名称一致。

public class Teacher {
    private int id;
    private String name;
    private int age;
    private List<Class> classes;
    // getter 和 setter 方法省略
}

public class Dormitory {
    private int id;
    private String name;
    private List<Student> students;
    // getter 和 setter 方法省略
}

public class Student {
    private int id;
    private String name;
    private int age;
    private Dormitory dormitory;
    private List<Sign> signs;
    // getter 和 setter 方法省略
}

public class Class {
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> students;
    // getter 和 setter 方法省略
}

public class Sign {
    private int id;
    private Date signTime;
    private Student student;
    // getter 和 setter 方法省略
}

2. 定义 Mapper 接口

接下来需要定义 Mapper 接口，用于定义关联查询的方法。在 Mapper 接口中使用 @Results 注解来定义查询结果集的映射关系。

public interface MyMapper {
    @Select("select * from teacher t left join class c on t.id=c.teacher_id")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "age", column = "age"),
            @Result(property = "classes", column = "id", javaType = List.class,
                    many = @Many(select = "com.example.mapper.MyMapper.getClassesByTeacherId"))
    })
    List<Teacher> getTeachers();

    @Select("select * from class where teacher_id=#{teacherId}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "teacher", column = "teacher_id", javaType = Teacher.class,
                    one = @One(select = "com.example.mapper.MyMapper.getTeacherById")),
            @Result(property = "students", column = "id", javaType = List.class,
                    many = @Many(select = "com.example.mapper.MyMapper.getStudentsByClassId"))
    })
    List<Class> getClassesByTeacherId(int teacherId);

    @Select("select * from teacher where id=#{id}")
    Teacher getTeacherById(int id);

    @Select("select * from student where class_id=#{classId}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "name", column = "name"),
            @Result(property = "age", column = "age"),
            @Result(property = "dormitory", column = "dormitory_id", javaType = Dormitory.class,
                    one = @One(select = "com.example.mapper.MyMapper.getDormitoryById")),
            @Result(property = "signs", column = "id", javaType = List.class,
                    many = @Many(select = "com.example.mapper.MyMapper.getSignsByStudentId"))
    })
    List<Student> getStudentsByClassId(int classId);

    @Select("select * from dormitory where id=#{id}")
    Dormitory getDormitoryById(int id);

    @Select("select * from sign where student_id=#{studentId}")
    @Results({
            @Result(property = "id", column = "id"),
            @Result(property = "signTime", column = "sign_time"),
            @Result(property = "student", column = "student_id", javaType = Student.class,
                    one = @One(select = "com.example.mapper.MyMapper.getStudentById"))
    })
    List<Sign> getSignsByStudentId(int studentId);

    @Select("select * from student where id=#{id}")
    Student getStudentById(int id);
}

3. 编写 XML 映射文件

在 XML 映射文件中使用 <association> 和 <collection> 标签来定义关联关系，其中 <association> 标签用于关联单个对象，<collection> 标签用于关联多个对象。

<mapper namespace="com.example.mapper.MyMapper">
    <resultMap id="teacherResultMap" type="com.example.entity.Teacher">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <collection property="classes" ofType="com.example.entity.Class">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <association property="teacher" javaType="com.example.entity.Teacher">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
                <result column="age" property="age"/>
            </association>
            <collection property="students" ofType="com.example.entity.Student">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
                <result column="age" property="age"/>
                <association property="dormitory" javaType="com.example.entity.Dormitory">
                    <id column="id" property="id"/>
                    <result column="name" property="name"/>
                </association>
                <collection property="signs" ofType="com.example.entity.Sign">
                    <id column="id" property="id"/>
                    <result column="sign_time" property="signTime"/>
                    <association property="student" javaType="com.example.entity.Student">
                        <id column="id" property="id"/>
                        <result column="name" property="name"/>
                        <result column="age" property="age"/>
                    </association>
                </collection>
            </collection>
        </collection>
    </resultMap>

    <resultMap id="classResultMap" type="com.example.entity.Class">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <association property="teacher" javaType="com.example.entity.Teacher">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </association>
        <collection property="students" ofType="com.example.entity.Student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
            <association property="dormitory" javaType="com.example.entity.Dormitory">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
            </association>
            <collection property="signs" ofType="com.example.entity.Sign">
                <id column="id" property="id"/>
                <result column="sign_time" property="signTime"/>
                <association property="student" javaType="com.example.entity.Student">
                    <id column="id" property="id"/>
                    <result column="name" property="name"/>
                    <result column="age" property="age"/>
                </association>
            </collection>
        </collection>
    </resultMap>

    <resultMap id="studentResultMap" type="com.example.entity.Student">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <association property="dormitory" javaType="com.example.entity.Dormitory">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
        </association>
        <collection property="signs" ofType="com.example.entity.Sign">
            <id column="id" property="id"/>
            <result column="sign_time" property="signTime"/>
            <association property="student" javaType="com.example.entity.Student">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
                <result column="age" property="age"/>
            </association>
        </collection>
    </resultMap>

    <resultMap id="signResultMap" type="com.example.entity.Sign">
        <id column="id" property="id"/>
        <result column="sign_time" property="signTime"/>
        <association property="student" javaType="com.example.entity.Student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="age" property="age"/>
        </association>
    </resultMap>

    <select id="getTeachers" resultMap="teacherResultMap">
        select * from teacher t left join class c on t.id=c.teacher_id
    </select>

    <select id="getClassesByTeacherId" resultMap="classResultMap">
        select * from class where teacher_id=#{teacherId}
    </select>

    <select id="getTeacherById" resultType="com.example.entity.Teacher">
        select * from teacher where id=#{id}
    </select>

    <select id="getStudentsByClassId" resultMap="studentResultMap">
        select * from student where class_id=#{classId}
    </select>

    <select id="getDormitoryById" resultType="com.example.entity.Dormitory">
        select * from dormitory where id=#{id}
    </select>

    <select id="getSignsByStudentId" resultMap="signResultMap">
        select * from sign where student_id=#{studentId}
    </select>

    <select id="getStudentById" resultType="com.example.entity.Student">
        select * from student where id=#{id}
    </select>
</mapper>

4. 执行查询操作

在执行查询操作时，需要通过 SqlSession 获取 Mapper 接口的实例，然后调用 Mapper 接口中定义的方法进行查询操作。

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
SqlSession sqlSession = sqlSessionFactory.openSession();
MyMapper myMapper = sqlSession.getMapper(MyMapper.class);
List<Teacher> teachers = myMapper.getTeachers();
for (Teacher teacher : teachers) {
    System.out.println("老师姓名：" + teacher.getName());
    List<Class> classes = teacher.getClasses();
    for (Class clazz : classes) {
        System.out.println("班级名称：" + clazz.getName());
        List<Student> students = clazz.getStudents();
        for (Student student : students) {
            System.out.println("学生姓名：" + student.getName());
            Dormitory dormitory = student.getDormitory();
            System.out.println("宿舍名称：" + dormitory.getName());
            List<Sign> signs = student.getSigns();
            for (Sign sign : signs) {
                System.out.println("签到时间：" + sign.getSignTime());
            }
        }
    }
}
sqlSession.close();

通过以上步骤，我们成功使用 MyBatis 实现了一个关联查询，获取了所有老师及其所带班级的学生信息，包括学生所属宿舍和签到记录。这只是一个简单的示例，您可以根据实际情况进行扩展和修改。

MyBatis 关联查询实现多表数据关联 - 老师、宿舍、学生、班级和签到表

原文地址: https://www.cveoy.top/t/topic/m5ZT 著作权归作者所有。请勿转载和采集!

免费AI点我,无需注册和登录