使用mybatis对学生表和成绩表进行一对多关联查询（查找序号为2的学生信息和各项成绩）

最新推荐文章于 2025-11-17 19:27:58 发布

原创最新推荐文章于 2025-11-17 19:27:58 发布 · 687 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #数据库

首先在自己的本地数据库中创建student表和score表

CREATE TABLE IF NOT EXISTS student (

    id INT(10) AUTO_INCREMENT PRIMARY KEY,  -- 学号，主键，自增

    name VARCHAR(32) NOT NULL,              -- 学生姓名

    age TINYINT(3) UNSIGNED NOT NULL CHECK (age BETWEEN 1 AND 150), -- 年龄，范围 1~150

    gender VARCHAR(10) NOT NULL CHECK (gender IN ('male', 'female')) -- 性别，限定 male 或 female

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS score (

    id INT(10) AUTO_INCREMENT PRIMARY KEY,  -- 成绩id，主键，自增

    name VARCHAR(32) NOT NULL,              -- 课程名

    stu_id INT(10) NOT NULL,                -- 学生id，引用 student 表

    score FLOAT NOT NULL CHECK (score >= 0 AND score <= 100), -- 成绩，0 到 100 分数范围

    CONSTRAINT fk_stu_id FOREIGN KEY (stu_id) REFERENCES student(id)

    ON DELETE CASCADE                       -- 外键约束，引用 student 表的 id，删除学生时删除成绩

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

在表里插入数据

INSERT INTO student (id, name, age, gender)

VALUES

(1, '张军', 20, 'male'),

(2, '李兰', 21, 'female');

INSERT INTO score (id, name, stu_id, score)

VALUES

(1, 'c语言', 1, 81),

(2, 'Java程序设计', 1, 75),

(3, 'Web前端', 1, 73),

(4, '对象关系映射技术', 1, 76),

(5, 'c语言', 2, 82),

(6, 'Java程序设计', 2, 71),

(7, 'Web前端', 2, 78),

(8, '对象关系映射技术', 2, 76);

创建实体类student和score

student类：

package org.example.entity;

import java.util.List;

public class Student {
    private Integer id;        // 学号
    private String name;       // 姓名
    private Integer age;       // 年龄
    private String gender;     // 性别
    private List<Score> scores; // 成绩列表

    // Getters and Setters
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public String getGender() {
        return gender;
    }

    public void setGender(String gender) {
        this.gender = gender;
    }

    public List<Score> getScores() {
        return scores;
    }

    public void setScores(List<Score> scores) {
        this.scores = scores;
    }
}

score类：

package org.example.entity;

public class Score {
    private Integer id;      // 成绩id
    private String name;     // 课程名
    private Integer stuId;   // 学生id
    private Float score;     // 成绩

    // Getters and Setters
    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getStuId() {
        return stuId;
    }

    public void setStuId(Integer stuId) {
        this.stuId = stuId;
    }

    public Float getScore() {
        return score;
    }

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

接口文件scoremapper和studentmapper

scoremapper：

package org.example.mapper;

import org.example.entity.Score;

import java.util.List;

public interface ScoreMapper {

    public List<Score> getScoresByStudentId();
}

studentmapper：

package org.example.mapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.example.entity.Score;
import org.example.entity.Student;

import java.util.List;

@Mapper
public interface StudentMapper {

    // 根据学生ID获取学生基本信息，包括成绩信息
    Student getStudentById(@Param("id") Integer id);

    // 获取指定学生的所有成绩
    List<Score> getScoresByStudentId(@Param("stuId") Integer stuId);
}

添加MyBatisUtil.java文件

package org.example;

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

import java.io.IOException;
import java.io.InputStream;

public class MyBatisUtil {
    private static SqlSessionFactory sqlSessionFactory;

    static {
        try {
            String resource = "mybatis-config.xml"; // 确保这个路径是正确的
            InputStream inputStream = Resources.getResourceAsStream(resource);
            sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        } catch (IOException e) {
            // 添加日志输出，以便查看具体的异常信息
            System.err.println("初始化 SqlSessionFactory 失败：" + e.getMessage());
            e.printStackTrace();
        }
    }

    public static SqlSessionFactory getSqlSessionFactory() {
        return sqlSessionFactory;
    }
}

进行配置文件：

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="org.example.mapper.ScoreMapper">
    <!-- 成绩结果映射 -->
    <resultMap id="ScoreResultMap" type="org.example.entity.Score">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="stuId" column="stu_id"/>
        <result property="score" column="score"/>
    </resultMap>

    <select id="getScoresByStudentId" resultType="org.example.entity.Score">
        SELECT * FROM score WHERE stu_id = #{stuId}
    </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="org.example.mapper.StudentMapper">


    <resultMap id="studentResultMap" type="org.example.entity.Student">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="age" column="age"/>
        <result property="gender" column="gender"/>
        <collection property="scores" ofType="org.example.entity.Score" column="id" select="org.example.mapper.ScoreMapper.getScoresByStudentId"/>
    </resultMap>
    <select id="getStudentById" resultMap="studentResultMap">
        SELECT * FROM student WHERE id = #{id}
    </select>
</mapper>

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>
    <!-- 配置日志 -->
    <settings>
        <setting name="logImpl" value="LOG4J2"/> <!-- 可选：可以选择其他日志实现 -->
    </settings>

    <!-- 数据库连接 -->
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/> <!-- MySQL JDBC Driver -->
                <property name="url" value="jdbc:mysql://localhost:3306/mybatisdb?useUnicode=true&amp;characterEncoding=UTF-8&amp;serverTimezone=UTC"/> <!-- 数据库 URL -->
                <property name="username" value="root"/> <!-- 数据库用户名 -->
                <property name="password" value="root"/> <!-- 数据库密码 -->
            </dataSource>
        </environment>
    </environments>

    <!-- Mapper 配置 -->
    <mappers>
        <mapper resource="org/example/mapper/StudentMapper.xml"/> <!-- StudentMapper 的路径 -->
        <mapper resource="org/example/mapper/ScoreMapper.xml"/>
    </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>org.example</groupId>
    <artifactId>Test2</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>17</maven.compiler.source>
        <maven.compiler.target>17</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>org.mybatis</groupId>
            <artifactId>mybatis</artifactId>
            <version>3.5.7</version>
        </dependency>
        <dependency>
            <groupId>junit</groupId>
            <artifactId>junit</artifactId>
            <version>4.13.2</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>org.junit.jupiter</groupId>
            <artifactId>junit-jupiter</artifactId>
            <version>5.8.1</version>
            <scope>test</scope>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>8.0.33</version> <!-- 根据需要选择版本 -->
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-api</artifactId>
            <version>2.17.1</version>
        </dependency>
        <dependency>
            <groupId>org.apache.logging.log4j</groupId>
            <artifactId>log4j-core</artifactId>
            <version>2.17.1</version>
        </dependency>



    </dependencies>

</project>

创建StudentMapperTest测试

import org.apache.ibatis.session.SqlSession;
import org.apache.ibatis.session.SqlSessionFactory;
import org.example.MyBatisUtil;
import org.example.entity.Score;
import org.example.entity.Student;
import org.example.mapper.StudentMapper;
import org.junit.jupiter.api.BeforeEach;
import org.junit.jupiter.api.Test;

import java.util.List;

import static org.junit.jupiter.api.Assertions.assertNotNull;

public class StudentMapperTest {
    private SqlSessionFactory sqlSessionFactory;

    @BeforeEach
    public void setUp() {
        // 初始化 SqlSessionFactory，确保正确配置
        sqlSessionFactory = MyBatisUtil.getSqlSessionFactory(); // MyBatisUtil 是你自定义的工具类
    }

    @Test
    public void testGetStudentById() {
        try (SqlSession session = sqlSessionFactory.openSession()) {
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
            Student student = studentMapper.getStudentById(2); // 获取 ID 为 2 的学生

            // 验证学生对象不为空
            assertNotNull(student);
            System.out.println("学生信息:");
            System.out.println("ID: " + student.getId());
            System.out.println("姓名: " + student.getName());
            System.out.println("年龄: " + student.getAge());
            System.out.println("性别: " + student.getGender());

            // 输出成绩信息
            System.out.println("成绩信息:");
            for (Score score : student.getScores()) {
                System.out.println("课程: " + score.getName() + ", 成绩: " + score.getScore());
            }
        }
    }
}

本实验主要在查找序号为2的学生信息和各项成绩

示例如下