Mybatis 实现一对多关联,进行sql查询 （16）

Mybatis 实现一对多关联,进行sql查询。为了更加形象，采用：一个教研组对应多个教师，既：教师的教研编号与教研组的教研编号关联。作应用举例说明。

1、mapper.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.mybatis03.mapper.TeacherMapper">
    <!-- 实现一对多关联，一个教研组对应多个教师，既：教师的教研编号与教研组的教研编号关联  -->
    <select id="queryGroupAndTeachers"  parameterType="int" resultMap="groupTeacherMap">
        SELECT g.*,t.* FROM teachergroup g
         INNER JOIN teacher t
         ON g.groupid = t.groupno
         WHERE g.groupid =#{groupid}
    </select>
    <!--类和表对应关系 (一对多都用collection，一对一用 association) -->
    <resultMap id="groupTeacherMap" type="com.mybatis03.bean.TeacherGroup">
        <!-- 因为type主类是TeacherGroup，所以先配置  -->
        <id property="groupID" column="groupid"/>
        <result property="groupName" column="groupname"/>
        <!-- 配置成员属性教师 ,一对多；属性类型：javaType,属性元素类型 ofType-->
        <collection property="teacherList" ofType="com.mybatis03.bean.Teacher">
            <id property="teaNo" column="teano"></id>
            <result property="teaName" column="teaname"></result>
            <result property="courseNo" column="courseno"></result>
            <result property="groupNo" column="groupno"></result>
        </collection>
    </resultMap>
</mapper>

2、接口类

/**
 * @author ：jack.zhao
 * @Describe: 操作mybatis接口
 * @date ：2021-10-16 22:55
 */
public interface TeacherMapper {
    /* 实现一对多关联，一个教研组对应多个教师，既：教师的教研编号与教研组的教研编号关联  */
    TeacherGroup queryGroupAndTeachers(int groupNo);
}

3、实体类

3.1 教师实体类

package com.mybatis03.bean;

/**
 * @author ：jack.zhao
 * @description 教师实体类
 * @date ：2021-10-16 22:55
 */
public class Teacher {
    @Override
    public String toString() {
        return "Teacher{" +
                "teaNo=" + teaNo +
                ", courseNo=" + courseNo +
                ", teaName='" + teaName + '\'' +
                ", teacherCourse{" +

                "courseNo=" + teacherCourse.getCourseNo() +
                ", courseName=" + teacherCourse.getCourseName() +"}"+

                '}';
    }


    /* 教师编号 */
    private int teaNo;
    /* 课程编号 */
    private int courseNo;
    /* 教师名称 */
    private String teaName;

    /* 教师课程类,课程作为老师属性 */
    private TeacherCourse teacherCourse;

    /* 新增一个教研组编号，用于关联教研组编号，实现多对一关联*/
    private int  groupNo;

    /**
     *  <!--利用resultMap实现一对一 -->
     * @return
     */
    public TeacherCourse getTeacherCourse() {
        return teacherCourse;
    }

    public void setTeacherCourse(TeacherCourse teacherCourse) {
        this.teacherCourse = teacherCourse;
    }

    public int getGroupNo() {
        return groupNo;
    }

    public void setGroupNo(int groupNo) {
        this.groupNo = groupNo;
    }

    public int getCourseNo() {
        return courseNo;
    }

    public void setCourseNo(int courseNo) {
        this.courseNo = courseNo;
    }

    public int getTeaNo() {
        return teaNo;
    }

    public void setTeaNo(int teaNo) {
        this.teaNo = teaNo;
    }

    public String getTeaName() {
        return teaName;
    }

    public void setTeaName(String teaName) {
        this.teaName = teaName;
    }
}

3.2 教研组实体类

package com.mybatis03.bean;

import java.util.List;

/**
 * @author ：tycoon
 * @description：教研组，一个组可对应多个老师
 * @date ：2021-10-19 15:51
 */
public class TeacherGroup {
    @Override
    public String toString() {

        String str = "";
        for (int i = 0; i < teacherList.size(); i++) {

            str += "teaNo=" + teacherList.get(i).getTeaNo() + ",";
            str += "courseNo=" + teacherList.get(i).getCourseNo() + ",";
            str += "teaName=" + teacherList.get(i).getTeaName() + ",";
            str += "groupNo=" + teacherList.get(i).getGroupNo();
            if (i < teacherList.size() - 1) {
                str += ",";
            }

        }
        return "TeacherGroup{" +
                "groupID=" + groupID +
                ", groupName='" + groupName + '\'' +
                ", teacherList{" +
//                "teaNo=" + teacherList.get(0).getTeaNo() + "," +
//                "courseNo=" + teacherList.get(0).getCourseNo() + "," +
//                "teaName=" + teacherList.get(0).getTeaName() + "," +
//                "groupNo=" + teacherList.get(0).getGroupNo() + "," +
                    str+
                "}" +
                '}';
    }


    /* 教研组编号：汉语教研组 1，英语教研组2，日语教研组3，德语教研组 4 */
    /* 教研组编号，用于关联教师编号，实现多对一关联*/
    private int groupID;

    /* 教研组名称，英语教研组，日语教研组还是德语教研组*/
    private String groupName;

    /* 一个教研组对应多个教师,属于一对多关系。关联关系：教师的教研编号与教研组的教研编号关联 */
    private List<Teacher> teacherList;

    public List<Teacher> getTeacherList() {
        return teacherList;
    }

    public void setTeacherList(List<Teacher> teacherList) {
        this.teacherList = teacherList;
    }

    public int getGroupID() {
        return groupID;
    }

    public void setGroupID(int groupID) {
        this.groupID = groupID;
    }

    public String getGroupName() {
        return groupName;
    }

    public void setGroupName(String groupName) {
        this.groupName = groupName;
    }
}

4、数据表结构和测试数据

4.1 教研组数据库表结构

 

 4.2 教师表结构建立

　　

 

 4.3 教师表数据

 

 4.4 教研组表数据

 

 5、测试类

package com.mybatis03.test;

import com.mybatis03.bean.Teacher;
import com.mybatis03.bean.TeacherBusiness;
import com.mybatis03.bean.TeacherGroup;
import com.mybatis03.mapper.TeacherMapper;
import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;
import org.junit.jupiter.api.Test;

import java.io.Reader;
import java.util.List;

/**
 * @author ：jack.zhao
 * @Describe: 测试
 * @date ：2021-10-16 22:55
 */
public class testTeacher01 {

   
    /* 实现一对多关联，一个教研组对应多个教师，既：教师的教研编号与教研组的教研编号关联 */
    @Test
    public void queryGroupAndTeachers() throws Exception {
        Reader reader = Resources.getResourceAsReader("mybatis-03.xml");
        SqlSessionFactory sessionFactory = new SqlSessionFactoryBuilder().build(reader);

        SqlSession session = sessionFactory.openSession();
        int groupNo = 1;
        TeacherMapper personMapper = session.getMapper(TeacherMapper.class);
        TeacherGroup teacherGroup = personMapper.queryGroupAndTeachers(groupNo);
        System.out.println("实现一对多关联，一个教研组对应多个教师,查询结果为：" + teacherGroup);
    }
}

测试结构截图

"C:\Program Files\Java\jdk1.8.0_25\bin\java" -ea -Didea.test.cyclic.buffer.size=1048576 "-javaagent:C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\lib\idea_rt.jar=1304:C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\bin" -Dfile.encoding=UTF-8 -classpath "C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\lib\idea_rt.jar;C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\plugins\junit\lib\junit-rt.jar;C:\Users\newsoft\AppData\Roaming\JetBrains\IntelliJ IDEA 2017.3.5\plugins\junit\lib\junit5-rt.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\platform\junit-platform-launcher\1.5.2\junit-platform-launcher-1.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\apiguardian\apiguardian-api\1.1.0\apiguardian-api-1.1.0.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\platform\junit-platform-engine\1.5.2\junit-platform-engine-1.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\opentest4j\opentest4j\1.2.0\opentest4j-1.2.0.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\platform\junit-platform-commons\1.5.2\junit-platform-commons-1.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\jupiter\junit-jupiter-engine\5.5.2\junit-jupiter-engine-5.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\jupiter\junit-jupiter-api\5.5.2\junit-jupiter-api-5.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\junit\vintage\junit-vintage-engine\5.5.2\junit-vintage-engine-5.5.2.jar;D:\download\lib\mavenTollTransfer\mic-repository\junit\junit\4.12\junit-4.12.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\hamcrest\hamcrest-core\1.3\hamcrest-core-1.3.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\charsets.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\deploy.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\access-bridge-64.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\cldrdata.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\dnsns.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\jaccess.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\jfxrt.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\localedata.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\nashorn.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunec.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunjce_provider.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunmscapi.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\sunpkcs11.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\ext\zipfs.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\javaws.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jce.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jfr.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jfxswt.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\jsse.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\management-agent.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\plugin.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\resources.jar;C:\Program Files\Java\jdk1.8.0_25\jre\lib\rt.jar;D:\ideaworkspace\ProjectStudy\mybatis-02\target\classes;D:\download\lib\mavenTollTransfer\mic-repository\org\hamcrest\hamcrest-core\2.1\hamcrest-core-2.1.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\hamcrest\hamcrest\2.1\hamcrest-2.1.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\mybatis\mybatis\3.4.5\mybatis-3.4.5.jar;D:\download\lib\mavenTollTransfer\mic-repository\mysql\mysql-connector-java\5.1.44\mysql-connector-java-5.1.44.jar;D:\download\lib\mavenTollTransfer\mic-repository\cglib\cglib\3.3.0\cglib-3.3.0.jar;D:\download\lib\mavenTollTransfer\mic-repository\org\ow2\asm\asm\7.1\asm-7.1.jar;D:\download\lib\mavenTollTransfer\mic-repository\net\logstash\logback\logstash-logback-encoder\5.3\logstash-logback-encoder-5.3.jar;D:\download\lib\mavenTollTransfer\mic-repository\com\fasterxml\jackson\core\jackson-databind\2.10.3\jackson-databind-2.10.3.jar;D:\download\lib\mavenTollTransfer\mic-repository\com\fasterxml\jackson\core\jackson-annotations\2.10.3\jackson-annotations-2.10.3.jar;D:\download\lib\mavenTollTransfer\mic-repository\com\fasterxml\jackson\core\jackson-core\2.10.3\jackson-core-2.10.3.jar" com.intellij.rt.execution.junit.JUnitStarter -ideVersion5 -junit5 com.mybatis03.test.testTeacher01,queryGroupAndTeachers
实现一对多关联，一个教研组对应多个教师,查询结果为：TeacherGroup{groupID=1, groupName='chinese', teacherList{teaNo=1001,courseNo=7001,teaName=sara,groupNo=1,teaNo=1002,courseNo=7002,teaName=sakula,groupNo=1}}

Process finished with exit code 0