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

ゝ一纸荒年。 2023-09-30 18:06 12阅读 0赞

根据部门号查询部门信息及该部门的所有员工信息

实体类

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    public class Dept implements Serializable {
        private Integer deptno;
        private String dname;
        private String loc;
        // 组合一个Emp的List集合作为属性
        private List<Emp> empList;
    }

接口

package com.msb.mapper;
    import com.msb.pojo.Dept;
    /**
     * @Author: Ma HaiYang
     * @Description: MircoMessage:Mark_7001
     */
    public interface DeptMapper {
        /**
         * 根据部门编号查询部门信息及该部分的所有员工信息
         * @param deptno 要查询的部门编号
         * @return Dept对象,内部组合了一个Emp的List属性用于封装部门的所有员工信息
         */
        Dept findDeptJoinEmpsByDeptno(int deptno);
    }

映射文件

<?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.DeptMapper">
        
        <resultMap id="deptJoinEmps" type="dept">
            <id column="deptno" property="deptno"></id>
            <result column="dname" property="dname"></result>
            <result column="loc" property="loc"></result>
            
            <collection property="empList" ofType="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>
            </collection>
        </resultMap>
        <select id="findDeptJoinEmpsByDeptno" resultMap="deptJoinEmps">
            select * from dept d left join emp e on d.deptno =e.deptno where d.deptno =#{deptno}
        </select>
    </mapper>

测试代码

@Test
    public void testOneToMany() throws ParseException {
        DeptMapper mapper = sqlSession.getMapper(DeptMapper.class);
        Dept dept = mapper.findDeptJoinEmpsByDeptno(20);
        System.out.println(dept);
        System.out.println("---------");
        List<Emp> empList = dept.getEmpList();
        empList.forEach(System.out::println);
    }