06_一对多联表查询

1 需求

• 一个班级中有多个学生
• 根据classId查询对应的班级信息，包括学生信息和老师信息

2 创建表和数据

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

3 定义实体类

• Student

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

• 修改Classes类，添加一个学生集合属性

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

4 定义SQL映射文件classMapper.xml

4.1 方式一：嵌套结果，也就是连表查询

• xml
  

<!-- 方式一：嵌套结果 ,连表查询
	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
	-->
	<resultMap type="Classes" id="ClassResultMap3">
		<id property="id" column="c_id"/>
		<result property="name" column="c_name"/>
		<association property="teacher" column="teacher_id" javaType="Teacher">
			<id property="id" column="t_id"/>
			<result property="name" column="t_name"/>
		</association>
		<!-- ofType指定students集合中的对象类型 -->
		<collection property="students" ofType="Student">
			<id property="id" column="s_id"/>
			<result property="name" column="s_name"/>
		</collection>
	</resultMap>
	<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>

• 测试
  

@Test
	public void testOM() {
		SqlSessionFactory factory = MybatisUtils.getFactory();
		SqlSession sqlSession = factory.openSession();
		Classes c = sqlSession.selectOne("cn.imentors.mybatis.test6.classMapper.getClass3", 1);
		System.out.println(c);
	}

4.2 方式二，嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型

• xml
  

<!-- 
	方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
		SELECT * FROM class WHERE c_id=1;
		SELECT * FROM teacher WHERE t_id=1   //1 是上一个查询得到的teacher_id的值
		SELECT * FROM student WHERE class_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="students" 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>

测试

@Test
	public void testOM2() {
		SqlSessionFactory factory = MybatisUtils.getFactory();
		SqlSession sqlSession = factory.openSession();
		Classes c = sqlSession.selectOne("cn.imentors.mybatis.test6.classMapper.getClass4", 1);
		System.out.println(c);
	}

5 属性说明

• collection:做一对多关联查询
• ofType:指定集合中元素对象的类型

捐赠我们
    良师益友工作室一直在致力于帮助编程爱好更加快速方便地学习编程，如果您对我们的成果表示认同并且觉得对你有所帮助，欢迎您对我们捐赠^_^。