准备两个表员工和部门,关系是一个部门下有多个员工,表数据如下
员工:
部门:
对应的实体:
员工:
@Data
public class Emp {
private Integer id;
private String empName;
private String sex;
private String email;
private Dept dept;
}
部门:
@Data
public class Dept {
private Integer id;
private String deptName;
private List<Emp> emps;
}
先说 association 标签.此标签适合使用一对一的情况,比如一个员工对应一个部门,现在有如下需求,根据员工id查询员工信息,并一起把所属的部门数据查询出来,该需求有三种方式可以实现:
1.直接通过sql查询,并且直接通过属性名点. 的形式进行赋值
<!--直接通过sql查询,并且直接通过属性名点. 的形式进行赋值-->
<resultMap id="resultMap1" type="com.yyc.platform.user.model.Emp">
<id column="id" property="id"/>
<result column="emp_name" property="empName"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="email" property="email"/>
<result column="dept_id" property="dept.id"/>
<result column="dept_name" property="dept.deptName"/>
</resultMap>
<select id="getEmpById1" resultMap="resultMap1">
SELECT e.id ,e.emp_name ,e.email ,e.sex,e.dept_id ,d.dept_name
FROM employee e
LEFT JOIN dept d on e.dept_id=d.id
where e.id=#{id}
</select>
2.通过使用association 标签直接进行赋值
<resultMap id="resultMap2" type="com.yyc.platform.user.model.Emp">
<id column="id" property="id"/>
<result column="emp_name" property="empName"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="email" property="email"/>
<association property="dept" javaType="com.yyc.platform.user.model.Dept">
<id column="dept_id" property="id"/>
<result column="dept_name" property="deptName"/>
</association>
</resultMap>
<select id="getEmpById2" resultMap="resultMap2">
SELECT e.id ,e.emp_name ,e.email ,e.sex,e.dept_id ,d.dept_name
FROM employee e
LEFT JOIN dept d on e.dept_id=d.id
where e.id=#{id}
</select>
3.使用association 中的select进行分步查询,参数就是员工表中的id
<resultMap id="resultMap3" type="com.yyc.platform.user.model.Emp">
<id column="id" property="id"/>
<result column="emp_name" property="empName"/>
<result column="name" property="name"/>
<result column="sex" property="sex"/>
<result column="email" property="email"/>
<!--会根据查询到的dept_id 作为参数 去找这个方法com.yyc.platform.user.mapper.DeptMapper.getDeptById 然后进行查询-->
<association property="dept" select="com.yyc.platform.user.mapper.DeptMapper.getDeptById" column="dept_id">
</association>
</resultMap>
<select id="getEmpById3" resultMap="resultMap3">
SELECT e.id ,e.emp_name ,e.email ,e.sex,e.dept_id
FROM employee e
where e.id=#{id}
</select>
第三步需要在 部门xml中提供者个getDeptById 的方法,如下:
<!--只查询部门的信息-->
<resultMap id="resultMapDept" type="com.yyc.platform.user.model.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
</resultMap>
<select id="getDeptById" resultMap="resultMapDept">
select id ,dept_name from dept where id =#{id}
</select>
这三种方式都可以实现部门信息的封装
再说collection,该标签适合使用在结果集是集合的情况下,比如,查询部门信息,并把所有的该部门下的员工查询出来,上面的部门entity也能看出来
1.通过联合查询查询部门和员工的信息,并且通过collection 封装员工数据
<resultMap id="resultDeptAndEmp" type="com.yyc.platform.user.model.Dept">
<id column="d_id" property="id"/>
<result column="dept_name" property="deptName"/>
<collection property="emps" ofType="com.yyc.platform.user.model.Emp">
<id column="e_id" property="id"/>
<result column="emp_name" property="empName"/>
<result column="email" property="email"/>
<result column="sex" property="sex"/>
</collection>
</resultMap>
<select id="resultDeptAndEmp" resultMap="resultDeptAndEmp">
SELECT
d.id AS d_id,
d.dept_name,
e.id AS e_id,
e.emp_name,
e.email,
e.sex
FROM
dept d
LEFT JOIN employee e ON e.dept_id = d.id
WHERE
d.id =#{id}
</select>
2.通过使用collection中的select 标签 进行分步查询,参数值是部门id
<resultMap id="resultMapStep" type="com.yyc.platform.user.model.Dept">
<id column="id" property="id"/>
<result column="dept_name" property="deptName"/>
<collection property="emps" select="com.yyc.platform.user.mapper.EmpMapper.getEmpByDeptId" column="id">
</collection>
</resultMap>
<select id="getDeptByIdStep" resultMap="resultMapStep">
SELECT
d.id ,
d.dept_name
FROM
dept d
WHERE
d.id =#{id}
</select>