4_多对多关联查询

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

实体类

1. @NoArgsConstructor
2. @AllArgsConstructor
3. @Data
4. public class Project implements Serializable {

5.  private Integer pid;
   

6.  private String pname;
   

7.  private Integer money;
   

8.  // 组合一个ProjectRecord对象集合作为属性
   

9.  private List<ProjectRecord> projectRecords;
   

10. }
11. @Data
12. @AllArgsConstructor
13. @NoArgsConstructor
14. public class ProjectRecord implements Serializable {

15. private Integer empno;
    

16. private Integer pid;
    

17. // 组合一个Emp对象作为属性
    

18. private Emp emp;
    

19. }

接口

1. package com.msb.mapper;
2. import com.msb.pojo.Emp;
3. import com.msb.pojo.Project;
4. /**
5. • @Author: Ma HaiYang
6. • @Description: MircoMessage:Mark_7001
7. */
8. public interface ProjectMapper {

9.  /**
   

10.  * 根据项目编号查询一个项目信息及参与该项目的所有员工信息
    

11.  * @param pid 项目编号
    

12.  * @return 所有信息封装的Project对象
    

13.  */
    

14. Project findProjectJoinEmpsByPid(int pid);
    

15. }

映射文件

3.      PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
   

4.      "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
   

6.  <!--Project findProjectJoinEmpsByPid(int pid);-->
   

7.  <resultMap id="projectJoinEmps" type="project">
   

8.      <id column="pid" property="pid"></id>
   

9.      <result column="pname" property="pname"></result>
   

10.     <result column="money" property="money"></result>
    

11.     <!--一对多 集合属性 collection-->
    

12.     <collection property="projectRecords" ofType="projectRecord">
    

13.         <id column="empno" property="empno"></id>
    

14.         <id column="pid" property="pid"></id>
    

15.         <!--一对一 -->
    

16.         <association property="emp" javaType="emp">
    

17.             <id property="empno" column="empno"></id>
    

18.             <result property="ename" column="ename"></result>
    

19.             <result property="job" column="job"></result>
    

20.             <result property="sal" column="sal"></result>
    

21.             <result property="hiredate" column="hiredate"></result>
    

22.             <result property="mgr" column="mgr"></result>
    

23.             <result property="comm" column="comm"></result>
    

24.             <result property="deptno" column="deptno"></result>
    

25.         </association>
    

26.     </collection>
    

27. </resultMap>
    

28. <select id="findProjectJoinEmpsByPid"  resultMap="projectJoinEmps">
    

29.     select * from
    

30.     project p
    

31.     left join projectrecord pr
    

32.     on p.pid = pr.pid
    

33.     left join emp e
    

34.     on e.empno = pr.empno
    

35.     where p.pid= #{pid}
    

36. </select>
    

测试代码

1. @Test
2. public void testManyToMany() throws ParseException {

3.  ProjectMapper mapper = sqlSession.getMapper(ProjectMapper.class);
   

4.  Project project = mapper.findProjectJoinEmpsByPid(2);
   

5.  System.out.println(project.getPid());
   

6.  System.out.println(project.getPname());
   

7.  System.out.println(project.getMoney());
   

8.  List<ProjectRecord> projectRecords = project.getProjectRecords();
   

9.  for (ProjectRecord projectRecord : projectRecords) {
   

10.     Emp emp = projectRecord.getEmp();
    

11.     System.out.println(emp);
    

12. }
    

13. }