8_MyBatis实现多表查询

绝地灬酷狼 2023-09-30 18:06 45阅读 0赞

前面已经使用MyBatis完成了对Emp表的CRUD操作，不管是使用SqlSession直接操作，还是使用Mapper代理方式，都只是完成了对单个数据库表的操作。这肯定是远远不够的。

在实际开发中，经常会将来自多张表的数据在一个位置显示。比如查询并显示的员工信息中会有来自部门表、岗位表的数据，而后台一般是定义一个方法

1\_关联查询

1\_手动处理映射关系

实体类
    public class Emp  implements Serializable {
        private Integer empno;
        private String name;
        private String job;
        private Integer mgr;
        private Date hiredate;
        private Double sal;
        private Double comm;
        private Integer deptno;
    映射文件
    <mapper namespace="com.msb.mapper.EmpMapper">
        
        <resultMap id="empMap" type="emp">
            
            <result property="name" column="ename"></result>
            
        </resultMap>
        <select id="findByEmpno" resultMap="empMap" >
            select * from emp where empno =#{empno}
        </select>
    </mapper>

2\_一对一关联查询

数据准备: 创建项目表和项目记录表

![00b83870ee908a12e087eec6628bfa5d.png][]

CREATE TABLE `projects`  (
      `pid` int(2) NOT NULL AUTO_INCREMENT,
      `pname` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
      `money` int(11) NULL DEFAULT NULL,
      PRIMARY KEY (`pid`) USING BTREE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    INSERT INTO `projects` VALUES (1, ' ***大学OA', 500000);
    INSERT INTO `projects` VALUES (2, '学生选课系统', 100000);
    INSERT INTO `projects` VALUES (3, '讲师测评系统', 20000);
    INSERT INTO `projects` VALUES (4, '线上问答系统 ', 20000);
    CREATE TABLE `projectrecord`  (
      `empno` int(4) NOT NULL,
      `pid` int(2) NOT NULL,
      PRIMARY KEY (`empno`, `pid`) USING BTREE,
      INDEX `fk_project_pro`(`pid`) USING BTREE,
      CONSTRAINT `fk_emp_pro` FOREIGN KEY (`empno`) REFERENCES `emp` (`EMPNO`) ON DELETE CASCADE ON UPDATE CASCADE,
      CONSTRAINT `fk_project_pro` FOREIGN KEY (`pid`) REFERENCES `projects` (`pid`) ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;
    INSERT INTO `projectrecord` VALUES (7369, 1);
    INSERT INTO `projectrecord` VALUES (7521, 1);
    INSERT INTO `projectrecord` VALUES (7369, 2);
    INSERT INTO `projectrecord` VALUES (7499, 2);
    INSERT INTO `projectrecord` VALUES (7521, 2);
    INSERT INTO `projectrecord` VALUES (7369, 3);
    INSERT INTO `projectrecord` VALUES (7499, 3);
    INSERT INTO `projectrecord` VALUES (7521, 3);
    INSERT INTO `projectrecord` VALUES (7369, 4);
    INSERT INTO `projectrecord` VALUES (7499, 4);

需求:根据编号查询员工信息及所在的部门信息

实体类添加一个部门作为属性

实体类

@AllArgsConstructor
    @NoArgsConstructor
    @Data
    public class Emp implements Serializable {
        private Integer empno;
        private String ename;
        private String job;
        private Integer mgr;
        private Date hiredate;
        private Double sal;
        private Double comm;
        private Integer deptno;
        // 组合一个Dept对象作为自己的属性
        private Dept dept;
    }

接口

public interface EmpMapper {
        /**
         * 根据员工编号查询员工的所有信息并携带所在的部门信息
         * @param empno 要查询的员工编号
         * @return Emp对象,组合了Dept对象作为属性,对部门信息进行存储
         */
        Emp findEmpJoinDeptByEmpno(int empno);
    }

映射文件

<?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="com.msb.mapper.EmpMapper">
        
        <resultMap id="empJoinDept" type="emp">
            
            <id property="empno" column="empno"></id>
            <result property="ename" column="ename"></result>
            <result property="job" column="job"></result>
            <result property="sal" column="sal"></result>
            <result property="hiredate" column="hiredate"></result>
            <result property="mgr" column="mgr"></result>
            <result property="comm" column="comm"></result>
            <result property="deptno" column="deptno"></result>
            
            <association property="dept" javaType="dept">
                <id column="deptno" property="deptno"></id>
                <result column="dname" property="dname"></result>
                <result column="loc" property="loc"></result>
            </association>
        </resultMap>
        <select id="findEmpJoinDeptByEmpno" resultMap="empJoinDept" >
            select * from
            emp e
            left join dept  d
            on e.deptno =d.deptno
            where empno = #{empno}
        </select>
    </mapper>

测试代码

@Test
    public void testOneToOne() throws ParseException {
        EmpMapper mapper = sqlSession.getMapper(EmpMapper.class);
        Emp emp = mapper.findEmpJoinDeptByEmpno(7499);
        System.out.println(emp);
    }

[00b83870ee908a12e087eec6628bfa5d.png]: https://img-blog.csdnimg.cn/img_convert/00b83870ee908a12e087eec6628bfa5d.png