mybatis-联表查询+查询输入的参数是集合(利用参数是集合进行查询,实现级联查询)
一、数据库表的介绍
1、班级表:
CREATE TABLE classes
(c_id
int(3) NOT NULL,c_name
varchar(20) DEFAULT NULL,teacher_id
int(3) DEFAULT NULL,
PRIMARY KEY (c_id
),
KEY teacher_id
(teacher_id
),
CONSTRAINT classes_ibfk_1
FOREIGN KEY (teacher_id
) REFERENCES teacher
(t_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2、学生表:
CREATE TABLE student
(s_id
int(3) NOT NULL,s_name
varchar(20) DEFAULT NULL,classes_id
int(3) DEFAULT NULL,
PRIMARY KEY (s_id
),
KEY classes_id
(classes_id
),
CONSTRAINT student_ibfk_1
FOREIGN KEY (classes_id
) REFERENCES classes
(c_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
3、老师表:
CREATE TABLE teacher
(t_id
int(3) NOT NULL,t_name
varchar(20) DEFAULT NULL,
PRIMARY KEY (t_id
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
4、数据库概念模型:
二、数据库连表查询:
1、查询要求:通过班级编号,查询学生信息及对应的老师和班级的信息
2、mapper映射文件的编写:
<!--resultMap属性与后面取得resultMapper的id名相同,注意这里是三张表的连表查询-->
<select id="selStuClasTeacher" parameterType="int" resultMap="stuClasTeacher">
SELECT s.*,t.t_name,c.c_name
from student s,teacher t,classes c
where s.classes_id=c.c_id=t.t_id
HAVING s.classes_id=#{c_id}
</select>
<resultMap type="Classes" id="stuClasTeacher">
<id column="c_id" property="c_id"/>
<result column="c_name" property="c_name"/>
<!-- association 是实体类里面的类属性-->
<association property="teacher" javaType="Teacher" column="t_id">
<id column="t_id" property="t_id"/>
<result column="t_name" property="t_name"/>
</association>
<association property="student" javaType="Student" column="s_id">
<id column="s_id" property="s_id"/>
<result column="s_name" property="s_name"/>
</association>
</resultMap>
三、参数是集合的查询(类似于级联查询)
1、查询条件:根据学生的多个id查询学生信息,传入的参数是集合,就是做SQL查询时,需要循环进行查询时,利用参数是集合的方式,在SQL语句中就实现重复查询的作用
2、查询mapper映射文件的编写
<!--注意这里的SQL语句用的是in关键字,这里的的级联可以输入数组-->
<select id="selStuById" resultMap="Students">
SELECT * from student WHERE s_id in
<foreach collection="list" open="(" separator="," close=")" item="a">
#{a}
</foreach>
</select>
四、实体接口:
五、实体类:
1、班级实体类
public class Classes {
private int c_id;
private String c_name;
private Teacher teacher;
private Student student;
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 Student getStudent() {
return student;
}
public void setStudent(Student student) {
this.student = student;
}
public Classes() {
super();
}
@Override
public String toString() {
return "Classes [c_id=" + c_id + ", c_name=" + c_name + ", teacher="
+ teacher + ", student=" + student + "]";
}
}
2、老师实体类
public class Teacher {
private int t_id;
private String t_name;
private Classes classes;
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;
}
public Classes getClasses() {
return classes;
}
public void setClasses(Classes classes) {
this.classes = classes;
}
@Override
public String toString() {
return "Teacher [t_id=" + t_id + ", t_name=" + t_name + ", classes="
+ classes + "]";
}
public Teacher(int t_id, String t_name, Classes classes) {
super();
this.t_id = t_id;
this.t_name = t_name;
this.classes = classes;
}
public Teacher() {
super();
}
}
3、学生实体类:
public class Student {
private int s_id;
private String s_name;
private Classes classes;
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;
}
public Classes getClasses() {
return classes;
}
public void setClasses(Classes classes) {
this.classes = classes;
}
public Student(int s_id, String s_name, Classes classes) {
super();
this.s_id = s_id;
this.s_name = s_name;
this.classes = classes;
}
public Student() {
super();
}
@Override
public String toString() {
return "Student [s_id=" + s_id + ", s_name=" + s_name + ", classes="
+ classes + "]";
}
}