1、数据库准备工作
--删除表
drop table SelectiveInfo;
drop table StudentInfo;
drop table ClassInfo;
drop table TeacherInfo;
drop table CourseInfo;
drop table DepartmentInfo;
--创建部门表
create table DepartmentInfo(
departmentCode varchar2(5),
departmentName varchar2(64) unique,
constraint PK_DepartmentInfo primary key(departmentCode));
--创建课程信息表
create table CourseInfo(
courseID varchar2(8),
courseName varchar2(32) not null,
departmentCode varchar2(5) not null,
credit number(3,1) not null,
resume varchar2(255),
constraint PK_CourseInfo primary key(courseID),
constraint FK_Cdepartment foreign key(departmentCode)
references DepartmentInfo(departmentCode));
--创建教师信息表
create table TeacherInfo(
teacherID varchar2(18),
teacherName varchar2(32) not null,
departmentCode varchar2(5) not null,
sex char(1) not null,
tel varchar2(20),
fax varchar2(20),
teacherBiref varchar2(512),
constraint PK_TeacherInfo primary key(teacherID),
constraint FK_departmentCode foreign key(departmentCode)
references DepartmentInfo(departmentCode));
--创建排课信息表
create table ClassInfo(
classID number(10),
className varchar2(32) not null,
teacherID varchar2(18) not null,
courseID varchar2(8) not null,
classYear varchar2(6) not null,
classTimePlace varchar2(128),
constraint PK_ClassInfo primary key(classID),
constraint FK_courseID foreign key(courseID)
references CourseInfo(courseID),
constraint FK_teacherID foreign key(teacherID)
references TeacherInfo(teacherID));
--创建学生信息表
create table StudentInfo(
studentID varchar2(18),
studentName varchar2(32) not null,
sex char(1) not null,
departmentCode varchar2(5) not null,
address varchar2(512),
tel varchar2(20),
fax varchar2(20),
constraint PK_StudentInfo primary key(studentID),
constraint FK_department foreign key(departmentCode)
references DepartmentInfo(departmentCode));
--创建选课信息表
create table SelectiveInfo(
studentID varchar2(18) not null,
classID number(10) not null,
grade number(4,1),
selectiveDate date,
status char(1) default 'n',
constraint PK_SelectiveInfo primary key(studentID,classID),
constraint FK_classID foreign key(classID)
references ClassInfo(classID));
--添加部门代码信息记录
insert into DepartmentInfo values('101','计算机学院');
insert into DepartmentInfo values('201','软件学院');
insert into DepartmentInfo values('301','工商管理学院');
insert into DepartmentInfo values('401','人文学院');
--添加课程信息记录
insert into CourseInfo values('1010001','database manage','201',3.5,'以oralce数据库为例,着重数据库管理基础');
insert into CourseInfo values('1010002','database development','201',2.5,'以oralce数据库为例,着重数据库开发基础');
insert into CourseInfo values('2010001','C++ development','101',3.5,'讲授面向对象编程');
insert into CourseInfo values('2010002','JSP Application','101',1.5,'是Dcampus-0220的后续课程');
insert into CourseInfo values('2010003','Dcampus Enterprise JavaBean','101',2.5,'讲授EJB原理');
--添加教师信息
insert into TeacherInfo values('199910110002','angelo smith','101','m','020-87119321','020-87113912','he is a good man');
insert into TeacherInfo values('200210110002','angel rose','101','f','020-87113433','020-87134952','she is a good woman');
insert into TeacherInfo values('200110330002','mark stone','201','m','020-87119321','020-87113912','he is a angel husband');
insert into TeacherInfo values('199910123002','angelo stone','101','s','020-87145521','020-87134912','he is a bad man');
insert into TeacherInfo values('200310123044','wu da wei','301','f','020-87134421','020-87154412','he is a bad man');
insert into TeacherInfo values('200234234099','wang fei','401','f','020-87123231','020-87134412','he is a bad man');
--添加排课信息表
insert into ClassInfo values(1,'oracle DBA','199910110002','1010001','2002下','monday 27101,tuesday 12204');
insert into ClassInfo values(2,'oracle Advance DBA','199910110002','1010001','2002下','friday 27101,sunday 12204');
insert into ClassInfo values(3,'C++ fundament Application','200110330002','2010001','2002上','friday 27121,tuesday 13404');
insert into ClassInfo values(4,'C++ Advance Application','199910123002','2010001','2003下','monday 27101,satuday 2704');
--添加学生信息
insert into StudentInfo values('2002100230221','liu doudou','m','101','he is a good student','020-45449321','020-45113912');
insert into StudentInfo values('2002100230765','wu wei dong','f','101','she is a good student','020-45443441','020-45176562');
insert into StudentInfo values('2002100345876','he meihua','f','101','she is a good girl','021-45434321','022-45113912');
insert into StudentInfo values('2002100785446','he zhuanzhuan','m','201','this is a joke','023-45433333','025-45143342');
insert into StudentInfo values('2002100343336','tian dehua','m','201','this is a big joke','023-4543334','024-43553342');
insert into StudentInfo values('2002343333336','zhang lili','s','201','this is a smill joke','053-4673334','052-43666342');
insert into StudentInfo values('2002342343445','zhang guorong','m','301','this is a smill joke','053-46343334','052-42326342');
insert into StudentInfo values('2002323423434','mei yanfang','f','401','this is a smill joke','053-46324334','052-42877742');
--添加选课信息
insert into SelectiveInfo values('2002343333336',1,67,to_date('2010-12-2','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002343333336',2,87,to_date('2010-12-2','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100230765',1,98,to_date('2010-12-3','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100230765',3,88,to_date('2010-12-4','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100345876',1,83,to_date('2010-12-1','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100345876',4,71,to_date('2010-12-3','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100785446',3,85,to_date('2010-11-30','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100785446',2,74,to_date('2010-11-28','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100230221',1,61,to_date('2010-12-3','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100230221',2,58,to_date('2010-12-4','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100230221',3,69,to_date('2010-12-5','yyyy-mm-dd'),'n');
insert into SelectiveInfo values('2002100343336',2,55,to_date('2010-12-2,15:30:44','yyyy-mm-dd,hh24:mi:ss'),'n');
insert into SelectiveInfo values('2002100343336',4,66,sysdate,'n');
2、编写JavaBean(可跳过此步查阅第三步)
DepartmentInfoVo.java
import java.util.ArrayList;
import com.luo.ssm.po.StudentInfo;
public class DepartmentInfoVo {
private String DEPARTMENTCODE;
private String DEPARTMENTNAME;
private ArrayList<StudentInfo> stuList;
public ArrayList<StudentInfo> getStuList() {
return stuList;
}
public void setStuList(ArrayList<StudentInfo> stuList) {
this.stuList = stuList;
}
public String getDEPARTMENTCODE() {
return DEPARTMENTCODE;
}
public void setDEPARTMENTCODE(String dEPARTMENTCODE) {
DEPARTMENTCODE = dEPARTMENTCODE;
}
public String getDEPARTMENTNAME() {
return DEPARTMENTNAME;
}
public void setDEPARTMENTNAME(String dEPARTMENTNAME) {
DEPARTMENTNAME = dEPARTMENTNAME;
}
@Override
public String toString() {
return "DepartmentInfoVo [DEPARTMENTCODE=" + DEPARTMENTCODE + ", DEPARTMENTNAME=" + DEPARTMENTNAME
+ ", stuList=" + stuList + "]";
}
}
StudentInfo.java
public class StudentInfo {
private String STUDENTID;
private String STUDENTNAME;
private char SEX;
private String DEPARTMENTCODE;
private String ADDRESS;
private String TEL;
private String FAX;
private SelectiveInfo selectiveInfo;
public String getSTUDENTID() {
return STUDENTID;
}
public void setSTUDENTID(String sTUDENTID) {
STUDENTID = sTUDENTID;
}
public String getSTUDENTNAME() {
return STUDENTNAME;
}
public void setSTUDENTNAME(String sTUDENTNAME) {
STUDENTNAME = sTUDENTNAME;
}
public char getSEX() {
return SEX;
}
public void setSEX(char sEX) {
SEX = sEX;
}
public String getDEPARTMENTCODE() {
return DEPARTMENTCODE;
}
public void setDEPARTMENTCODE(String dEPARTMENTCODE) {
DEPARTMENTCODE = dEPARTMENTCODE;
}
public String getADDRESS() {
return ADDRESS;
}
public void setADDRESS(String aDDRESS) {
ADDRESS = aDDRESS;
}
public String getTEL() {
return TEL;
}
public void setTEL(String tEL) {
TEL = tEL;
}
public String getFAX() {
return FAX;
}
public void setFAX(String fAX) {
FAX = fAX;
}
@Override
public String toString() {
return "StudentInfo [STUDENTID=" + STUDENTID + ", STUDENTNAME=" + STUDENTNAME + ", SEX=" + SEX
+ ", DEPARTMENTCODE=" + DEPARTMENTCODE + ", ADDRESS=" + ADDRESS + ", TEL=" + TEL + ", FAX=" + FAX
+ ", selectiveInfo=" + selectiveInfo + "]";
}
}
SelectiveInfo.java
public class SelectiveInfo {
private String STUDENTID;
private String CLASSID;
private String GRADE;
private String SELECTIVEDATE;
private String STATUS;
private Classinfo classinfo;
public String getSTUDENTID() {
return STUDENTID;
}
public void setSTUDENTID(String sTUDENTID) {
STUDENTID = sTUDENTID;
}
public String getCLASSID() {
return CLASSID;
}
public void setCLASSID(String cLASSID) {
CLASSID = cLASSID;
}
public String getGRADE() {
return GRADE;
}
public void setGRADE(String gRADE) {
GRADE = gRADE;
}
public String getSELECTIVEDATE() {
return SELECTIVEDATE;
}
public void setSELECTIVEDATE(String sELECTIVEDATE) {
SELECTIVEDATE = sELECTIVEDATE;
}
public String getSTATUS() {
return STATUS;
}
public void setSTATUS(String sTATUS) {
STATUS = sTATUS;
}
public Classinfo getClassinfo() {
return classinfo;
}
public void setClassinfo(Classinfo classinfo) {
this.classinfo = classinfo;
}
@Override
public String toString() {
return "SelectiveInfo [STUDENTID=" + STUDENTID + ", CLASSID=" + CLASSID + ", GRADE=" + GRADE
+ ", SELECTIVEDATE=" + SELECTIVEDATE + ", STATUS=" + STATUS + ", classinfo=" + classinfo + "]";
}
}
ClassInfo.java
public class ClassInfo {
private String CLASSID;
private String CLASSNAME;
private String TEACHERID;
private String COURSEID;
private String CLASSYEAR;
private String CLASSTIMEPLACE;
public String getCLASSID() {
return CLASSID;
}
public void setCLASSID(String cLASSID) {
CLASSID = cLASSID;
}
public String getCLASSNAME() {
return CLASSNAME;
}
public void setCLASSNAME(String cLASSNAME) {
CLASSNAME = cLASSNAME;
}
public String getTEACHERID() {
return TEACHERID;
}
public void setTEACHERID(String tEACHERID) {
TEACHERID = tEACHERID;
}
public String getCOURSEID() {
return COURSEID;
}
public void setCOURSEID(String cOURSEID) {
COURSEID = cOURSEID;
}
public String getCLASSYEAR() {
return CLASSYEAR;
}
public void setCLASSYEAR(String cLASSYEAR) {
CLASSYEAR = cLASSYEAR;
}
public String getCLASSTIMEPLACE() {
return CLASSTIMEPLACE;
}
public void setCLASSTIMEPLACE(String cLASSTIMEPLACE) {
CLASSTIMEPLACE = cLASSTIMEPLACE;
}
@Override
public String toString() {
return "Classinfo [CLASSID=" + CLASSID + ", CLASSNAME=" + CLASSNAME + ", TEACHERID=" + TEACHERID + ", COURSEID="
+ COURSEID + ", CLASSYEAR=" + CLASSYEAR + ", CLASSTIMEPLACE=" + CLASSTIMEPLACE + "]";
}
}
3、编写StuMapper.java接口方法
public interface StuMapper {
//一对多查询
public List<DepartmentInfoVo> departmentInfoVoList(String DEPARTMENTCODE);
//多对多查询
public List<Object> selectNtoN(String DEPARTMENTCODE);
}
4、编写StuMapper.xml映射文件
注意映射文件<mapper>标签的namespace属性须填写同名接口的全类名。
1.collection用于一对多的关系,而association是用于一对一和多对一,对此有疑问者可查阅详解Mybatis中collection和association的使用区别。
2.javaType和ofType都可以用来指定对象类型,但javaType用来指定pojo中属性的类型,而ofType则用来指定映射到list集合中pojo的属性类型。
3.一对多关系:一个部门对应着对多个学生。
4.多对多关系:一个部门对应着多个学生,一个学生对应着多个选课,一个选课对应着一个课程排课。
<?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.luo.ssm.dao.StuMapper">
<!-- 一对多查询(1) -->
<resultMap type="com.luo.ssm.po.DepartmentInfoVo" id="departmentInfoVoMapper">
<id column="DEPARTMENTCODE" property="DEPARTMENTCODE"/>
<result column="DEPARTMENTNAME" property="DEPARTMENTNAME"/>
<collection property="stuList" ofType="com.luo.ssm.po.StudentInfo">
<id column="STUDENTID" property="STUDENTID"/>
<result column="STUDENTNAME" property="STUDENTNAME"/>
<result column="SEX" property="SEX"/>
<result column="DEPARTMENTCODE" property="DEPARTMENTCODE"/>
<result column="ADDRESS" property="ADDRESS"/>
<result column="TEL" property="TEL"/>
<result column="FAX" property="FAX"/>
</collection>
</resultMap>
<!-- 一对多查询(2) collection:一对多 association:多对一,一对一-->
<select id="departmentInfoVoList" parameterType="String" resultMap="departmentInfoVoMapper" >
SELECT s.STUDENTID,s.STUDENTNAME,d.DEPARTMENTCODE,d.DEPARTMENTNAME FROM STUDENTINFO s,DEPARTMENTINFO d where s.DEPARTMENTCODE=#{DEPARTMENTCODE} and d.DEPARTMENTCODE=#{DEPARTMENTCODE}
</select>
<!-- 多对多查询(1) -->
<resultMap type="com.luo.ssm.po.DepartmentInfoVo" id="selectNtoNresultMap">
<result column="DEPARTMENTCODE" property="DEPARTMENTCODE"/>
<result column="DEPARTMENTNAME" property="DEPARTMENTNAME"/>
<collection property="stuList" ofType="com.luo.ssm.po.StudentInfo">
<result column="STUDENTID" property="STUDENTID"/>
<result column="STUDENTNAME" property="STUDENTNAME"/>
<collection property="selectiveInfo" ofType="com.luo.ssm.po.SelectiveInfo" >
<result column="CLASSID" property="CLASSID"/>
<result column="GRADE" property="GRADE"/>
<result column="SELECTIVEDATE" property="SELECTIVEDATE"/>
<association property="classinfo" javaType="com.luo.ssm.po.ClassInfo">
<result column="CLASSNAME" property="CLASSNAME"/>
<result column="CLASSID" property="CLASSID"/>
<result column="CLASSTIMEPLACE" property="CLASSTIMEPLACE"/>
</association>
</collection>
</collection>
</resultMap>
<!-- 多对多查询(2) -->
<select id="selectNtoN" resultMap="selectNtoNresultMap">
SELECT d.DEPARTMENTCODE,
d.DEPARTMENTNAME,
stu.STUDENTID,
stu.STUDENTNAME,
sel.CLASSID,
sel.GRADE,
sel.SELECTIVEDATE,
c.CLASSID,
c.CLASSNAME,
c.CLASSTIMEPLACE
FROM
DEPARTMENTINFO d,
STUDENTINFO stu,
SELECTIVEINFO sel,
CLASSINFO c
where
d.DEPARTMENTCODE=#{DEPARTMENTCODE}
AND stu.DEPARTMENTCODE=#{DEPARTMENTCODE}
AND stu.STUDENTID=sel.STUDENTID
AND sel.CLASSID=c.CLASSID
<!-- 末尾添加分号会报错 -->
</select>
</mapper>
5、编写测试类
SelectTest.java
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.springframework.beans.factory.BeanFactory;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import com.luo.ssm.dao.StuMapper;
import com.luo.ssm.po.DepartmentInfoVo;
public class SelectTest {
public static void main(String[] args) {
try {
BeanFactory beanFactory = new ClassPathXmlApplicationContext("spring/applicationContext.xml");
SqlSessionFactory sqlSessionFactory=(SqlSessionFactory) beanFactory.getBean("sqlSessionFactory");
SqlSession session=sqlSessionFactory.openSession();
/* //一对多查询
String DEPARTMENTCODE="101";
List<DepartmentInfoVo> list=session.selectList("departmentInfoVoList",DEPARTMENTCODE);
for (Object object : list) {
System.out.println(object.toString());
} */
//多对多查询
String DEPARTMENTCODE="101";
List<Object> list=session.selectList("selectNtoN",DEPARTMENTCODE);
for (Object object : list) {
System.out.println(object.toString());
}
} catch (Exception e) {
e.printStackTrace();
}
}
}
5、执行测试类
数据库查询结果
注意:测试类打印输出时,只输出对应的STUDENTID信息的最后一个,即每个STUDENTID仅输出一次,如需输出以下数据库结果需做特殊处理,需将 StudentInfo.java 中 private SelectiveInfo selectiveInfo 及 SelectiveInfo.java 中 private Classinfo classinfo 映射到list集合中pojo的属性类型,如:DepartmentInfoVo 中的 private ArrayList<StudentInfo> stuList。