5. 关联查询

1.一对一关联

根据班级id查询班级信息(带老师的信息)

创建表和数据

CREATE TABLE teacher(
	t_id INT PRIMARY KEY AUTO_INCREMENT, 
	t_name VARCHAR(20)
);
CREATE TABLE class(
	c_id INT PRIMARY KEY AUTO_INCREMENT, 
	c_name VARCHAR(20), 
	teacher_id INT
);
ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES teacher(t_id);	

INSERT INTO teacher(t_name) VALUES('LS1');
INSERT INTO teacher(t_name) VALUES('LS2');

INSERT INTO class(c_name, teacher_id) VALUES('bj_a', 1);
INSERT INTO class(c_name, teacher_id) VALUES('bj_b', 2);

定义实体类

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

定义sql映射文件ClassMapper.xml

<select id="getClass" parameterType="int" resultMap="ClassResultMap">
	select * from class c, teacher t where c.teacher_id=t.t_id and  c.c_id=#{id}
</select>
<resultMap type="_Classes" id="ClassResultMap">
	<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>

2.一对多关联

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

创建表和数据

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

定义实体类

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

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

定义sql映射文件ClassMapper.xml

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