Mybatis-05-多对一｜一对多

1、多对一的处理

什么是多对一

  • 多个学生，对应一个老师
  • 对于学生这边而言， 关联 .. 多个学生，关联一个老师 【多对一】 关联对象
  • 对于老师而言， 集合 ， 一个老师，有很多学生 【一对多】 关联集合

1、测试环境

image

创建表

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');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('6', '小明', '1');

测试环境搭建

  1. Maven新建一个基础项目或模块

  2. 导入依赖 【mysql驱动，Mybatis，junit，lombok】

    <dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>5.1.49</version>
</dependency>
<dependency>
    <groupId>org.mybatis</groupId>
    <artifactId>mybatis</artifactId>
    <version>3.4.6</version>
</dependency>
<dependency>
    <groupId>junit</groupId>
    <artifactId>junit</artifactId>
    <version>4.12</version>
</dependency>
<dependency>
    <groupId>org.projectlombok</groupId>
    <artifactId>lombok</artifactId>
    <version>1.18.20</version>
    <scope>provided</scope>
</dependency>

  3. 建立实体类，Teacher，Student

    student

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

    // 学生需要关联一个老师
    private Teacher teacher;
}

    Teacher

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

  4. 建立接口 StudentMapper，TeacherMapper

  5. 建立StudentMapper.xml，TeacherMapper.xml文件

  6. 在核心配置中绑定注册Mapper接口或文件

  7. 定义一个简单方法，测试查询是否能成功

2、按照查询嵌套处理

思路：

  1. 先查询所有的学生信息
  2. 根据查询出来的学生的 tid，寻找对应的老师 ！子查询

实现代码：

  1. StudentMapper 接口中定义查询方法 （ 查询所有的学生信息，以及对应的老师信息

    ```java
public interface StudentMapper {

    public List<Student> getStudent();
}
```

  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.qn.dao.StudentMapper">
    <select id="getStudent" resultMap="StudentTeacher">
        select * from student
    </select>

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

</mapper>

复杂的属性需要单独处理：

  • 对象：association
  • 集合：collection

测试代码：

@Test
public void getStudent(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    List<Student> students = mapper.getStudent();
    for (Student student : students) {
        System.out.println(student);
    }

    sqlSession.close();
}

结果：

image

3、按照结果嵌套处理

实现代码：

  1. StudentMapper 接口中定义查询方法

    public interface StudentMapper {

    public List<Student> studentTeacher();
}

  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.qn.dao.StudentMapper">

    <select id="studentTeacher" resultMap="StudentMap">
        select s.id sid, s.name sname, t.name tname
        from student s, teacher t
        where tid = t.id
    </select>
    
    <resultMap id="StudentMap" type="Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>

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

测试代码：

@Test
public void studentTeacher(){
    SqlSession sqlSession = MybatisUtils.getSqlSession();

    StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);

    List<Student> students = mapper.studentTeacher();
    for (Student student : students) {
        System.out.println(student);
    }
    sqlSession.close();
}

结果：

image

mysql 中多对一查询方式：

  • 子查询 ---->> 查询嵌套
  • 联表查询 --->> 结果嵌套

2、一对多的处理

1、测试环境

与上方一致

2、按照查询嵌套处理

代码实现：

  1. TeacherMapper 接口中定义查询方法

    public interface TeacherMapper {

    // 获取指定老师下的所有学生
    List<Teacher> getTeacherById(@Param("tid") int id);
}

  2. 编写 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.qn.dao.TeacherMapper">

    <select id="getTeacherById" resultMap="TeacherMap">
        select s.id sid, s.name sname, t.name tname, t.id tid
        from student s, teacher t
        where tid = t.id and t.id = #{tid}
    </select>
    
    <resultMap id="TeacherMap" 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"/>
        </collection>
    </resultMap>
    
</mapper>

测试代码：

@Test
public void getTeacherById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    List<Teacher> teachers = mapper.getTeacherById(1);
    for (Teacher teacher : teachers) {
        System.out.println(teacher);
    }

    sqlSession.close();
}

结果：

image

/*
* Teacher{
*   id=0, 
*   name='秦老师', 
*   students=[
*     Student{id=1, name='遇见星光', tid=1}, 
*     Student{id=2, name='小红', tid=1}, 
*     Student{id=3, name='小张', tid=1}, 
*     Student{id=4, name='小李', tid=1}, 
*     Student{id=5, name='小王', tid=1}, 
*     Student{id=6, name='小明', tid=1}
*   ]
* }
 */

3、按照结果嵌套处理

代码实现：

  1. TeacherMapper 接口中定义查询方法

    public interface TeacherMapper {
    // 获取指定老师下的所有学生
    List<Teacher> getTeacherByIdTwo(@Param("tid") int id);
}

  2. 编写 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.qn.dao.TeacherMapper">

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

</mapper>

测试代码：

@Test
public void getTeacherById() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);

    List<Teacher> teachers = mapper.getTeacherByIdTwo(1);
    for (Teacher teacher : teachers) {
        System.out.println(teacher);
    }

    sqlSession.close();
}

结果：

image

/*
* Teacher{
*   id=0,
*   name='秦老师',
*   students=[
*     Student{id=1, name='遇见星光', tid=1},
*     Student{id=2, name='小红', tid=1},
*     Student{id=3, name='小张', tid=1},
*     Student{id=4, name='小李', tid=1},
*     Student{id=5, name='小王', tid=1},
*     Student{id=6, name='小明', tid=1}
*   ]
* }
 */

3、总结

  1. 关联： association （多对于一 （对象
  2. 集合： collection（一对多 （集合

javaType="" 指定 实体类 属性的类型

ofType="" 用来指定映射 集合中的泛型

注意点：

  1. 保证SQL的可读性，尽量保证通俗易懂
  2. 注意一对多和多对一中，属性名和字段的问题
  3. 如果问题不好排查错误，可以使用日志，建议使用Log4j
posted @   遇见星光  阅读(47)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：向量存储与相似性搜索在 .NET 中的实现
· 基于Microsoft.Extensions.AI核心库实现RAG应用
· Linux系列：如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
阅读排行：
· 震惊！C++程序真的从main开始吗？99%的程序员都答错了
· winform 绘制太阳，地球，月球 运作规律
· 【硬核科普】Trae如何「偷看」你的代码？零基础破解AI编程运行原理
· 上周热点回顾（3.3-3.9）
· 超详细：普通电脑也行Windows部署deepseek R1训练数据并当服务器共享给他人
点击右上角即可分享
微信分享提示
AI IDE Trae