（五）Mybatis的入门教程—— resultMap的应用（关联查询）和association相关用法

本文链接：https://blog.csdn.net/Steriles_/article/details/81666524

场景：查询Employee的同时查出员工对应的部门

一个Employee对应一个Department，每一个员工都有他的department信息，且department信息也在Employee对象当中。

public class Employee{
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Department dept;
    //省略setter和getter方法
}

public class Department{
    private Integer id;
    private String departmentName;
    //getter、setter方法省略
}

创建部门表

CREATE TABLE tbl_dept(
    id INT(11) PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(255)
)

再插入几个部门

并为员工表添加一列新值

ALTER TABLE tbl_employee ADD COLUMN d_id INT(11);

添加约束

ALTER TABLE tbl_employee ADD CONSTRAINT fk_emp_dept
FOREIGN KEY(d_id) REFERENCES tbl_dept(id)

样式

多表联查

接口

public interface EmployeeMapperPlus {
    public Employee getEmpAndDept(Integer id);
}

映射文件

联合查询：级联属性封装结果集

<resultMap type="com.mybatis.bean.Employee" id="MyDifEmp">
    <id column="id" property="id" />
    <result column="Last_name" property="LastName" />
    <result column="gender" property="gender" />
    <result column="did" property="dept.id" />
    <result column="dept_name" property="dept.departmentName" />
</resultMap>

<select id="getEmpAndDept" resultMap="MyDifEmp">
    SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,
        d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
    WHERE e.d_id=d.id AND e.id=#{id}
    
</select>

测试

@Test
public void test () throws IOException{
    sqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    sqlSession openSession = sqlSessionFactory.openSession();
    try{
        EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
        Employee empAndDept = mapper.getEmpAndDept(1);
        System.out.println(empAndDept);
        System.out.println(empAndDept.getDept());
    }finally{
        openSession.close();
    }
}

结果

或者使用另一种方法，使用association定义关联的单个对象的封装规则（相当于嵌套了结果集）

映射文件

association可以指定联合的javabean对象

property=” “；可以指定哪个属性是联合的对象

javaType=” “；指定这个属性对象的类型【不能省略】

<resultMap type="com.mybatis.bean.Employee" id="MyDifEmp2">
    <id column="id" property="id" />
    <result column="Last_name" property="LastName" />
    <result column="gender" property="gender" />
    <association property="dept" javaType="com.mybatis.bean.Department">
        <id column="did" property="id" />
        <result column="dept_name" property="departmentName" />
    </association>
</resultMap>

<select id="getEmpAndDept" resultMap="MyDifEmp2">
    SELECT e.id id,e.last_name last_name,e.gender gender,e.d_id d_id,
        d.id did,d.dept_name dept_name FROM tbl_employee e,tbl_dept d
    WHERE e.d_id=d.id AND e.id=#{id}
    
</select>

当然，association还可以用来分步查询

分步查询的步骤：

1、先按照员工id查询员工信息

2、根据查询员工信息中的d_id值去部门表查出部门信息

2、部门设置到员工中

演示查询过程

（1）先查出员工信息

（2）再根据员工信息中部门id查处员工所在部门信息

创建一个新的接口

public interface DepartmentMapper{
    public Department getDeptById(Integer id);
}

创建该接口相应的映射文件

<mapper namespace="com.mybatis.dao.DepartmentMapper">
    <select id="getDeptById" resultType="com.mybatis.bean.Department">
        select id,dept_name departmentName from tbl_dept 
        where id=#{id}
    </select>

</mapper>

接口

public interface EmployeeMapperPlus{
    public Employee getEmpByIdStep(Integer id)
}

映射文件

association定义关联对象的封装规则

select：表明当前属性是调用select指定的方法查出的结果

column：指定将哪一列的值传给这个方法

流程：使用select指定的方法（传入column指定的这列参数的值）查出对象，并封装给property指定的属性

<resultMap type="com.mybatis.bean.Employee" id="MyEmpByStep">
    <id column="id" property="id" />
    <result column="last_name" property="lastName" />
    <result column="email" property="email" />
    <result column="gender" property="gender" />
    <!-- association定义关联对象的封装规则 -->
    <association property="dept" 
        select="com.mybatis.dao.DepartmentMapper.getDeptById"
        column="d_id" > 
    </association>
</resultMap>

<select id="getEmpByIdStep" resultMap="MyEmpByStep">
    select * from tbl_employee where id=#{id}
</select>

测试

@Test
public void test () throws IOException{
    sqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
    sqlSession openSession = sqlSessionFactory.openSession();
    try{
        EmployeeMapperPlus mapper = openSession.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpByIdStep(1);
        System.out.println(employee);
        System.out.println(employee.getDept());
    }finally{
        openSession.close();
    }
}

结果