MyBatis關聯查詢

一對一關聯
一對一的場景：一個班主任只屬於一個班級，一個班級也只能有一個班主任。
1、建立班級表和教師表語句

班級表

CREATE TABLE class
    (
        c_id INT NOT NULL AUTO_INCREMENT,
        c_name VARCHAR(20),
        teacher_id INT,
        PRIMARY KEY (c_id)
    )
ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO class(c_name, teacher_id) VALUES('class_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('class_b', 2);

教師表

CREATE TABLE teacher
    (
        t_id INT NOT NULL AUTO_INCREMENT,
        t_name VARCHAR(20),
        PRIMARY KEY (t_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO teacher(t_name) VALUES('teacher1');
INSERT INTO teacher(t_name) VALUES('teacher2');

2、建立實體

public class Classes {
	private int id;
	private String name;
	private Teacher teacher;
/* 省略Getter與Setter */
}

public class Teacher {
	private int id;
	private String name;
	/* 省略Getter與Setter */
}

3、編寫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，namespace的值習慣上設定成包名+sql對映檔名，這樣就能夠保證namespace的值是唯一的 -->
<mapper namespace="com.mahaochen.work.mapper.ClassMapper">
	<!-- 一對一聯表查詢  -->
	<!-- 方式一：巢狀結果，使用巢狀結果對映來處理重複的聯合的子集，封裝聯表查詢的資料（去除重複資料） -->
	<select id="SelectOneToOne1" parameterType="int" resultMap="ClassResultMap1">
		select * from class c, teacher t where c.teacher_id=t.t_id and c.c_id=#{id}
	</select>
	<!-- 使用resultMap對映實體類和欄位之間的一一對應關係 -->
	<resultMap type="com.mahaochen.work.domain.Classes" id="ClassResultMap1">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" javaType="com.mahaochen.work.domain.Teacher">
			<id property="id" column="t_id" />
			<result property="name" column="t_name" />
		</association>
	</resultMap>

	<!-- 方式二：巢狀查詢，通過執行另外一個SQL對映語句來返回預期的複雜型別 -->
	<select id="SelectOneToOne2" parameterType="int" resultMap="ClassResultMap2">
		select * from class where c_id=#{id}
	</select>
	<!-- 使用resultMap對映實體類和欄位之間的一一對應關係 -->
	<resultMap type="com.mahaochen.work.domain.Classes" id="ClassResultMap2">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" column="teacher_id" select="SelectTeacher" />
	</resultMap>
	<!-- 查詢Teacher表語句 -->
	<select id="SelectTeacher" parameterType="int" resultType="com.mahaochen.work.domain.Teacher">
		SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
	</select>
</mapper>

        MyBatis使用association標籤來解決一對一的關聯查詢，association標籤可用的屬性如下：
property 物件屬性的名稱
javaType 物件屬性的型別
column 所對應的外來鍵欄位名稱
select 使用另一個查詢封裝的結果

一對多關聯
一對多的場景：根據班級，獲取包括學生、教師、班級資訊。一個班級包好多個學生。
1、建立學生表

CREATE TABLE student
    (
        s_id INT NOT NULL AUTO_INCREMENT,
        s_name VARCHAR(20),
        class_id INT,
        PRIMARY KEY (s_id)
    )
    ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO student(s_name, class_id) VALUES('student_A', 1);
INSERT INTO student(s_name, class_id) VALUES('student_B', 1);
INSERT INTO student(s_name, class_id) VALUES('student_C', 1);
INSERT INTO student(s_name, class_id) VALUES('student_D', 2);
INSERT INTO student(s_name, class_id) VALUES('student_E', 2);
INSERT INTO student(s_name, class_id) VALUES('student_F', 2);

2、建立實體

public class Student {

	private int id;
	private String name;
/* 省略Getter與Setter */
}

public class Classes {

	private int id;
	private String name;
	private Teacher teacher;
	//使用一個List<Student>集合屬性表示班級擁有的學生
	private List<Student> students;
/* 省略Getter與Setter */
}

3、編寫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，namespace的值習慣上設定成包名+sql對映檔名，這樣就能夠保證namespace的值是唯一的 -->
<mapper namespace="com.mahaochen.work.mapper.ClassMapper">
<!-- 一對多聯表查詢  -->
	<!--方式一：巢狀結果，使用巢狀結果對映來處理重複的聯合結果的子集 -->
	<select id="SelectOneToMany1" 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="com.mahaochen.work.domain.Classes" id="ClassResultMap3">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" column="teacher_id"
			javaType="com.mahaochen.work.domain.Teacher">
			<id property="id" column="t_id" />
			<result property="name" column="t_name" />
		</association>
		<!-- ofType指定students集合中的物件型別 -->
		<collection property="students" ofType="com.mahaochen.work.domain.Student">
			<id property="id" column="s_id" />
			<result property="name" column="s_name" />
		</collection>
	</resultMap>

	<!--方式二：巢狀查詢,通過執行另外一個SQL對映語句來返回預期的複雜型別 -->
	<select id="SelectOneToMany2" parameterType="int" resultMap="ClassResultMap4">
		select * from class where c_id=#{id}
	</select>
	<resultMap type="com.mahaochen.work.domain.Classes" id="ClassResultMap4">
		<id property="id" column="c_id" />
		<result property="name" column="c_name" />
		<association property="teacher" column="teacher_id"
			javaType="com.mahaochen.work.domain.Teacher" select="SelectTeacher2"></association>
		<!-- collection標籤來解決一對多的關聯查詢，ofType屬性指定集合中元素的物件型別。 -->
		<collection property="students" ofType="com.mahaochen.work.domain.Student"
			column="c_id" select="SelectStudent2"></collection>
	</resultMap>

	<select id="SelectTeacher2" parameterType="int" resultType="com.mahaochen.work.domain.Teacher">
		SELECT t_id id, t_name name FROM teacher WHERE t_id=#{id}
	</select>
	
	<select id="SelectStudent2" parameterType="int" resultType="com.mahaochen.work.domain.Student">
		SELECT s_id id, s_name name FROM student WHERE class_id=#{id}
	</select>
</mapper>

       MyBatis中使用collection標籤來解決一對多的關聯查詢，ofType屬性指定集合中元素的物件型別。collection標籤可用的屬性如下：
property 物件屬性的名稱
javaType 物件屬性的型別
column 所對應的外來鍵欄位名稱
select 使用另一個查詢封裝的結果