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

ゝ一纸荒年。 2022-05-21 05:47 217阅读 0赞

在mybatis中，对于一对一、多对一都可以使用`<association>`标签关联一方。  
**准备工作，创建两个表：**  
tbl\_employee：员工表，使用dept\_id关联部门表  
![这里写图片描述][70]

tbl\_department：  
![这里写图片描述][70 1]

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

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

Department.java:

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

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

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

public interface EmployeeMapperPlus {
        public Employee getEmpAndDeptById(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.EmployeeMapperPlus">
    
    <resultMap type="com.mybatis.domain.Employee" id="myEmp">
        <id column="id" property="id"/>
        <result column="last_name" property="lastName"/>
        <result column="gender" property="gender"/>
        <result column="email" property="email"/>
        
        <association property="dept" javaType="com.mybatis.domain.Department">
            
            <id column="dept_id" property="id"/>
            <result column="department_name" property="departmentName"/>
        </association>
    </resultMap>
    
    <select id="getEmpAndDeptById" resultMap="myEmp">
        select e.id,e.last_name,e.gender,e.email,e.dept_id,d.id,d.department_name
         from tbl_employee e,tbl_department d
         where e.dept_id = d.id and e.id=#{id}
    </select>

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

**1.3 测试**

@Test
    public void getEmpAndDeptById(){
        SqlSessionFactory factory = MyBatisUtil.getFactory();
        SqlSession session = factory.openSession();
        EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpAndDeptById(3);
        System.out.println(employee);
        session.commit();
        session.close();
    }

**1.4 结果**  
![这里写图片描述][70 2]  
可以看到关联成功

需要注意的地方：  
1. 上面使用tbl\_employee表中的dept\_id列关联 tbl\_department表的id，在实体类Employee中 不需要定义dept\_id属性，定义一个Department类的属性，然后通过关联查询将关联数据塞进去。  
2.在 `<association>`标签中需要配置javaType的值，指定关联的类型。

--------------------

## 2. 分部查询 ##

**2.1 创建部门的Mapper文件**  
接口:

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

Mapper XML文件：

配置Department实体的Mapper文件，创建getDeptById方法。  
并将之注册：

**2.2 配置Employee的Mapper文件**  
接口:

public interface EmployeeMapperPlus {
    
        public Employee getEmpAndDeptByStep(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.EmployeeMapperPlus">
        <resultMap type="com.mybatis.domain.Employee" id="myEmpByStep">
            <id column="id" property="id"/>
            <result column="last_name" property="lastName"/>
            <result column="gender" property="gender"/>
            <result column="email" property="email"/>
            
            <association property="dept" select="com.mybatis.mapper.DepartmentMapper.getDeptById" column="dept_id">
            </association>
        </resultMap>
        
        <select id="getEmpAndDeptByStep" resultMap="myEmpByStep">
            select id,last_name,gender,email,dept_id
             from tbl_employee
             where id = #{id}
        </select>
    </mapper>

**2.3 测试**

@Test
    public void getEmpAndDeptByStep(){
        SqlSessionFactory factory = MyBatisUtil.getFactory();
        SqlSession session = factory.openSession();
        EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpAndDeptByStep(3);
        System.out.println(employee);
        session.commit();
        session.close();
    }

**2.4 结果**  
![这里写图片描述][70 3]  
可以看到发出了两个sql，是分步查询的,而不是之前那样一条语句关联查询。

**2.5 配置懒加载**  
我们可以配置懒加载以达到在不使用关联表信息的时候不进行查询的目的  
在conf文件中配置settings：

在执行测试，这次只查询员工表信息：

@Test
    public void getEmpAndDeptByStep(){
        SqlSessionFactory factory = MyBatisUtil.getFactory();
        SqlSession session = factory.openSession();
        EmployeeMapperPlus mapper = session.getMapper(EmployeeMapperPlus.class);
        Employee employee = mapper.getEmpAndDeptByStep(3);
        System.out.println(employee.getLastName());
        session.commit();
        session.close();
    }

结果：  
![这里写图片描述][70 4]

可以看出，只发出一个sql，这就是懒加载模式，分步查询时，在不适使用关联表的信息时就不会尽心查询。

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

对于 column=”\{id=dept\_id,xx=xx\}” 中id表示getDeptById方法的参数，dept\_id是第一步查询的id列。  
`<association >` 标签还包含一个fetchType属性，这个可以设置抓取方式，即是懒加载还是立即加载。

[70]: /images/20220521/eba0264183804c05acfa56477d137910.png
[70 1]: /images/20220521/c9dfaa843fb14921854590bb99a21720.png
[70 2]: /images/20220521/ff17a820dea14f28871b5bd2495ccad1.png
[70 3]: /images/20220521/80a61f2564aa48e6950f49b8b3663c2e.png
[70 4]: /images/20220521/7d3f0c9e8d3a401c8298970e90dd1cfc.png