小技巧：Mybatis多表连接查询（一对多，多对多)

最新推荐文章于 2025-08-26 19:00:04 发布

原创最新推荐文章于 2025-08-26 19:00:04 发布 · 352 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql

QITA 专栏收录该内容

17 篇文章

订阅专栏

本文详细解析了SQL语句中的关联查询概念，包括多对一和一对多的关系，并通过具体示例展示了如何在MyBatis中实现这些查询，特别关注于实体类的定义、DAO层的方法及XML映射文件的配置。

回顾sql语句：sql语句

关联 - association 【多对一】
集合 - collection 【一对多】

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

多对一
实体类

public class Student {

    private int id;
    private String name;
    private Teacher teacher;
 }

public class Teacher {

    private int id;
    private String name;
    private List<Student> students;
}

dao层
Student getStudent(int id);

思路：先查询学生，再由学生tid找老师

<mapper namespace="com.wang.dao.StudentMapper">

    <select id="getStudent" resultMap="StudentTeacher">
        select * from student where id = #{id}
    </select>
    
    <resultMap id="StudentTeacher" type="com.wang.entity.Student">
        <!--复杂的属性，我们需要单独处理 对象： association 集合： collection -->
        <association property="teacher" column="tid" javaType="com.wang.entity.Teacher" select="getTeacher"/>
    </resultMap>
    
    <select id="getTeacher" resultType="com.wang.entity.Teacher">
        select * from teacher where id = #{tid}
    </select>

</mapper>

一对多
由老师查学生

<select id="getTeacher" resultMap="TeacherStudent">
    select * from mybatis.teacher where id = #{tid}
</select>

<resultMap id="TeacherStudent" type="Teacher">
    <collection property="students" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
</resultMap>

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