五、实现关联表查询

五、实现关联表查询

1.一对一关联

根据查询所有班级信息及其相关老师信息 

1.1 创建表和添加数据

CREATE TABLE teacher(
t_id number(8) PRIMARY KEY,
t_name VARCHAR2(30)
);

CREATE TABLE class(
c_id number(8) PRIMARY KEY,
c_name VARCHAR2(20),
teacher_id number(8)
);

ALTER TABLE class ADD CONSTRAINT fk_teacher_id FOREIGN KEY (teacher_id) REFERENCES
teacher(t_id);

INSERT INTO teacher(t_id,t_name) VALUES(10001,'LS1');
INSERT INTO teacher(t_id,t_name) VALUES(10002,'LS2');
INSERT INTO class VALUES(101,'bj_a', 10001);
INSERT INTO class VALUES(102,'bj_b', 10002);

1.2 定义实体类

public class Teacher {

	private int t_id;
	private String t_name;
	public Teacher() {
	}
	public Teacher(int t_id, String t_name) {
		super();
		this.t_id = t_id;
		this.t_name = t_name;
	}
	public int getT_id() {
		return t_id;
	}
	public void setT_id(int t_id) {
		this.t_id = t_id;
	}
	public String getT_name() {
		return t_name;
	}
	public void setT_name(String t_name) {
		this.t_name = t_name;
	}
	@Override
	public String toString() {
		return "Teacher [t_id=" + t_id + ", t_name=" + t_name + "]";
	}
	
}

public class Classes {  
  
    private int c_id;  
    private String c_name;  
    private Teacher teacher;    
    public int getC_id() {  
        return c_id;  
    }  
    public void setC_id(int c_id) {  
        this.c_id = c_id;  
    }  
    public String getC_name() {  
        return c_name;  
    }  
    public void setC_name(String c_name) {  
        this.c_name = c_name;  
    }  
    public Teacher getTeacher() {  
        return teacher;  
    }  
    public void setTeacher(Teacher teacher) {  
        this.teacher = teacher;  
    }  
    public Classes() {  
    }  
    public Classes(int c_id, String c_name, Teacher teacher) {  
        super();  
        this.c_id = c_id;  
        this.c_name = c_name;  
        this.teacher = teacher;  
    }  
    @Override  
    public String toString() {  
        return "Classes [c_id=" + c_id + ", c_name=" + c_name + ", teacher=" + teacher + "]";  
    }  
      
}

1.3 通过resultMap实现对class的查询  classesMapper.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="com.mybatis.entity.classesMapper">
	<resultMap type="Classes" id="ClassResultMap">
		<id column="c_id" property="c_id" />
		<result column="c_name" property="c_name"/>
		<association property="teacher" column="teacher_id" javaType="Teacher">
			<id column="t_id" property="t_id"/>
			<result column="t_name" property="t_name"/>
		</association>
	</resultMap>
	<select id="getAllClasses" resultMap="ClassResultMap">
		select * from class c,teacher t where c.teacher_id = t.t_id
	</select>
	
	<resultMap type="Classes" id="ClassResultMap2">
		<id column="c_id" property="c_id" />
		<result column="c_name" property="c_name"/>
		<association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacher">
		</association>
	</resultMap>
	<select id="getAllClasses2" resultMap="ClassResultMap2">
		select * from class
	</select>
	
	<select id="getTeacher" parameterType="int" resultType="Teacher">
		SELECT * FROM teacher WHERE t_id=#{t_id}
	</select>
</mapper>

1.4 在conf.xml文件中注册classMapper.xml，设置entity的别名

	<typeAliases>
		<typeAlias type="com.mybatis.entity.Classes" alias="Classes"/>
		<typeAlias type="com.mybatis.entity.Teacher" alias="Teacher"/>
	</typeAliases>

	<mappers>
		<mapper resource="com/mybatis/entity/classesMapper.xml" />
		<mapper resource="com/mybatis/entity/teacherMapper.xml" />
	</mappers>

1.5测试

public class ClassesTest {

	SqlSession session = DbUtil.getSession();

	@Test
	public void selectAllBook(){
		try {
			String statement = "com.mybatis.entity.classesMapper.getAllClasses";
			List<Classes> classes = null;
			classes = session.selectList(statement);
			System.out.println("1111111:"+classes.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	

	@Test
	public void selectAllBook2(){
		try {
			String statement = "com.mybatis.entity.classesMapper.getAllClasses2";
			List<Classes> classes = null;
			classes = session.selectList(statement);
			System.out.println("222222:"+classes.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	

}

總結：

association 用于一對一的关联查询

property 对象属性的名称

javaType 对象属性的类型

column 所对应的外键字段名称

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

有两种方式：

①嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
          封装联表查询的数据(去除重复的数据)

select * from class c,teacher t where c.teacher_id = t.t_id

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

select * from class

select * from teacher where t_id=#{t_id}

2.一对多关联

查询初所有班级及其对应的学生和老师

2.1创建表和添加数据

CREATE TABLE student(
s_id number(8) PRIMARY KEY,
s_name VARCHAR2(30),
class_id number(8)
);

Alter table student
add constraint fk_student_class foreign key (class_id) references class(c_id);

create sequence seq_student
start with 1
increment by 1;

INSERT INTO student(s_id,s_name, class_id) VALUES(seq_student.nextval,'xs_A', 101);
INSERT INTO student(s_id,s_name, class_id) VALUES(seq_student.nextval,'xs_B', 101);
INSERT INTO student(s_id,s_name, class_id) VALUES(seq_student.nextval,'xs_C', 101);
INSERT INTO student(s_id,s_name, class_id) VALUES(seq_student.nextval,'xs_D', 102);
INSERT INTO student(s_id,s_name, class_id) VALUES(seq_student.nextval,'xs_E', 102);
INSERT INTO student(s_id,s_name, class_id) VALUES(seq_student.nextval,'xs_F', 102);

2.2定义实体类

public class Student {

	private int s_id;
	private String s_name;
	public Student() {
		super();
	}
	public Student(int s_id, String s_name) {
		super();
		this.s_id = s_id;
		this.s_name = s_name;
	}
	public int getS_id() {
		return s_id;
	}
	public void setS_id(int s_id) {
		this.s_id = s_id;
	}
	public String getS_name() {
		return s_name;
	}
	public void setS_name(String s_name) {
		this.s_name = s_name;
	}
	@Override
	public String toString() {
		return "Student [s_id=" + s_id + ", s_name=" + s_name + "]";
	}
}

修改class类

public class Classes {

	private int c_id;
	private String c_name;
	private Teacher teacher;
	private List<Student> students;
	public int getC_id() {
		return c_id;
	}
	public void setC_id(int c_id) {
		this.c_id = c_id;
	}
	public String getC_name() {
		return c_name;
	}
	public void setC_name(String c_name) {
		this.c_name = c_name;
	}
	public Teacher getTeacher() {
		return teacher;
	}
	public void setTeacher(Teacher teacher) {
		this.teacher = teacher;
	}
	public List<Student> getStudents() {
		return students;
	}
	public void setStudents(List<Student> students) {
		this.students = students;
	}
	public Classes() {
	}
	public Classes(int c_id, String c_name, Teacher teacher, List<Student> students) {
		super();
		this.c_id = c_id;
		this.c_name = c_name;
		this.teacher = teacher;
		this.students = students;
	}
	@Override
	public String toString() {
		return "Classes [c_id=" + c_id + ", c_name=" + c_name + ", teacher=" + teacher + ", students=" + students + "]";
	}
	
}

2.3通过resultMap实现对class的查询  classesMapper.xml

<resultMap type="Classes" id="ClassResultMap3">
		<id column="c_id" property="c_id" />
		<result column="c_name" property="c_name"/>
		<association property="teacher" column="teacher_id" javaType="Teacher">
			<id column="t_id" property="t_id"/>
			<result column="t_name" property="t_name"/>
		</association>
		<collection property="students" column="c_id" ofType="Student">
			<id column="s_id" property="s_id"/>
			<result column="s_name" property="s_name"/>
		</collection>
	</resultMap>
	<select id="getAllClasses3" resultMap="ClassResultMap3">
		select * from class c,teacher t,student s where c.teacher_id = t.t_id and c.c_id=s.class_id
	</select>
	
	
	<resultMap type="Classes" id="ClassResultMap4">
		<id column="c_id" property="c_id" />
		<result column="c_name" property="c_name"/>
		<association property="teacher" column="teacher_id" javaType="Teacher" select="getTeacher">
		</association>
		<collection property="students" column="c_id" ofType="Student" select="getStudentByClassId">
		</collection>
	</resultMap>
	<select id="getAllClasses4" resultMap="ClassResultMap4">
		select * from class
	</select>
	
	
	<select id="getStudentByClassId" parameterType="int" resultType="Student">
		SELECT * FROM student WHERE class_id=#{c_id}
	</select>

2.4在conf.xml文件中注册classMapper.xml，设置entity的别名

		<typeAlias type="com.mybatis.entity.Student" alias="Student"/>

		<mapper resource="com/mybatis/entity/studentMapper.xml" />

總結：

collection：做一对多关联查询

ofType：指定集合中元素对象的类型