场景重现

---

当想要查询一个部门下的所有员工时，一个部门 对应 多个员工

实验使用的类和对象

mapper.xml：

<select id="getEmpAndDept" resultMap="empAndDeptResultMapTwo">  
    select * from t_emp 
    left join t_dept on t_emp.did = t_dept.did    
    where t_emp.eid = #{eid}
</select>

pojo:

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class Emp {  
    private Integer eid;  
    private String empName;  
    private Integer age;  
    private String sex;  
    private String email;  
    private Dept dept;  
}

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class Dept {  
    private Integer did;  
    private String deptName;  
}

使用 <collection> 解决

---

1. 在原有的pojo中加入员工集合

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class Dept {  
    private Integer did;  
    private String deptName;  
    private List<Emp> emps;  
}

2. 在mapper的映射文件中创建 <resultMap>，并为员工集合定义标签<collection>

<resultMap id="deptAndEmpResultMap" type="Dept">  
    <id property="did" column="did"/>  
    <result column="dept_name" property="deptName"/>  
    <collection property="emps" ofType="emp">  
        <id property="eid" column="eid"/>  
        <result property="empName" column="emp_name"/>  
        <result property="age" column="age"/>  
        <result property="sex" column="sex"/>  
        <result property="email" column="email"/>  
    </collection>  
</resultMap>

3. 在映射文件中引入这个 <resultMap>

<select id="getDeptAndEmp" resultMap="deptAndEmpResultMap">  
    select * from t_dept left join t_emp on t_dept.did = t_emp.did where t_dept.did = #{did}  
</select>

使用分布查询

---

1. 和之前一样在原有的pojo中加入员工集合

@Data  
@AllArgsConstructor  
@NoArgsConstructor  
public class Dept {  
    private Integer did;  
    private String deptName;  
    private List<Emp> emps;  
}

2. 在mapper的映射文件中创建 <resultMap>，并使用<collection> 的select查询对应部门员工表

<resultMap id="deptAndEmpResultMap" type="Dept">  
    <id property="did" column="did"/>  
    <result column="dept_name" property="deptName"/>  
	<collection property="emps" select="com.zxb.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo" column="did">  
	    <id property="eid" column="eid"/>  
	    <result property="empName" column="emp_name"/>  
	    <result property="age" column="age"/>  
	    <result property="sex" column="sex"/>  
	    <result property="email" column="email"/>  
	</collection>
</resultMap>

3. emp创建对应的查询

<select id="getDeptAndEmpByStepTwo" resultType="Emp">  
    select * from t_emp where did = #{did}  
</select>