Mybatis一对一以及多表联查

第一张学生表：

第二年级表 

 第三张成绩表

第四张科目表：

问题：通过联查获取学生的信息以及科目成绩

学生实体类：

@Data
@ToString
public class Student {
    private int studentno;
    private String loginpwd;
    private String studentname;
    private int sex;
    private int gradeid;
    private String phone;
    private String address;
    private Date borndate;
    private String email;
    private String identitycard;
    private Grade grade;
    private List<Result> results;
}

年级实体类：

@Data
@ToString
public class Grade {
    private int gradeid;
    private String gradename;
}

成绩实体类：

@ToString
@Data
public class Result {
    private int studentno;
    private int subjectno;
    private Date examdate;
    private int studentresult;
    private Subject subject;


}

科目实体类：

@Data
@ToString
public class Subject {
    private int subjectno;
    private  String subjectname;
    private Result studentresult;
}

 StudentMapper

public interface StudentMapper {

       //一对一关联
    public List<Student> getStudentList();

    //多表联查关联
    Student getResult();
}

 Mybatis工具类：

public class MybatisUtil {

    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            //使用mybatis第一步 ：获取SqlSessionFactory对象
            String resource = "mybatis-config.xml";
            InputStream   inputStream = Resources.getResourceAsStream(resource);
             sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    //既然有了 SqlSessionFactory，顾名思义，我们可以从中获得 SqlSession 的实例。SqlSession 提供了在数据库执行 SQL 命令所需的所有方法
    public static SqlSession getSqlSession(){
        return sqlSessionFactory.openSession();
    }


}

mybatis-config配置文件：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<!--configuration核心配置文件 -->


<configuration>

<!--    <settings>-->
<!--        <setting name="logImpl" value="LOG4J"/>-->
<!--    </settings>-->

    <typeAliases>
        <package name="com.chen.pojo"/>
    </typeAliases>


    
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/school?userSSL=true&amp;useUnicode=true&amp;characterEncoding=UTF-8"/>
                <property name="username" value="root"/>
                <property name="password" value="root"/>
            </dataSource>
        </environment>
    </environments>
    <!--每一个mapper.xml都需要在mybatis核心配置文件中注册 -->
    <mappers>
       <mapper resource="mapper/StudentMapper.xml"></mapper>
        <mapper resource="mapper/GradeMapper.xml"></mapper>
        <mapper resource="mapper/ResultMapper.xml"></mapper>
    </mappers>


</configuration>

核心部分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.chen.mapper.StudentMapper">

<!--查询学生的基本信息和年级表-->
    <resultMap id="st_list" type="student">
        <id column="studentno" property="studentno"/>
        <result column="loginpwd" property="loginpwd"/>
        <result column="studentname" property="studentname"/>
        <result column="sex" property="sex"/>
        <result column="gradeid" property="gradeid"/>
        <result column="address" property="address"/>
        <result column="borndate" property="borndate"/>
        <result column="email" property="email"/>
        <result column="identitycard" property="identitycard"/>
    <association property="grade"  javaType="grade" column="gradeid" resultMap="gradeResult" >
    </association>
    </resultMap>
    
    <resultMap id="gradeResult" type="grade">
        <id property="grade" column="grade"/>
        <result property="gradename" column="gradename"/>
    </resultMap>


    <select id="getStudentList" resultMap="st_list">
      SELECT s.studentno,s.loginpwd,s.sex,s.phone,s.address,s.borndate,
      s.email,identitycard,s.studentname,g.gradename ,g.gradeid FROM student AS s inner join grade  AS g
        on s.gradeid = g.gradeid
    </select>

    
    <!--    学生多个成绩，成绩关联科目-->
    <resultMap id="student_result" type="student">
        <id column="studentno" property="studentno"/>
       <result property="studentname" column="studentname"/>
        <collection property="results" ofType="result">
            <result property="studentno" column="studentno"/>
            <result property="studentresult" column="studentresult"/>
            <association property="subject" column="subjectno">
                <id column="subjectno" property="subjectno"/>
                <result column="subjectname" property="subjectname"/>

            </association>

        </collection>
    </resultMap>


    <select id="getResult" resultMap="student_result">
         SELECT s.studentno,studentname,r.studentresult , r.subjectno, sub.subjectname FROM student s
         INNER JOIN result r
         ON s.studentno = r.studentno
         INNER JOIN `subject` sub
         ON r.subjectno = sub.subjectno
    </select>
    


</mapper>

测试类：

public class UserTest {


    @Test
    public void  StudentTest(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = studentMapper.getStudentList();
        for (Student student:studentList){
            System.out.println(student);
        }
        sqlSession.close();
    }


    @Test
    public void  StudentTest3(){
        SqlSession sqlSession = MybatisUtil.getSqlSession();

        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student studentList = studentMapper.getResult();
        System.out.println(studentList);
        sqlSession.close();
    }

一对一运行结果：

多表联查结果：