【Mybatis】MyBatis之表的关联查询（五）

　　本章介绍Mybatis之表的关联查询

一对一关联

　　查询员工信息以及员工的部门信息

　　1、准备表employee员工表，department部门表

 CREATE TABLE `employee` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `last_name` varchar(255) DEFAULT NULL,
   `gender` char(1) DEFAULT NULL,
   `email` varchar(255) DEFAULT NULL,
   `dept_id` int(11) DEFAULT NULL COMMENT '部门ID',
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

 CREATE TABLE `department` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `dep_name` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`id`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

　　2、新建员工实体类（EmployeePlus.java）,以及部门实体类（Department.java）

 package com.hd.test.pojo;

 public class EmployeePlus {

     private Integer id;
     private String lastName;
     private String gender;
     private String email;

     private Department dept;

     public EmployeePlus() {
         // TODO Auto-generated constructor stub
     }

     public EmployeePlus(String lastName, String gender, String email) {
         super();
         this.lastName = lastName;
         this.gender = gender;
         this.email = email;
     }

     public EmployeePlus(Integer id, String lastName, String gender, String email) {
         super();
         this.id = id;
         this.lastName = lastName;
         this.gender = gender;
         this.email = email;
     }

     public Integer getId() {
         return id;
     }
     public void setId(Integer id) {
         this.id = id;
     }
     public String getLastName() {
         return lastName;
     }
     public void setLastName(String lastName) {
         this.lastName = lastName;
     }
     public String getGender() {
         return gender;
     }
     public void setGender(String gender) {
         this.gender = gender;
     }
     public String getEmail() {
         return email;
     }
     public void setEmail(String email) {
         this.email = email;
     }

     public Department getDept() {
         return dept;
     }

     public void setDept(Department dept) {
         this.dept = dept;
     }

     @Override
     public String toString() {
         return "EmployeePlus [id=" + id + ", lastName=" + lastName + ", gender=" + gender + ", email=" + email
                 + ", dept=" + dept + "]";
     }

 }

 package com.hd.test.pojo;

 public class Department {

     private Integer id;
     private String deptName;

     public Integer getId() {
         return id;
     }
     public void setId(Integer id) {
         this.id = id;
     }
     public String getDeptName() {
         return deptName;
     }
     public void setDeptName(String deptName) {
         this.deptName = deptName;
     }

     @Override
     public String toString() {
         return "Department [id=" + id + ", deptName=" + deptName + "]";
     }

 }

　　3、编辑sql映射文件EmployeeMapperPlus.xml，文件中有2种resultMap，都可以使用

 <?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.hd.test.mapper.EmployeeMapperPlus">

     <!-- 联合查询：级联属性封装结果集 -->
     <!-- 封装方式1:普通封装 -->
     <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeDept">
         <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.deptName"/>
     </resultMap>

     <!-- 封装方式2:采用association标签的方式封装 -->
     <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeDept2">
         <id column="id" property="id"/>
         <result column="last_name" property="lastName"/>
         <result column="gender" property="gender"/>

         <association property="dept" javaType="com.hd.test.pojo.Department">
             <id column="did" property="id"/>
             <result column="dept_name" property="deptName"/>
         </association>
     </resultMap>

     <select id="getMyEmployeeDept" resultMap="MyEmployeeDept" >  <!-- resultMap="MyEmployeeDept2" -->
         SELECT
             emp.id,
             emp.last_name,
             emp.gender,
             dept.id AS did,
             dept.dep_name AS dept_name
         FROM
             employee emp
             LEFT JOIN department dept ON emp.dept_id = dept.id
         WHERE
             emp.id = #{id}
     </select>

 </mapper>

　　4、在mybatis-config.xml文件中注册EmployeeMapperPlus.xml

 <mappers>
     <!-- 添加sql射文件到Mybatis的全局配置文件中 -->
     <mapper resource="mapper/EmployeeMapperPlus.xml" />
 </mappers>

　　5、编写接口（DepartmentPlusMapper.java）

 package com.hd.test.mapper;

 import com.hd.test.pojo.DepartmentPlus;

 public interface DepartmentPlusMapper {

     public DepartmentPlus getDeparmentAndEmp(Integer id);

 }

　　6、测试类

 @Test
 public void test01() throws IOException {

     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

     SqlSession session = sqlSessionFactory.openSession();

     try {
         EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
         EmployeePlus employeePlus = mapper.getMyEmployeeDept(1);
         System.out.println(employeePlus);

     } catch (Exception e) {
         e.printStackTrace();
     }finally {
         session.close();
     }

 }

　　7、执行结果，可以看到员工和部门信息同时查出来了，如下：

　　　　

　　一对一关联查询拓展

　　　　a、分步的形式查询，即先查员工信息，然后查部门执行，需要执行2次sql

　　　　　　1）编辑EmployeeMapperPlus.xml文件，新增内容

 <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeByStep">
     <id column="id" property="id"/>
     <result column="last_name" property="lastName"/>
     <result column="gender" property="gender"/>

     <association property="dept" select="com.hd.test.mapper.DepartmentMapper.getDeparmentById" column="dept_id">

     </association>
 </resultMap>

 <select id="getMyEmployeeByStep" resultMap="MyEmployeeByStep" >
     SELECT id, last_name, gender, dept_id FROM employee WHERE id = #{id}
 </select> 

　　　　　　2）新增一个 DepartmentMapper.xml 文件，并且也在mybatis-config.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.hd.test.mapper.DepartmentMapper">
     <select id="getDeparmentById" resultType="com.hd.test.pojo.Department">
         select id, dep_name deptName from department where id =
         #{id}
     </select>
 </mapper>

　　　　　　3、调用方法如下：

 @Test
 public void test02() throws IOException {

     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

     SqlSession session = sqlSessionFactory.openSession();

     try {
         EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
         EmployeePlus employeePlus = mapper.getMyEmployeeByStep(1);
         System.out.println(employeePlus.getLastName());
         System.out.println(employeePlus.getDept());
     } catch (Exception e) {
         e.printStackTrace();
     }finally {
         session.close();
     }

 }

　　　　　　4、执行结果如下，可以从日志中看到，先执行sql查询了员工信息表的数据，然后在执行sql查询了部门表的数据，分两次查询

　　　　　　　　

　　　　b、分步的形式懒加载查询，即先查员工信息，然后在需要使用部门信息的时候，在去查询部门信息

　　　　　　方法1：在上面分步的形式查询基础上，在mybatis-config.xml文件中设置全局变量，然后执行

 <settings>
     <!-- 延迟加载的全局开关。当开启时，所有关联对象都会延迟加载。 特定关联关系中可通过设置fetchType属性来覆盖该项的开关状态。 -->
     <setting name="lazyloadingenabled" value="true"/>
     <!-- 当开启时，任何方法的调用都会加载该对象的所有属性。否则，每个属性会按需加载 -->
     <setting name="aggressivelazyloading" value="false"/>
 </settings>　　　　

　　　　　　　　　　　　

　　　　　　方法2：在上面分步的形式查询基础上，在sql配置文件中，配置resultMap中association标签的 fetchType 属性，fetchType 属性有2个值：lazy（懒加载），eager（立即加载），然后执行

 <resultMap type="com.hd.test.pojo.EmployeePlus" id="MyEmployeeByStep">
     <id column="id" property="id"/>
     <result column="last_name" property="lastName"/>
     <result column="gender" property="gender"/>

     <association property="dept" select="com.hd.test.mapper.DepartmentMapper.getDeparmentById" column="dept_id" fetchType="lazy">
     </association>
 </resultMap>

　　　　　　优先级：association标签的 fetchType 属性设置的优先级高于mybatis-config.xml文件中的e设置

一对多关联

　　查询部门信息，以及部门下所有员工信息

　　1、表结构上，2张表，员工和部门

　　2、新建部门实体类（DepartmentPlus.java）,以及员工实体类（Employee.java）

 package com.hd.test.pojo;

 import java.util.List;

 public class DepartmentPlus {

     private Integer id;
     private String deptName;

     private List<Employee> emps;

     public Integer getId() {
         return id;
     }
     public void setId(Integer id) {
         this.id = id;
     }
     public String getDeptName() {
         return deptName;
     }
     public void setDeptName(String deptName) {
         this.deptName = deptName;
     }
     @Override
     public String toString() {
         return "DepartmentPlus [id=" + id + ", deptName=" + deptName + ", emps=" + emps + "]";
     }
     public List<Employee> getEmps() {
         return emps;
     }
     public void setEmps(List<Employee> emps) {
         this.emps = emps;
     }

 }

 package com.hd.test.pojo;

 public class Employee {

     private Integer id;
     private String lastName;
     private String gender;
     private String email;

     public Integer getId() {
         return id;
     }
     public void setId(Integer id) {
         this.id = id;
     }
     public String getLastName() {
         return lastName;
     }
     public void setLastName(String lastName) {
         this.lastName = lastName;
     }
     public String getGender() {
         return gender;
     }
     public void setGender(String gender) {
         this.gender = gender;
     }
     public String getEmail() {
         return email;
     }
     public void setEmail(String email) {
         this.email = email;
     }
     @Override
     public String toString() {
         return "Employee [id=" + id + ", lastName=" + lastName + ", gender=" + gender + ", email=" + email + "]";
     }

 }

　　3、编辑sql映射文件DepartmentPlusMapper.xml，文件中有2种查询方式，一种一次性查处所有数据，另一种分步查询数据

<?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.hd.test.mapper.DepartmentPlusMapper">

    <!--嵌套结果集的方式，使用collection标签定义关联的集合类型的属性封装规则  -->
    <resultMap type="com.hd.test.pojo.DepartmentPlus" id="DeparmentAndEmpMap">
        <id column="id" property="id"/>
        <id column="dep_name"  property="deptName"/>
        <!--
            collection定义关联集合类型的属性的封装规则
            ofType:指定集合里面元素的类型
        -->
        <collection property="emps" ofType="com.hd.test.pojo.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="getDeparmentAndEmp" resultMap="DeparmentAndEmpMap">
        SELECT
            dept.id,
            dept.dep_name,
            emp.id AS eid,
            emp.last_name,
            emp.gender
        FROM
            department dept
            LEFT JOIN employee emp ON dept.id = emp.dept_id
        WHERE dept.id = #{id}
    </select>

    <!-- 分步查询方式  -->
    <resultMap type="com.hd.test.pojo.DepartmentPlus" id="DeparmentAndEmpMapByStep">
        <id column="id" property="id"/>
        <id column="dep_name"  property="deptName"/>
        <collection property="emps" select="com.hd.test.mapper.EmployeeMapper.getEmployeeByDeptId" column="id" fetchType="eager">
        </collection>
    </resultMap>

    <!--
        resultMap-collection 扩展：多列的值传递过去, 将多列的值封装map传递；
            column="{key1=column1,key2=column2}"

        如下也是可行的
        <collection property="emps" select="com.hd.test.mapper.EmployeeMapper.getEmployeeByDeptId" column="{deptId=id}" fetchType="eager">
        </collection>
     -->

    <select id="getDeparmentAndEmpMapByStep" resultMap="DeparmentAndEmpMapByStep">
        SELECT id, dep_name FROM department WHERE id = #{id}
    </select>

</mapper>

　　4、编辑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.hd.test.mapper.EmployeeMapper">
     <select id="getEmployeeByDeptId" resultType="com.hd.test.pojo.Employee">
         select id, last_name lastName, gender, email from employee where dept_id = #{deptId}

     </select>
 </mapper>

　　5、在mybatis-config.xml文件中注册DepartmentPlusMapper.xml 和 EmployeeMapper.xml文件

　　6、编写接口（DepartmentPlusMapper.java）

 package com.hd.test.mapper;

 import com.hd.test.pojo.DepartmentPlus;

 public interface DepartmentPlusMapper {

     public DepartmentPlus getDeparmentAndEmp(Integer id);

     public DepartmentPlus getDeparmentAndEmpMapByStep(Integer id);

 }

　　7、测试类　　

 @Test
 public void test03() throws IOException {

     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

     SqlSession session = sqlSessionFactory.openSession();

     try {
         DepartmentPlusMapper mapper = session.getMapper(DepartmentPlusMapper.class);
         DepartmentPlus departmentPlus = mapper.getDeparmentAndEmp(1);
         System.out.println(departmentPlus);
         // System.out.println(employeePlus.getDept());
     } catch (Exception e) {
         e.printStackTrace();
     }finally {
         session.close();
     }

 }

 @Test
 public void test04() throws IOException {

     InputStream inputStream = Resources.getResourceAsStream("mybatis-config.xml");
     SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);

     SqlSession session = sqlSessionFactory.openSession();

     try {
         DepartmentPlusMapper mapper = session.getMapper(DepartmentPlusMapper.class);
         DepartmentPlus departmentPlus = mapper.getDeparmentAndEmpMapByStep(1);
         System.out.println(departmentPlus.getDeptName());
         System.out.println(departmentPlus.getEmps());
     } catch (Exception e) {
         e.printStackTrace();
     }finally {
         session.close();
     }

 }

　　8、执行结果，可以看到部门信息以及部门下所有员工信息同时查出来了，如下：

　　　　a、一次性查询方法：getDeparmentAndEmp()，结果：　

　　　　　　

　　　　b、分步查询方法：getDeparmentAndEmpMapByStep()，结果：