一、创建从实体学生类到课程类的关联映射,实现对两表关联数据的查询。——使用mapper.xml绑定接口
1、实体类
- 课程类
- 学生类
package wzu.pojo;
public class course {
private int cno;
private String cname;
private int cpno;
private int ccredit;
public course(){
}
public course(int cno, String cname, int cpno, int ccredit) {
this.cno = cno;
this.cname = cname;
this.cpno = cpno;
this.ccredit = ccredit;
}
public String toString1() {
return "course{" +
"cno=" + cno +
", cname='" + cname + '\'' +
'}';
}
public String toString2() {
return "course{" +
"cno=" + cno +
", cname='" + cname + '\'' +
", ccredit=" + ccredit +
'}';
}
@Override
public String toString() {
return "course{" +
"cno=" + cno +
", cname='" + cname + '\'' +
", cpno=" + cpno +
", ccredit=" + ccredit +
'}';
}
public int getCno() {
return cno;
}
public void setCno(int cno) {
this.cno = cno;
}
public String getCname() {
return cname;
}
public void setCname(String cname) {
this.cname = cname;
}
public int getCpno() {
return cpno;
}
public void setCpno(int cpno) {
this.cpno = cpno;
}
public int getCcredit() {
return ccredit;
}
public void setCcredit(int ccredit) {
this.ccredit = ccredit;
}
}
package wzu.pojo;
import java.util.List;
public class student {
private int sno;
private String sname;
private String ssex;
private int sage;
private String sdept;
private List<course> courseList;
public String toString1() {
return "student{" +
"sno=" + sno +
", sname='" + sname + '\'' +
", ssex='" + ssex + '\'' +
", sage=" + sage +
", sdept='" + sdept + '\'' +
'}';
}
@Override
public String toString() {
return "student{" +
"sno=" + sno +
", sname='" + sname + '\'' +
", ssex='" + ssex + '\'' +
", sage=" + sage +
", sdept='" + sdept + '\'' +
", courseList=" + courseList +
'}';
}
public int getSno() {
return sno;
}
public void setSno(int sno) {
this.sno = sno;
}
public String getSname() {
return sname;
}
public void setSname(String sname) {
this.sname = sname;
}
public String getSsex() {
return ssex;
}
public void setSsex(String ssex) {
this.ssex = ssex;
}
public int getSage() {
return sage;
}
public void setSage(int sage) {
this.sage = sage;
}
public String getSdept() {
return sdept;
}
public void setSdept(String sdept) {
this.sdept = sdept;
}
public List<course> getCourseList() {
return courseList;
}
public void setCourseList(List<course> courseList) {
this.courseList = courseList;
}
}
2、学生映射类
package wzu.mapper;
import org.apache.ibatis.annotations.Param;
import wzu.pojo.student;
import java.util.List;
public interface studentMapper {
student selBySno(@Param("sno") int sno);
List<student> selBySname(@Param("sname") String sname);
List<student> selByCno(@Param("cno") int cno);
}
3、学生映射文件(SQL配置)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="wzu.mapper.studentMapper">
<!-- result property=‘wzu.pojo.student的属性’ -->
<!-- (1)查询学号为xxxx学生的信息及选课情况(课程号及课程名);-->
<resultMap id="selBySnoResult" type="wzu.pojo.student" >
<id property="sno" column="sno"/>
<result property="sname" column="sname"/>
<result property="ssex" column="ssex"/>
<result property="sage" column="sage"/>
<result property="sdept" column="sdept"/>
<collection property="courseList" ofType="wzu.pojo.course">
<id property="cno" column="cno"/>
<!-- <result property="cno" column="cno"/>-->
<result property="cname" column="cname"/>
</collection>
</resultMap>
<select id="selBySno" parameterType="int" resultMap="selBySnoResult">
SELECT stu.*,c.cno as cno,c.cname as cname
FROM student stu,sc,course c
where stu.sno='${sno}' and stu.sno=sc.sno and sc.cno=c.cno ;
</select>
<!-- (2)查询xxxx姓学生的信息及选课情况(课程号,课程名,课程学分);-->
<resultMap id="selBySnameResult" type="wzu.pojo.student" >
<id property="sno" column="sno"/>
<result property="sname" column="sname"/>
<result property="ssex" column="ssex"/>
<result property="sage" column="sage"/>
<result property="sdept" column="sdept"/>
<collection property="courseList" ofType="wzu.pojo.course">
<id property="cno" column="cno"/>
<result property="cname" column="cname"/>
<result property="ccredit" column="ccredit"/>
</collection>
</resultMap>
<select id="selBySname" parameterType="String" resultMap="selBySnameResult">
SELECT stu.*,c.cno as cno,c.cname as cname,c.ccredit as ccredit
FROM student stu,sc,course c
where stu.sname like '${sname}%' and stu.sno=sc.sno and sc.cno=c.cno ;
</select>
<!-- (3)查询选修了xxxx号课程的学生信息及课程名。-->
<resultMap id="selByCnoResult" type="wzu.pojo.student" >
<id property="sno" column="sno"/>
<result property="sname" column="sname"/>
<result property="ssex" column="ssex"/>
<result property="sage" column="sage"/>
<result property="sdept" column="sdept"/>
<collection property="courseList" ofType="wzu.pojo.course">
<id property="cno" column="cno"/>
<result property="cname" column="cname"/>
</collection>
</resultMap>
<select id="selByCno" parameterType="int" resultMap="selByCnoResult">
SELECT stu.*,c.cname as cname
FROM student stu,sc,course c
where sc.cno=#{cno} and stu.sno=sc.sno and sc.cno=c.cno ;
</select>
</mapper>
4、Util 类
5、核心配置文件
6、测试类
import org.junit.After; import org.junit.Before; import org.junit.Test;
public class studentMapperTest { SqlSession sqlSession; studentMapper mapper; @Before public void init(){ sqlSession = Util.createSqlSession(); mapper = sqlSession.getMapper(studentMapper.class); } @Test public void selBySno(){ student stu=mapper.selBySno(201215121); System.out.println("学生信息:"+stu.toString1()); for(course c:stu.getCourseList()){ System.out.println("该学生选课:"+c.toString1()); } } @After public void end(){ Util.closeSqlSession(sqlSession); } }
package wzu.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import wzu.Util;
import wzu.mapper.studentMapper;
import wzu.pojo.course;
import wzu.pojo.student;
import java.util.List;
public class studentMapperTest {
SqlSession sqlSession;
studentMapper mapper;
@Before
public void init(){
sqlSession = Util.createSqlSession();
mapper = sqlSession.getMapper(studentMapper.class);
}
// (1)查询学号为201215121学生的信息及选课情况(课程号及课程名);
@Test
public void selBySno(){
student stu=mapper.selBySno(201215121);
System.out.println("学生信息:"+stu.toString1());
for(course c:stu.getCourseList()){
System.out.println("该学生选课:"+c.toString1());
}
}
// (2)查询李姓学生的信息及选课情况(课程号,课程名,课程学分);
@Test
public void selBySname(){
List<student> stu=mapper.selBySname("李");
for(student s:stu){
System.out.println("学生信息:"+s.toString1());
for(course c:s.getCourseList()){
System.out.println("选课情况:"+c.toString2());
}
}
}
// (3)查询选修了2号课程的学生信息及课程名。
@Test
public void selByCno() {
List<student> stu = mapper.selByCno(2);
for (student s : stu) {
System.out.println("学生信息:" + s.toString1());
for (course c : s.getCourseList()) {
System.out.println("选课情况:" + c.getCname());
}
}
}
@After
public void end(){
Util.closeSqlSession(sqlSession);
}
}
二、创建从实体课程类到学生类的关联映射,实现对两表关联数据的查询。——使用mapper.xml绑定接口
文件结构,同上题。
这三个 不一样。。。。
1、实体映射(接口)类
2、对应 实体映射文件(SQL配置)
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"https://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="wzu.mapper.studentMapper">
<!-- (1)查询xxxx号课程信息及选修该课程的学生信息;-->
<resultMap id="selByCnoResult" type="wzu.pojo.course" >
<id property="cno" column="cno"/>
<result property="cname" column="cname"/>
<result property="cpno" column="cpno"/>
<result property="ccredit" column="ccredit"/>
<collection property="studentList" ofType="wzu.pojo.student">
<id property="sno" column="sno"/>
<result property="sname" column="sname"/>
<result property="ssex" column="ssex"/>
<result property="sage" column="sage"/>
<result property="sdept" column="sdept"/>
</collection>
</resultMap>
<select id="selByCno" parameterType="int" resultMap="selByCnoResult">
SELECT c.*,stu.*
FROM student stu,sc,course c
where sc.cno=#{cno} and stu.sno=sc.sno and sc.cno=c.cno ;
</select>
<!-- (2)模糊查询课程名中包含“计算”的课程信息及选修对应课程的学生信息;-->
<resultMap id="selByCnameResult" type="wzu.pojo.course" >
<id property="cno" column="cno"/>
<result property="cname" column="cname"/>
<result property="cpno" column="cpno"/>
<result property="ccredit" column="ccredit"/>
<collection property="studentList" ofType="wzu.pojo.student">
<id property="sno" column="sno"/>
<result property="sname" column="sname"/>
<result property="ssex" column="ssex"/>
<result property="sage" column="sage"/>
<result property="sdept" column="sdept"/>
</collection>
</resultMap>
<select id="selByCname" parameterType="String" resultMap="selByCnameResult">
SELECT c.*,stu.*
FROM student stu,sc,course c
where c.cname like '%${cname}%' and stu.sno=sc.sno and sc.cno=c.cno ;
</select>
<!-- (3)查询计算机系(CS)学生选修的课程信息。-->
<resultMap id="selBysdeptResult" type="wzu.pojo.course" >
<id property="cno" column="cno"/>
<result property="cname" column="cname"/>
<result property="cpno" column="cpno"/>
<result property="ccredit" column="ccredit"/>
</resultMap>
<select id="selBysdept" parameterType="String" resultMap="selBysdeptResult">
select course.* from
(SELECT distinct sc.cno as cno
FROM student stu,sc
where stu.sdept = '${sdept}' and stu.sno=sc.sno) as B left join course
on course.cno=B.cno;
</select>
</mapper>
3、测试类
package wzu.test;
import org.apache.ibatis.session.SqlSession;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import wzu.Util;
import wzu.mapper.studentMapper;
import wzu.pojo.course;
import wzu.pojo.student;
import java.util.List;
public class studentMapperTest {
SqlSession sqlSession;
studentMapper mapper;
@Before
public void init(){
sqlSession = Util.createSqlSession();
mapper = sqlSession.getMapper(studentMapper.class);
}
// (1)查询3号课程信息及选修该课程的学生信息;
@Test
public void selByCno() {
course c = mapper.selByCno(3);
System.out.println("课程信息:"+c.toString1());
for (student s :c.getStudentList()) {
System.out.println("学生信息:" + s.toString());
}
}
// (2)模糊查询课程名中包含“计算”的课程信息及选修对应课程的学生信息;
@Test
public void selByCname(){
List<course> cList=mapper.selByCname("计算");
for(course c:cList){
System.out.println("课程信息:"+c.toString1());
for(student s:c.getStudentList()){
System.out.println("学生信息:" + s.toString());
}
}
}
// (3)查询计算机系(CS)学生选修的课程信息。;
@Test
public void selBysdept(){
List<course> cList=mapper.selBysdept("CS");
System.out.println("查询计算机系(CS)学生选修的课程信息:");
for(course c:cList){
System.out.println(c.toString1());
}
}
@After
public void end(){
Util.closeSqlSession(sqlSession);
}
}
总结
。。。。