13、mybatis多表关联查询级联属性

1、创建表结构

CREATE  TABLE `mybatis`.`dept` (
  `id` INT NOT NULL ,
  `dept_name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) );


alter table employee add d_id INT(45);

INSERT INTO `mybatis`.`dept` (`id`, `dept_name`) VALUES ('1', '开发部');
INSERT INTO `mybatis`.`dept` (`id`, `dept_name`) VALUES ('2', '测试部');

2、EmployeeMapper接口

package com.mi.dao;

import com.mi.pojo.Employee;
import org.apache.ibatis.annotations.MapKey;
import org.apache.ibatis.annotations.Param;

import java.util.List;
import java.util.Map;

public interface EmployeeMapper {
    //查询员工及部门
    public Employee getEmpAndDept(Integer id);
}

3、EmployeeMapper.xml

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
        PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
        "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!--指定为接口的全类名-->
<mapper namespace="com.mi.dao.EmployeeMapper">

    <!--联合查询，级联属性封装结果集-->
    <resultMap id="myEmpAndEmp" type="com.mi.pojo.Employee">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="d_id" property="dept.id"/>
        <result column="dept_name" property="dept.deptName"/>
    </resultMap>
    <!--关联查询员工及部门-->
    <select id="getEmpAndDept" resultMap="myEmpAndEmp">
        select e.id,e.last_name, e.gender,d.id d_id,d.dept_name dept_name
        from employee e ,dept d
        where e.d_id = d.id and e.id = #{id}

    </select>
</mapper>

4、Test

   @Test
    public void getEmpAndDept() throws IOException {
        //1、获取SqlSessionFactory对象
        SqlSessionFactory sqlSessionFactory = getSqlSessionFactory();
        //2、获取Sqlsesion对象
        SqlSession sqlSession = sqlSessionFactory.openSession();

        try {
            //3、获取接口的实现类对象
            //会为接口自动创建一个代理对象，代理对象去执行增删改查方法
            EmployeeMapper mapper = sqlSession.getMapper(EmployeeMapper.class);

            Employee employee = mapper.getEmpAndDept(3);
            System.out.println(employee);
            System.out.println(employee.getDept());
        }finally {
            sqlSession.close();
        }
    }