Day11_1--mybatis多表查询拓展学习之三表联查

IT ·南栀

于 2024-07-26 17:48:16 发布

阅读量321

点赞数 6

分类专栏： Java全栈文章标签：学习数据库

本文链接：https://blog.csdn.net/github_49984491/article/details/140717163

版权

Java全栈专栏收录该内容

34 篇文章 1 订阅

订阅专栏

三表联查的关键在于理清楚表与表之间的联系，从数据库表中可以看出要想查出一个学生的多门课程的成绩，我们的SQL语句就应该是以下逻辑：

（1）通过studens表的sno查找到学生在scores表中的sno（一对多）

(2) 通过scores表中的cno查找coures表中的cname（一对一）

创建数据库的语句在文章末尾处

现在捋清楚数据之间的关系后，就要处理实体类中的数据的正确注入

实体类

public class students {
    private String sno;
    private String sname;
    private String ssex;
    private String sbirthday;
    private String classZ;
    //将成绩实体类以集合（成绩表被注入了课程表）的形式注入学生表
    private List<scores> scores;
//setter、getter方法
}

public class scores {
    private String sno;
    private String cno;
    private String degree;
    //将课程表注入成绩表
    private courses courses;
//setter、getter方法
}

public class courses {
    private String cno;
    private String tno;
    private String cname;
//setter、getter方法
}

这里采用接口扫描来查询数据

Mapper接口

import java.util.List;

public interface threeMapper {
    //查询的结果类型是List<students>
    public List<students> selectInfo();
}

Mapper.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接口-->
<mapper namespace="Day11.threeTable.threeMapper">
    <!--三表联查-->
    <select id="selectInfo" resultMap="three">
        select students.*, courses.*, scores.*
        from students,
             courses,
             scores
        where students.sno = scores.sno
          and scores.cno = courses.cno
    </select>

    <resultMap id="three" type="Day11.threeTable.students">
        <id column="sno" property="sno"></id>
        <result column="sname" property="sname"></result>
        <result column="ssex" property="ssex"></result>
        <result column="sbirthday" property="sbirthday"></result>
        <result column="classZ" property="classZ"></result>
        <!--一个学生对应多门课程的成绩-->
        <collection property="scores" ofType="Day11.threeTable.scores">
            <!--sno和cno相当于外键，用于联系students和courses表-->
            <id column="sno" property="sno"></id>
            <id column="cno" property="cno"></id>
            <result column="degree" property="degree"></result>
            <!--一个成绩对应一门课程-->
            <association property="courses" javaType="Day11.threeTable.courses">
                <id column="cno" property="cno"></id>
                <!--tno和cname这里会报错，不用理会即可-->
                <result column="tno" property="tno"></result>
                <result column="cname" property="cname"></result>
            </association>
        </collection>
    </resultMap>
</mapper>

mybatis_config配置文件

<?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="LOG4J"/>-->
<!--    </settings>-->
    <!-- 处理日期转换-->
    <typeHandlers>
        <typeHandler handler="Day10.DateTypeHandler"></typeHandler>
    </typeHandlers>

    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://localhost:3306/mybatis?serverTimezone=UTC"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <!--        这里的写的是mapper映射文件-->
        <mapper resource="mybatis/mappers/ChangeCard/changecardMapper.xml"/>
    </mappers>
</configuration>

创建数据库表语句


-- ----------------------------
-- Table structure for courses
-- ----------------------------
DROP TABLE IF EXISTS `courses`;
CREATE TABLE `courses`  (
  `cno` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `cname` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `tno` varchar(10) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of courses
-- ----------------------------
INSERT INTO `courses` VALUES ('3-105', '计算机导论', '825');
INSERT INTO `courses` VALUES ('3-245', '操作系统', '804');
INSERT INTO `courses` VALUES ('6-166', '数据电路', '856');
INSERT INTO `courses` VALUES ('9-888', '高等数学', '100');
ALUES (3, '管理员', '负责商品的管理问题');

-- ----------------------------
-- Table structure for scores
-- ----------------------------
DROP TABLE IF EXISTS `scores`;
CREATE TABLE `scores`  (
  `sno` varchar(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `cno` varchar(5) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `degree` decimal(10, 1) NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of scores
-- ----------------------------
INSERT INTO `scores` VALUES ('103', '3-245', 86.0);
INSERT INTO `scores` VALUES ('105', '3-245', 75.0);
INSERT INTO `scores` VALUES ('109', '3-245', 68.0);
INSERT INTO `scores` VALUES ('103', '3-105', 92.0);
INSERT INTO `scores` VALUES ('105', '3-105', 88.0);
INSERT INTO `scores` VALUES ('109', '3-105', 76.0);
INSERT INTO `scores` VALUES ('101', '3-105', 64.0);
INSERT INTO `scores` VALUES ('107', '3-105', 91.0);
INSERT INTO `scores` VALUES ('108', '3-105', 78.0);
INSERT INTO `scores` VALUES ('101', '6-166', 85.0);
INSERT INTO `scores` VALUES ('107', '6-106', 79.0);
INSERT INTO `scores` VALUES ('108', '6-166', 81.0);

-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students`  (
  `sno` varchar(3) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `sname` varchar(4) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `ssex` varchar(2) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NOT NULL,
  `sbirthday` date NOT NULL,
  `classZ` varchar(50) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb3 COLLATE = utf8mb3_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES ('108', '曾华', '男', '1977-09-01', '95033');
INSERT INTO `students` VALUES ('105', '匡明', '男', '1975-10-02', '95031');
INSERT INTO `students` VALUES ('107', '王丽', '女', '1976-01-23', '95033');
INSERT INTO `students` VALUES ('101', '李军', '男', '1976-02-20', '95033');
INSERT INTO `students` VALUES ('109', '王芳', '女', '1975-02-10', '95031');
INSERT INTO `students` VALUES ('103', '陆君', '男', '1974-06-03', '95031');