Mybatis学习——一对多关联表查询

1.实体类

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

2.映射文件

<?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.zhengbin.entity.classMapper">
    <!-- 方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集 
        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=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" javaType="Teacher">
            <id property="id" column="t_id"/>
            <result property="name" column="t_name"/>
        </association>
        <!-- ofType 指定 students 集合中的对象类型 -->
        <collection property="list" ofType="Student">
            <id property="id" column="s_id" />
            <result property="name" column="s_name" />
        </collection>
    </resultMap>
    
    <!-- 方式二:嵌套查询:通过执行另外一个 SQL 映射语句来返回预期的复杂类型
        SELECT * FROM class WHERE c_id=1; 
        SELECT * FROM teacher WHERE t_id=1 
        //1 是上一个查询得到的 teacher_id 的值 SELECT*FROMstudentWHEREclass_id=1 
        //1是第一个查询得到的c_id字段的值 
    -->
    <select id="getClass4" parameterType="int" resultMap="ClassResultMap4"> 
        select * from class where c_id=#{id}
    </select>
    <resultMap type="Classes" id="ClassResultMap4">
        <id property="id" column="c_id" />
        <result property="name" column="c_name" />
        <association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacher2"></association>
        <collection property="list" ofType="Student" column="c_id" select="getStudent"></collection>
    </resultMap>
    <select id="getTeacher2" parameterType="int" resultType="Teacher"> 
        SELECT t_id 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>
</mapper>

3.测试类

package com.zhengbin.test;

import java.util.List;

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;

import com.zhengbin.entity.Classes;
import com.zhengbin.entity.Student;
import com.zhengbin.util.MyBatisUtils;

public class Test5 {
    @org.junit.Test
    public void getClasses(){
        SqlSessionFactory sessionFactory = MyBatisUtils.getFactory();
        // 参数为TRUE，相当于session.commit();
        SqlSession session = sessionFactory.openSession(true);
        // 读取映射文件
        String statement = "com.zhengbin.entity.classMapper" + ".getClass3";
        /*
         * 查看本班所有学生的信息，与任课老师的信息
         * 一个班级对多个学生，一个班级对一个老师
         * 即 一对多 与 一对一
         */
        Classes c = session.selectOne(statement,1);
        List<Student>list = c.getList();
        System.out.println(list);
        System.out.println(c);
        session.close();
    }
    
    @org.junit.Test
    public void getClasses1(){
        SqlSessionFactory sessionFactory = MyBatisUtils.getFactory();
        // 参数为TRUE，相当于session.commit();
        SqlSession session = sessionFactory.openSession(true);
        // 读取映射文件
        String statement = "com.zhengbin.entity.classMapper" + ".getClass4";
        Classes c = session.selectOne(statement,1);
        List<Student> list = c.getList();
        System.out.println(list);
        System.out.println(c);
        session.close();
    }
}