实验6 Mybatis关联映射

80 阅读 0 评论 53 点赞

我是靠谱客的博主受伤帆布鞋，最近开发中收集的这篇文章主要介绍实验6 Mybatis关联映射，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

一、实验目的

1、了解数据表之间的三种关联关系；

2、了解对象之间的三种关联关系；

3、熟悉关联关系中的嵌套查询与嵌套结果查询；

4、掌握一对一、一对多及多对多关联映射的实现方法。

二、实验内容

在Mybatis数据库中创建学生表（student）、课程表（course）、成绩表（score），并插入必要的测试数据，完成以下任务：

student表

属性	数据类型	长度	是否主键/外键	可空性	备注
sno	char	12	主键	否	学号
sname	varchar	10		否	姓名
age	tinyint				年龄
address	varchar	50			家庭住址

# 创建学生表，并插入三条数据
CREATE TABLE student(
   sno CHAR(12) PRIMARY KEY NOT NULL,
   sname VARCHAR(10) NOT NULL,
   age TINYINT,
   address VARCHAR(50)
);
INSERT INTO student VALUES('10001','张翔',18,'广东省');
INSERT INTO student VALUES('10002','李四',22,'湖北省');
INSERT INTO student VALUES('10003','张三',20,'福建省');

course表

属性	数据类型	长度	是否主键/外键	可空性	备注
cno	char	8	主键	否	课程号
cname	varchar	50		否	课程名
credit	tinyint				学分
introduction	varchar	500			课程简介

# 创建课程表，并插入三条数据
CREATE TABLE course(
   cno CHAR(8) PRIMARY KEY NOT NULL,
   cname VARCHAR(50) NOT NULL,
   credit TINYINT,
   introduction VARCHAR(500)
);
INSERT INTO course VALUES('101','高等数学',5,'高等数学是一门数学课');
INSERT INTO course VALUES('102','C语言程序设计',5,'C语言是基础编程课');
INSERT INTO course VALUES('103','大学英语',3,'大学英语是一门英语课');

score表

属性	数据类型	长度	是否主键/外键	可空性	备注
sno	char	12	主键、外键	否	学号
cno	varchar	50	主键、外键	否	课程号
score	tinyint				成绩
number	varchar	tinyint			第几次选修，默认值为1

# 创建成绩表，并插入三条数据
CREATE TABLE score(
   sno CHAR(12) PRIMARY KEY NOT NULL,
   cno CHAR(8) NOT NULL,
   FOREIGN KEY(sno) REFERENCES student(sno),
   FOREIGN KEY(cno) REFERENCES course(cno),
   score TINYINT,
   number VARCHAR(20)
);
INSERT INTO score VALUES('10001','102',99,'1');
INSERT INTO score VALUES('10002','101',99,'1');
INSERT INTO score VALUES('10003','103',99,'1');

# 在com.pojo.pojo1包下创建Course类：

package com.pojo.pojo1;

import java.util.List;

public class Course {

    private String cno;         //课程号
    private String cname;       //课程名
    private int credit;         //学分
    private String introduction;//课程简介

    private List<Score> scoreList;

    //只需要打印课程名和scoreList这个属性，所以就只重写cname和scoreList的toString方法即可
    @Override
    public String toString() {
        return "Course{" +
                "cname='" + cname + ''' +
                "scoreList=" + scoreList +
                '}';
    }


    public List<Score> getScoreList() {
        return scoreList;
    }

    public void setScoreList(List<Score> scoreList) {
        this.scoreList = scoreList;
    }

    public String getCno() {
        return cno;
    }

    public void setCno(String cno) {
        this.cno = cno;
    }

    public String getCname() {
        return cname;
    }

    public void setCname(String cname) {
        this.cname = cname;
    }
}

# 在com.pojo.pojo1包下创建Score类：

package com.pojo.pojo1;

public class Score {

    private String sno;     //学号
    private String cno;     //课程号
    private int score;      //成绩
    private String number;   //第几次选修，默认值为1

    //只需要打印分数这个属性，所以就只重写score的toString方法即可
    @Override
    public String toString() {
        return "Score{" +
                "score=" + score +
                '}';
    }

    public int getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public String getCno() {
        return cno;
    }

    public void setCno(String cno) {
        this.cno = cno;
    }

}

# 在com.pojo.pojo1包下创建Student类：

package com.pojo.pojo1;

import com.pojo.pojo1.Course;
import com.pojo.pojo1.Score;

import java.util.List;

public class Student {

    private String sno;     //学号
    private String sName;   //姓名
    private int age;        //年龄
    private String address; //家庭住址

    private List<Course> courseList;
    private List<Score> scoreList;

    @Override
    public String toString() {
        return "Student{" +
                "sno='" + sno + ''' +
                ", sName='" + sName + ''' +
                "courseList=" + courseList +
                '}';
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public String getsName() {
        return sName;
    }

    public void setsName(String sName) {
        this.sName = sName;
    }

    public List<Course> getCourseList() {
        return courseList;
    }

    public void setCourseList(List<Course> courseList) {
        this.courseList = courseList;
    }

    public List<Score> getScoreList() {
        return scoreList;
    }

    public void setScoreList(List<Score> scoreList) {
        this.scoreList = scoreList;
    }
}

# 在com.pojo.pojo2包下创建ScoreTwo类：

package com.pojo.pojo2;

public class ScoreTwo {

    private String sno;     //学号
    private String cno;     //课程号
    private double score;      //成绩
    private String number;   //第几次选修，默认值为1

    //只需要打印分数这个属性，所以就只重写score的toString方法即可
    @Override
    public String toString() {
        return "Score{" +
                "score=" + score +
                '}';
    }

    public double getScore() {
        return score;
    }

    public void setScore(int score) {
        this.score = score;
    }

    public String getCno() {
        return cno;
    }

    public void setCno(String cno) {
        this.cno = cno;
    }

}

# 在com.pojo.pojo2包下创建Student2类;

package com.pojo.pojo2;

import java.util.List;

public class Student2 {

    private String sno;     //学号
    private String sName;   //姓名
    private int age;        //年龄
    private String address; //家庭住址

    private List<ScoreTwo> scoreTwoList;

    @Override
    public String toString() {
        return "Student{" +
                "sno='" + sno + ''' +
                ", sName='" + sName + ''' +
                "scoreList=" + scoreTwoList +
                '}';
    }

    public String getSno() {
        return sno;
    }

    public void setSno(String sno) {
        this.sno = sno;
    }

    public String getsName() {
        return sName;
    }

    public void setsName(String sName) {
        this.sName = sName;
    }

    public List<ScoreTwo> getScore2List() {
        return scoreTwoList;
    }

    public void setScore2List(List<ScoreTwo> scoreTwoList) {
        this.scoreTwoList = scoreTwoList;
    }
}

# 在utils包下创建MybatisUtils工具类：

package utils;

import org.apache.ibatis.io.Resources;
import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.apache.ibatis.session.SqlSessionFactoryBuilder;

import java.io.IOException;
import java.io.Reader;

public class MyBatisUtils {

    private static SqlSessionFactory sqlSessionFactory = null;
    static {
        //创建sqlSessionFactory
        Reader reader = null;
        try {
            reader = Resources.getResourceAsReader("mybatis-config.xml");
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(reader);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static SqlSession getSession(){
        return sqlSessionFactory.openSession();
    }

}

# 在resources/Mapper目录下创建以下xml文件:

# 创建ScoreMapper.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:名称空间，由于映射文件可能有多个，为了防止crud语句的唯一标识
-->

<mapper namespace="mapper.ScoreMapper">
    <!--
        select:查询的statement(声明)，用来编写查询语句
        id:语句的唯一标识
        resultType:配置返回的结果集类型
        parameterType:传递的参数类型，可以省略
    -->

    <!--查询某个人的平均分-->
    <select id="findAverageScoreByName"
            resultMap="scoreResult">
        SELECT
            sno ,
            AVG(score)
        FROM
            score
        WHERE
            sno = (SELECT sno FROM student WHERE sname=#{sname})
        GROUP BY
            sno
    </select>

    <resultMap id="scoreResult" type="Score">
        <id property="sno" column="sno"></id>
        <result property="score" column="AVG(score)"></result>
    </resultMap>

    <!--查询某课程所有人的成绩，此方法是把student封装在score里面，然后用selectOne方法输出score-->
    <select id="findAllScoreByCourseName"
            resultMap="studentScoreResult">
        SELECT
            student.sname,
            score.score
        FROM
            student,
            score
        WHERE
            student.sno = score.sno
          AND
            score.cno in (SELECT cno FROM course WHERE cname=#{cname})
    </select>

    <resultMap id="studentScoreResult" type="com.pojo.pojo2.ScoreTwo">
        <result property="score" column="score"></result>
        <collection property="studentList"
                    javaType="List"
                    ofType="Student">
            <id property="sno" column="sno"></id>
            <result property="sName" column="sname"></result>
        </collection>
    </resultMap>

    <select id="findCourseAverage"
            resultType="com.pojo.pojo2.ScoreTwo">
        SELECT
            AVG(score) score
        FROM
            score
        WHERE
            cno = (SELECT cno FROM course WHERE cname=#{cname})
    </select>

</mapper>

# 创建StudentMapper.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:名称空间，由于映射文件可能有多个，为了防止crud语句的唯一标识
-->

<mapper namespace="mapper.StudentMapper">
    <!--
        select:查询的statement(声明)，用来编写查询语句
        id:语句的唯一标识
        resultType:配置返回的结果集类型
        parameterType:传递的参数类型，可以省略
    -->
    <select id="findAllScoreByName"
            resultMap="allResult">
        SELECT
            student.sname,
            student.sno,
            course.cno,
            course.cname,
            score.score
        FROM
            score,
            course,
            student
        WHERE
            score.sno = student.sno
          AND
            score.cno = course.cno
          AND
            student.sno in (select sno from student where sname=#{sname})
    </select>

    <resultMap id="allResult" type="Student">
        <!--查询Student的sno,sName-->
        <id column="sno" property="sno"></id>
        <result property="sName" column="sname"></result>
        <!--courseList的列，查询cno,cname这三个属性-->
        <collection property="courseList"
                    javaType="List"
                    ofType="course">
            <id property="cno" column="cno"></id>
            <result property="cname" column="cname"></result>
            <!--嵌套查询结果-->
            <!--scoreList的列，查询sno,cno,score这三个属性-->
            <collection property="scoreList"
                        javaType="List"
                        ofType="Score">
                <id property="sno" column="sno"></id>
                <id property="cno" column="cno"></id>
                <result property="score" column="score"></result>
            </collection>
        </collection>
    </resultMap>

    <!--查询某课程所有人的成绩-->
    <select id="findAllScoreByCourseName"
            resultMap="studentCourseScoreResult">
        SELECT
            student.sno,
            student.sname,
            score.score
        FROM
            student,
            score
        WHERE
            student.sno = score.sno
          AND
            score.cno in (SELECT cno FROM course WHERE cname=#{cname})
    </select>
    <resultMap id="studentCourseScoreResult" type="Student2">
        <!--查询Student的sno,sname-->
        <id property="sno" column="sno"></id>
        <result property="sName" column="sname"></result>
            <collection property="scoreTwoList"
                        javaType="list"
                        ofType="Score">
                <result property="score" column="score"></result>
            </collection>
    </resultMap>

</mapper>

# 在resources下创建以下xml文件：

# 创建db,properties文件（用户名和密码需要更换为你们自己的）：

mysql.driver=com.mysql.cj.jdbc.Driver
mysql.url=jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC&
     characterEncoding=utf8&useUnicode=true&useSSL=false
mysql.username=root
mysql.password=123456

# 创建Mybatis-config.xml文件：

<?xml version="1.0" encoding="utf-8" ?>
<!DOCTYPE configuration
        PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <!--加载配置-->
    <!--加载类路径下的属性文件-->
    <properties resource="db.properties"/>

    <!--别名映射-->
    <typeAliases>
        <!--第一种配置方式-->
        <!--        <typeAlias type="com.itheima.com.pojo.Person" alias="person"></typeAlias>-->
        <!--第二种配置方式-->
        <package name="com.pojo"/>
    </typeAliases>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"></transactionManager>
            <!--数据库连接相关配置，db.properties文件中的内容-->
            <dataSource type="POOLED">
                <property name="driver" value="${mysql.driver}"/>
                <property name="url" value="${mysql.url}"/>
                <property name="username" value="${mysql.username}"/>
                <property name="password" value="${mysql.password}"/>
            </dataSource>
        </environment>
    </environments>


    <mappers>
        <mapper resource="mapper/StudentMapper.xml"></mapper>
        <mapper resource="mapper/ScoreMapper.xml"></mapper>
    </mappers>

</configuration>

# pom.xml文件

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0"
         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
         xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>

    <groupId>com.itheima</groupId>
    <artifactId>mybatistest</artifactId>
    <version>1.0-SNAPSHOT</version>

    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.2</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.11</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.12</version>
            <!--   	        <scope>test</scope>-->
            <scope>compile</scope>
        </dependency>

        <dependency>
            <groupId>log4j</groupId>
            <artifactId>log4j</artifactId>
            <version>1.2.17</version>
        </dependency>

    </dependencies>
    <build>
        <resources>
            <resource>
                <directory>src/main/java</directory>
                <includes>
                    <include>**/*.properties</include>
                    <include>**/*.xml</include>
                </includes>
                <filtering>true</filtering>
            </resource>
        </resources>
        <plugins>
            <plugin>
                <groupId>org.apache.maven.plugins</groupId>
                <artifactId>maven-compiler-plugin</artifactId>
                <configuration>
                    <source>7</source>
                    <target>7</target>
                </configuration>
            </plugin>
        </plugins>
    </build>

</project>

查找张翔选修的全部课程成绩及其平均成绩；
查询选修了《Java EE程序设计》课程的所有学生成绩及该课程的平均成绩；

# 创建MyBatisTest测试类：

import com.pojo.pojo1.Score;
import com.pojo.pojo1.Student;
import com.pojo.pojo2.ScoreTwo;
import org.apache.ibatis.session.SqlSession;
import org.junit.Test;
import utils.MyBatisUtils;

public class MyBatisTest {

    @Test
    public void findAllScoreByName(){

        SqlSession session = MyBatisUtils.getSession();

        Student student = session.selectOne("mapper.StudentMapper.findAllScoreByName", "张翔");

        System.out.println(student);

        session.close();

    }

    @Test
    public void findAverageScoreByName(){

        SqlSession session = MyBatisUtils.getSession();

        String sName = "张翔";
        Score score = session.selectOne("mapper.ScoreMapper.findAverageScoreByName", sName);

        System.out.println(sName+"所有成绩的平均分为"+score);

        session.close();

    }

    @Test
    public void findAllScoreByCourseName(){

        SqlSession session = MyBatisUtils.getSession();

        String courseName = "JavaEE程序设计";

        Object student = session.selectList("mapper.StudentMapper.findAllScoreByCourseName", courseName);

        System.out.println(courseName + "所有学生的成绩：" + student);

        session.close();

    }

    @Test
    public void findCourseAverage(){

        SqlSession session = MyBatisUtils.getSession();

        String courseName = "JavaEE程序设计";

        ScoreTwo score2 = session.selectOne("mapper.ScoreMapper.findCourseAverage", courseName);

        System.out.println(courseName + "的平均分为" + score2);

        session.close();


    }

}