MyBatis-一对多关联表查询 -06

一、一对多的映射关系

添加学生表，一个班级对应多个学生，根据班级id查询对应班级的学生和老师

学生表

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

学生实体类

package com.weixuan.mybatis.bean;

public class Student {

	private int id;
	private String name;

	public int getId() {
		return id;
	}

	public void setId(int id) {
		this.id = id;
	}

	public String getName() {
		return name;
	}

	public void setName(String name) {
		this.name = name;
	}

	public Student(int id, String name) {
		super();
		this.id = id;
		this.name = name;
	}

	public Student() {
		super();
	}

	@Override
	public String toString() {
		return "Student [id=" + id + ", name=" + name + "]";
	}

}

二、两种方式查询

<?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.weixuan.mybatis.bean.one2one2">

	<!-- 方式一: 嵌套结果: 使用嵌套结果映射来处理重复的联合结果的子集 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" 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>

	<!-- 方式二：嵌套查询：通过执行另外一个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>

</mapper>

package com.weixuan.testcrud3;

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

import com.weixuan.mybatis.bean.Classes;
import com.weixuan.utils.MyBatisUtils;

public class Test2 {

	SqlSessionFactory sqlsessionfactory = MyBatisUtils.getFactory();
	
	SqlSession sqlsession = sqlsessionfactory.openSession(true);
	
	@Test
	public void test() {
		
		String sql = "com.weixuan.mybatis.bean.one2one2.getClass3";
	
		Classes classes = sqlsession.selectOne(sql, 2);
		
		System.out.println(classes);
		
		sqlsession.close();
		
	}
	
	@Test
	public void test1() {
		
		String sql = "com.weixuan.mybatis.bean.one2one2.getClass4";
	
		Classes classes = sqlsession.selectOne(sql, 2);
		
		System.out.println(classes);
		
		sqlsession.close();
		
	}
}