Mybatis 07 一对多表关联查询

实例:根据classId查询对应的班级信息,包括查询出附带学生和老师的信息

1.创建表和数据集

新建一个学生表，class_id表示班级id，一个班级下有多个学生

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

2.定义实体类：

2.1 创建Student类

package com.dhl.beyond.bean;

public class Student {
    private int id;
    private String name;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }
}

2.2 在之前创建的Classes类中添加 students属性(集合实现),并生成相应的get和set方法.

改后的Classes类

package com.dhl.beyond.bean;

import java.util.List;

public class Classes {
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> students;

    public List<Student> getStudents() {
        return students;
    }

    public void setStudents(List<Student> students) {
        this.students = students;
    }

    @Override
    public String toString() {
        return "Classes{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                ", students=" + students +
                '}';
    }

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }
}

添加的代码介绍

3.创建Mapper文件

3.1 源码

<?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.dhl.beyond.shiyan7.classMapper">
    <select id="getClass" parameterType="int" resultMap="getClassMap">
        select * from class c,teacher t,student s where c.teacher_id =t.t_id and s.class_id = c.c_id and c.c_id =#{id}
    </select>

    <resultMap id="getClassMap" type="Classes">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" javaType="Teacher">
            <id property="id" column="t_id" />
            <result property="name" column="t_name" />
        </association>
        <collection property="students" ofType="Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>

 
</mapper>

3.2 源码介绍

4.注册并且编写测试类

4.1注册

4.2 测试类

package com.dhl.beyond.shiyan7;

import com.dhl.beyond.bean.Classes;
import com.dhl.beyond.util.MybatisUtil;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;

import java.io.IOException;

public class ClassesTest {

    @Test
    public void getClasses() throws IOException {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Classes o = sqlSession.selectOne("com.dhl.beyond.shiyan7.classMapper.getClass", 2);
        System.out.println(o);
    }

}

4.3 结果集

5.补充:第二种方式实现(三条 sql语句实现)

5.1 对Mapper文中添加第二种方式,即用三条 sql语句实现, 在上面的mapper文件基础上添加如下语句:

    <select id="getClass2" parameterType="int" resultMap="getClassMap2">
        select * from class where c_id=#{id}
    </select>
    <select id="getTeacher" parameterType="int" resultType="Teacher">
        select t_id id, t_name name from teacher where t_id=#{id}
    </select>
    <select id="getStudents" parameterType="int" resultType="Student">
        select s_id id,s_name name from student where class_id = #{id}
    </select>
    <resultMap id="getClassMap2" type="Classes">
        <id property="id" column="c_id" />
        <result property="name" column="c_name"/>
        <association property="teacher" select="getTeacher" column="teacher_id" >
        </association>
        <collection property="students" select="getStudents" column="c_id">
        </collection>
    </resultMap>

代码介绍

5.2 测试类中写测试语句

@Test
    public void getClasses2() throws IOException {
        SqlSession sqlSession = MybatisUtil.getSqlSession();
        Classes o = sqlSession.selectOne("com.dhl.beyond.shiyan7.classMapper.getClass2", 1);
        System.out.println(o);
    }

5.3 结果集