一对多关系实例:
员工表:
部门表:
员工-部门关系表中,一个部门对应多个员工,想要在部门表中查询出对应的员工信息,就要解决一对多映射关系。
解决方法:在Dept实体类中添加员工的LIst集合
public class Dept { private Integer deptId; private String deptName; //处理一对多的关系 private List<Emp> emps; }
有两种方式解决此问题
1. Collection标签
<collection>标签中映射Emp员工属性值
property:关联的属性
ofType:集合内部元素的类型(List<Emp>就是Emp类型)
Dept getDeptAndEmpByStepByCollection(@Param("deptId") Integer deptId);
<!-- 处理一对多的关系-->
<resultMap id="getDeptAndEmpByStepByCollection" type="dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<!--
property: 关联的属性.
ofType: 集合内部元素的类型.
-->
<collection property="emps" ofType="emp">
<id column="emp_id" property="empId"></id>
<result column="emp_name" property="empName"></result>
</collection>
</resultMap>
<select id="getDeptAndEmpByStepByCollection" resultMap="getDeptAndEmpByStepByCollection">
SELECT * FROM t_dept left join t_emp on t_dept.dept_id = t_emp.dept_id WHERE t_dept.dept_id = #{deptId}
</select>
2. 分步查询
分别通过两个mapper查询各自的实体类属性,然后通过collection标签关联Emp.
Empmapper
List<Emp> getDeptAndEmpByStepTwo(@Param("deptId") Integer deptId);
<!--分步解决一对多问题-->
<select id = "getDeptAndEmpByStepTwo" resultType="emp">
select * from t_emp where dept_id = #{dept_id}
</select>
DeptMapper
<!-- 分布查询解决一对多问题
Dept getDeptAndEmpdsStepOne(@Param("deptId") Integer deptId);
-->
<resultMap id="getDeptAndEmpByStepOneResultMap" type="dept">
<id column="dept_id" property="deptId"></id>
<result column="dept_name" property="deptName"></result>
<!--
fetchType="eager" ofType="emp"
-->
<collection property="emps"
select="com.zt.mybatis.mapper.EmpMapper.getDeptAndEmpByStepTwo"
column="dept_id">
</collection>
</resultMap>
<select id="getDeptAndEmpByStepOne" resultMap="getDeptAndEmpByStepOneResultMap">
select * from t_dept where dept_id = #{deptId}
</select>
结果:
Preparing: select * from t_dept where dept_id = ? (BaseJdbcLogger.java:137)
Parameters: 2(Integer) (BaseJdbcLogger.java:137)
Total: 1 (BaseJdbcLogger.java:137)
Preparing: select * from t_emp where dept_id = ? (BaseJdbcLogger.java:137)
Parameters: 2(Integer) (BaseJdbcLogger.java:137)
Total: 2 (BaseJdbcLogger.java:137)
Dept(deptId=2, deptName=B, emps=[Emp(empId=2, empName=李四, age=34, gender=男, dept=null), Emp(empId=4, empName=张涛, age=24, gender=男, dept=null)])