Mybatis学习笔记-关联表查询的问题 (一对多关联)

最新推荐文章于 2023-07-03 17:29:23 发布

Taowiedong

最新推荐文章于 2023-07-03 17:29:23 发布

阅读量246

点赞数

分类专栏： java Mybatis 文章标签： mybatis

本文链接：https://blog.csdn.net/taoweidong1/article/details/78253660

版权

java 同时被 2 个专栏收录

32 篇文章 0 订阅

订阅专栏

Mybatis

11 篇文章 0 订阅

订阅专栏

上篇博文中对一对一的关联查询问题进行了研究，再实际应用中一对多的关联查询更多，这篇博客就对一对多的关联问题进行简单说明。

项目结构

这里写图片描述

项目源码

创建测试表和数据

CREATE TABLE student(
    s_id INT PRIMARY KEY AUTO_INCREMENT, 
    s_name VARCHAR(20), 
    class_id INT
);
INSERT INTO student(s_name, class_id) VALUES('xs_A', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_B', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_C', 1);
INSERT INTO student(s_name, class_id) VALUES('xs_D', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_E', 2);
INSERT INTO student(s_name, class_id) VALUES('xs_F', 2);

创建对应实体和修改已经存在的实体

省略对应属性的get和set方法

public class Student {
    private int id;
    private String name;
}
//一个班级有多个学生  一对多的关系
public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> students;
}

定义sql映射文件ClassMapper.xml

<!-- 一对多关联查询方法1：嵌套查询 -->
    <select id="getClass3" parameterType="int" resultMap="ClassResultMap3">
        select *
        from class c, teacher t,student s where c.teacher_id=t.t_id and
        c.C_id=s.class_id and c.c_id=#{id}
    </select>
    <resultMap type="_Classes" id="ClassResultMap3">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            javaType="_Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
        <!-- ofType指定Student集合中的对象类型 -->
        <collection property="students" ofType="_Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>


    <!-- 一对多查询方法2：嵌套查询 ，通过执行另外一个SQL映射语句来返回预期的复杂类型 -->
    <select id="getClaasess4" parameterType="int" resultMap="ClassResultMap4">
        select *
        from class where c_id=#{id}
    </select>
    <resultMap type="_Classes" id="ClassResultMap4">
        <id property="id" column="id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id"
            javaType="_Teacher" select="getTeacher2">
        </association>
        <collection property="students" ofType="_Student" column="c_id"
            select="getStudent"></collection>
    </resultMap>
    <select id="getTeacher2" parameterType="int" resultType="_Teacher">
        select
        t_id,t_name name from teacher where t_id=#{id}
    </select>
    <select id="getStudent" parameterType="int" resultType="_Student">
        select s_id id ,s_name name from student where class_id=#{id}
    </select>

测试代码

@Test
    public void TestStudent1() {
        // 注意此处默认不是自动提交事务的
        SqlSession session = MybatisUtils.getFactory().openSession(true);// 创建自动提交事物的Session对象
        String sql = "mapper.ClassMapper.getClass3";
        Classes result = (Classes) session.selectOne(sql, 1);
        logger.info("影响行数：" + result.toString());

    }