场景重现
当想要查询一个部门下的所有员工时,一个部门 对应 多个员工
实验使用的类和对象
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>