例如:查询某部门基础信息及该部门下的所有员工
实体类---部门---Dept
@Data
@AllArgsConstructor
@NoArgsConstructor
public class Dept {
private Integer deptId;
private String deptName;
private List<Emp> emps;
}
EmpMapper.xml
<select id="queryEmpByDeptId" resultType="Emp">
select * from EMP where dept_id = #{deptId}
</select>
具体处理一对多关系的两种方式:
1、collection
<resultMap id="deptAndEmpMap" type="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"/>
<collection property="emps" ofType="Emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"/>
</collection>
</resultMap>
<select id="queryDeptAndEmp" resultMap="deptAndEmpMap">
select D.dept_id,
D.dept_name,
E.emp_id,
E.emp_name
from DEPT D LEFT JOIN EMP E ON D.dept_id = E.dept_id
where D.dept_id = ${deptId}
</select>
2、分步查询
<resultMap id="deptAndEmpStepMap" type="Dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"/>
<collection property="emps" select="com.neusoft.mybatis.parameter.mapper.EmpMapper.queryEmpByDeptId" column="dept_id">
<id column="emp_id" property="empId"/>
<result column="emp_name" property="empName"/>
</collection>
</resultMap>
<select id="queryDeptAndEmpByStep" resultMap="deptAndEmpStepMap">
select * from DEPT where dept_id = #{deptId}
</select>