MyBatis的级联属性、联合、分步查询

在说级联属性查询之前，先把需要的表和数据创建好。

/创建员工表/
CREATE TABLE tbl_employee(
id int(11) AUTO_INCREMENT PRIMARY KEY,
last_name varchar(25),
gender char(1),
email varchar(255)
);
/创建部门表/
create table tbl_deptno(
id int(11) primary key auto_increment,
dept_name varchar(255)
);
/在员工表中添加部门id列/
alter table tbl_employee add column dept_id int(11);
/设置外键/
alter table tbl_employee add constraint fk_emp_dept foreign key(dept_id) references tbl_deptno(id);

员工表数据

image.png

部门表数据

image.png

为两张表分别创建实体类

public class Employee {

private Integer id;
private String last_name;
private String gender;
private String email;
private Deptno deptno;
//get,set方法省略....

}
public class Deptno {

private Integer id;
private String deptName;

//get,set方法省略....
}
现在我们有个需求就是查出某个员工及部门信息。

首先mapper接口中定义方法

public interface EmployeeMapperPlus {
//根据员工id查询
public Employee findEmpAndDept(Integer id);
}
sql映射文件

1、使用级联属性查询

<resultMap type="com.luohp.mybatis.bean.Employee" id="empAndDept_map">
<id column="id" property="id"/>
<result column="lastName" property="last_name"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>

<result column="did" property="deptno.id"/>
<result column="dname" property="deptno.deptName"/>
</resultMap>

<select id="findEmpAndDept" resultMap="empAndDept_map">
SELECT
e.id id,
e.last_name lastName,
e.gender gender,
e.email email,
d.id did,
d.dept_name dname
FROM
bl_employee e
LEFT JOIN tbl_deptno d ON e.dept_id = d.id
WHERE e.id=#{id}
</select>
2、使用association(联合)查询

<resultMap type="com.luohp.mybatis.bean.Employee" id="empAndDept_map">
<id column="id" property="id"/>
<result column="lastName" property="last_name"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>

<association property="deptno" javaType="Deptno">
<id column="did" property="id"/>
<result column="dname" property="deptName"/>
</association>
</resultMap>
<select id="findEmpAndDept" resultMap="empAndDept_map">
SELECT
e.id id,
e.last_name lastName,
e.gender gender,
e.email email,
d.id did,
d.dept_name dname
FROM
bl_employee e
LEFT JOIN tbl_deptno d ON e.dept_id = d.id
WHERE e.id=#{id}
</select>
3、分步查询(支持延迟加载)

<resultMap type="com.luohp.mybatis.bean.Employee" id="empAndDept_map1">
<id column="id" property="id"/>
<result column="lastName" property="last_name"/>
<result column="gender" property="gender"/>
<result column="email" property="email"/>


<association property="deptno" column="deptId"
select="com.luohp.mybatis.dao.DeptnoMapper.findDept" >
</association>
</resultMap>

<select id="findById" resultMap="empAndDept_map1" >
select
id,last_name lastName,gender,email,dept_id deptId
from
tbl_employee
where
id=#{id}
</select>