场景:查询部门下的所有员工
第一种方式:嵌套结果集方式
第二种方式:分步查询方式
第一种方式:嵌套结果集方式
javaBean
public class Department {
private Integer id;
private String name;
private List<Employee> employees;}
接口
public Department getDepartmentByIdPlus(Integer id);
sql映射文件
<!--
private Integer id;
private String name;
private List<Employee> employees;
-->
<!-- 嵌套结果集的方式-->
<!--public Department getDepartmentByIdPlus(Integer id);-->
<resultMap id="myDept" type="com.stayreal.mybatis.Department">
<id column="did" property="id"/>
<result column="dept_name" property="name"/>
<!-- collection定义关联集合类型的属性封装规则
offType:指定集合中的元素类型
-->
<collection property="employees" ofType="com.stayreal.mybatis.Employee">
<id column="eid" property="id"/>
<result column="last_name" property="lastName"/>
<result column="email" property="email"/>
<result column="gender" property="gender"/>
</collection>
</resultMap>
<select id="getDepartmentByIdPlus" resultMap="myDept">
select d.id did,d.dept_name dept_name,e.id eid,e.last_name last_name,
e.email email,e.gender gender
from tbl_dept d
left JOIN tbl_employee e on d.id = e.d_id
where d.id = #{id}
</select>
junit
DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);
Department dept = mapper.getDepartmentByIdPlus(2);// 分步查询
System.out.println(dept.toString());
System.out.println(dept.getEmployees());
// Department{id=2, name='ceshi'}[Employee{id=1, //lastName='Jerry', email='Jerry@qq.com', gender='1'}, //Employee{id=3, lastName='Jerry', email='Jerry@qq.com', //gender='1'}]
第二种方式:分步查询方式
public Department getDepartmentByIdStep(Integer id);
<!--public Department getDepartmentByIdStep(Integer id);-->
<resultMap id="myDeptStep" type="com.stayreal.mybatis.Department">
<id column="id" property="id"/>
<result column="dept_name" property="name"/>
<collection property="employees" select="com.stayreal.mybatis.EmployeeMapperPlus.getEmpsByDeptId"
column="id">
</collection>
</resultMap>
<select id="getDepartmentByIdStep" resultMap="myDeptStep">
select id,dept_name name from tbl_dept where id = #{id}
</select>
Department dept = mapper.getDepartmentByIdStep(2);// 分步查询 collection
//Department{id=2, name='ceshi'}
//[Employee{id=1, lastName='null', email='Jerry@qq.com', gender='1'}, Employee{id=3, lastName='null', email='Jerry@qq.com', gender='1'}]