五、实现关联表查询
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
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:指定集合中元素对象的类型