实验五. 多表关联映射

Jay_27

已于 2023-12-28 01:05:23 修改

阅读量13

点赞数

分类专栏： SSM 文章标签： java mybatis mysql

于 2023-05-31 22:13:43 首次发布

本文链接：https://blog.csdn.net/dsbss/article/details/130977876

版权

SSM 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

一、创建从实体学生类到课程类的关联映射，实现对两表关联数据的查询。——使用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);
    }

}