MyBatis-一对一关联表查询 -05

一、一对一的关联表查询

假设一个老师对应一个班级的情况

班级表

CREATE TABLE class(
	c_id INT PRIMARY KEY AUTO_INCREMENT, 
	c_name VARCHAR(20), 
	teacher_id INT
);

教师表

CREATE TABLE teacher(
	t_id INT PRIMARY KEY AUTO_INCREMENT, 
	t_name VARCHAR(20)
);

班级类

package com.weixuan.mybatis.bean;

public class Classes {
	private int id;
	private String name;
	private Teacher teacher;

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

	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 Teacher getTeacher() {
		return teacher;
	}

	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}

	public Classes(int id, String name, Teacher teacher) {
		super();
		this.id = id;
		this.name = name;
		this.teacher = teacher;
	}

	public Classes() {
		super();
	}

}

教师类

package com.weixuan.mybatis.bean;

public class Teacher {
	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;
	}

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

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

	public Teacher() {
		super();
	}

}

二、两种实现子查询的方式

方式一

	<!-- 方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集 封装联表查询的数据(去除重复的数据) -->
	<select id="getClass" parameterType="int" resultMap="GetClassOne2One">
		select *
		from class c, teacher t where c.teacher_id=t.t_id and c.c_id= #{id}
	</select>

	<resultMap type="Classes" id="GetClassOne2One">

		<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>
	</resultMap>

方式二

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


	<select id="getClass2" parameterType="int" resultMap="ClassResultMap2">
		select * from class where c_id=#{id}
	</select>
	<resultMap type="_Classes" id="ClassResultMap2">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" column="teacher_id"
			select="getTeacher">
		</association>
	</resultMap>
	<select id="getTeacher" parameterType="int" resultType="_Teacher">
		SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
	</select>

三、标签总结

association ----> 用于一对一的关联查询

property ----> 对象属性的名称

javaType ----> 对象属性的类型

column ----> 所对应的外键的字段名称或者实体类成员在数据库表中的对应字段名

select ----> 使用另一个查询封装的结果