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);

员工表数据

部门表数据

为两张表分别创建实体类

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：Employee类中部门对象。javaType：为deptno指定java类型，必须指定否则无法映射-->
    <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"/>
    <!-- 分步查询 -->
    <!-- 
	1、过程：将deptId作为参数传给DeptnoMapper下的findDept方法查询得到的结果赋值给deptno
	2、分步查询支持延迟加载。当在程序中如果用到deptno对象时，则进行加载，否则不加载。跟mybatis的缓存策略有关。
     -->
    <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>