mybatis学习笔记(7):一对多关联查询

青旅半醒 2022-05-21 05:55 195阅读 0赞

一对多或多对多对于后面的多方使用`<collection>`标签封装关联数据  
**准备工作**  
**创建两个表：**  
tbl\_employee：员工表，使用dept\_id和部门表的id关联  
![这里写图片描述][70]  
tbl\_department：部门表  
![这里写图片描述][70 1]

**创建两个实体类：**  
Employee.java：

public class Employee { 
    
        private Integer id;
        private String lastName;
        private String gender;
        private String email;
    
        public Employee() {
            super();
        }
        public Employee(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;
        }
        @Override
        public String toString() {
            return "Employee [id=" + id + ", lastName=" + lastName + ", gender="
                    + gender + ", email=" + email + "]";
        }
    }

Department.java

public class Department {
    
        private Integer id;
        private String departmentName;
        private List<Employee> emps;
    
        public Integer getId() {
            return id;
        }
        public void setId(Integer id) {
            this.id = id;
        }
        public String getDepartmentName() {
            return departmentName;
        }
        public void setDepartmentName(String departmentName) {
            this.departmentName = departmentName;
        }
        public List<Employee> getEmps() {
            return emps;
        }
        public void setEmps(List<Employee> emps) {
            this.emps = emps;
        }
        @Override
        public String toString() {
            return "Department [id=" + id + ", departmentName=" + departmentName
                    + "]";
        }
    }

在一方创建多方的集合属性来封装多方数据

## 1. 关联查询方式 ##

**1.1 创建Mapper文件**  
首先定义一个接口：

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

配置Mapper 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.mybatis.mapper.DepartmentMapper">
        
        <resultMap type="com.mybatis.domain.Department" id="myDept">
            <id column="id" property="id"/>
            <result column="department_name" property="departmentName"/>
            
            <collection property="emps" ofType="com.mybatis.domain.Employee">
                
                <id column="e_id" property="id"/>
                <result column="last_name" property="lastName"/>
                <result column="email" property="email"/>
                <result column="gender" property="gender"/>
            </collection>
        </resultMap>
    
        <select id="getDeptByIdPlus" resultMap="myDept">
            SELECT d.id,d.department_name,
                e.id e_id,e.last_name,e.email,e.gender
            FROM tbl_department d
            LEFT JOIN tbl_employee e
             ON d.id = e.dept_id
            WHERE d.id = #{id}
        </select>
    
    </mapper>

**注：column是查询列,property是映射的实体类属性。还有注意配置项ofType和之前一对一关联查询的不同**

**1.2 注册Mapper 文件**  
在conf配置文件中注册：

**1.3 测试**

@Test
    public void getDeptByIdPlus(){
        SqlSessionFactory factory = MyBatisUtil.getFactory();
        SqlSession session = factory.openSession();
        DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);
        Department dept = mapper.getDeptByIdPlus(1);
        System.out.println(dept);
        System.out.println(dept.getEmps());
        session.commit();
        session.close();
    }

**1.4 结果**  
![这里写图片描述][70 2]

## 2. 分步查询 ##

**2.1 创建员工的Mapper文件**  
接口:

public interface EmployeeMapper {
        //通过deptId查询员工信息
        public Employee getEmpByDeptId(Integer deptId);
    
    }

Mapper XML文件：

**2.2 配置部门的Mapper文件**  
接口文件：

public interface DepartmentMapper {
        //分步查询
        public Department getDeptBystep(Integer id);
    }

Mapper 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.mybatis.mapper.DepartmentMapper">
        <resultMap type="com.mybatis.domain.Department" id="myDeptBystep">
            <id column="id" property="id"/>
            <result column="department_name" property="departmentName"/>
            
            <collection property="emps" select="com.mybatis.mapper.EmployeeMapper.getEmpByDeptId" column="id">
            </collection>
        </resultMap>
        
        <select id="getDeptBystep" resultMap="myDeptBystep">
            SELECT id,department_name
            FROM tbl_department
            WHERE id = #{id}
        </select>
    </mapper>

**2.3 将上面定义的两个Mapper文件注册**

**2.4 测试**

@Test
    public void getDeptBystep(){
        SqlSessionFactory factory = MyBatisUtil.getFactory();
        SqlSession session = factory.openSession();
        DepartmentMapper mapper = session.getMapper(DepartmentMapper.class);
        Department dept = mapper.getDeptBystep(1);
        System.out.println(dept);
        System.out.println(dept.getEmps());
        session.commit();
        session.close();
    }

**2.5 结果**

![这里写图片描述][70 3]  
可以看到发出了两条sql  
**也支持懒加载模式**

注：`<collection>` 标签里面的column可以使用键值对传多列值，例如:

对于 column=”\{deptId=id,xx=xx\}” 中deptId表示getEmpByDeptId方法的参数，id是第一步查询的id列。

`<collection>` 标签还包含一个fetchType属性，这个可以设置抓取方式，即是懒加载还是立即加载。

[70]: /images/20220521/1bf4c37a04854f578c136a125904985b.png
[70 1]: /images/20220521/d0adab6c11514912b7c8788664d4283b.png
[70 2]: /images/20220521/e1fdb6b7f099470aa634babc3b55ca43.png
[70 3]: /images/20220521/365184f6939e447992825c9045dab428.png