8_MyBatis实现多表查询-4_多对多关联查询

女爷i 2023-09-30 18:06 1阅读 0赞

根据项目编号查询项目信息,以及参与到该项目之中的所有的员工信息

实体类

@NoArgsConstructor
    @AllArgsConstructor
    @Data
    public class Project  implements Serializable {
        private Integer pid;
        private String pname;
        private Integer money;
        // 组合一个ProjectRecord对象集合作为属性
        private List<ProjectRecord> projectRecords;
    }
    @Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class ProjectRecord implements Serializable {
        private Integer empno;
        private Integer pid;
        // 组合一个Emp对象作为属性
        private Emp emp;
    }

接口

package com.msb.mapper;
    import com.msb.pojo.Emp;
    import com.msb.pojo.Project;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public interface ProjectMapper {
        /**
         * 根据项目编号查询一个项目信息及参与该项目的所有员工信息
         * @param pid 项目编号
         * @return 所有信息封装的Project对象
         */
        Project findProjectJoinEmpsByPid(int pid);
    }

映射文件

<?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.ProjectMapper">
        
        <resultMap id="projectJoinEmps" type="project">
            <id column="pid" property="pid"></id>
            <result column="pname" property="pname"></result>
            <result column="money" property="money"></result>
            
            <collection property="projectRecords" ofType="projectRecord">
                <id column="empno" property="empno"></id>
                <id column="pid" property="pid"></id>
                
                <association property="emp" javaType="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>
            </collection>
        </resultMap>
        <select id="findProjectJoinEmpsByPid"  resultMap="projectJoinEmps">
            select * from
            project p
            left join projectrecord pr
            on p.pid = pr.pid
            left join emp e
            on e.empno = pr.empno
            where p.pid= #{pid}
        </select>
    </mapper>

测试代码

@Test
    public void testManyToMany() throws ParseException {
        ProjectMapper mapper = sqlSession.getMapper(ProjectMapper.class);
        Project project = mapper.findProjectJoinEmpsByPid(2);
        System.out.println(project.getPid());
        System.out.println(project.getPname());
        System.out.println(project.getMoney());
        List<ProjectRecord> projectRecords = project.getProjectRecords();
        for (ProjectRecord projectRecord : projectRecords) {
            Emp emp = projectRecord.getEmp();
            System.out.println(emp);
        }
    }