6.2. 一对多关联表查询

1). 提出需求

根据classId查询对应的班级信息,包括学生,老师

2). 创建表和数据：

CREATE TABLE student(

         s_idINT PRIMARY KEY AUTO_INCREMENT,

         s_nameVARCHAR(20),

         class_idINT

);

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

3). 定义实体类：

public class Student {

         privateint id;

         privateString name;

}

 

public class Classes {

         privateint id;

         privateString name;

         privateTeacher teacher;

         privateList<Student> students;

}

4). 定义sql映射文件ClassMapper.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.mybatis.test6.ClassMapper">


<!-- 根据classId查询对应的班级信息，包括老师（一对一），学生（一对多） -->


<!-- 方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集 
 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="getClass" parameterType="int" resultMap="getClassMap">
    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="com.mybatis.test6.Classes" id="getClassMap">
       <id property="id" column="c_id"/>
       <result property="name" column="c_name"/>
       <!-- 一对一 -->
       <association property="teacher" javaType="com.mybatis.test5.Teacher">
          <id property="id" column="t_id"/>
          <result property="name" column="t_name"/>
       </association>
       <!-- 一对多，这里property即实体类中该域的名字“private List<Student> students;” -->
       <collection property="students" ofType="com.mybatis.test6.Student">
           <id property="id" column="s_id"/>
           <result property="name" column="s_name"/>
       
       </collection>
    
    </resultMap>


</mapper>