Mybatis八(关联查询)

最新推荐文章于 2024-02-14 23:14:37 发布

前进的小学生

最新推荐文章于 2024-02-14 23:14:37 发布

阅读量354

点赞数

CC 4.0 BY-SA版权

分类专栏： java mybatis 文章标签： mybatis association collection resultMap 关联查询

本文链接：https://blog.youkuaiyun.com/zzzffhh/article/details/98171981

java 同时被 2 个专栏收录

45 篇文章

订阅专栏

mybatis

11 篇文章

订阅专栏

本文详细介绍MyBatis中关联查询的多种封装方式，包括级联属性、association标签、分步查询、collection嵌套结果集及懒加载等，旨在帮助开发者理解和掌握复杂查询结果的高效处理。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

前面的例子都是查询一张表,name查询两张表结果怎么封装呢,这就是关联查询,下面介绍关联查询的几种封装方式

前期准备

在员工信息中添加一条部门信息,标志这个员工是属于哪个部门的.

Employee.java

package com.fish.pojo;
public class Employee {
    private Integer id;
    private String lastName;
    private String email;
    private String gender;
    private Dapartment dapartment;//添加了一个部门属性
    ***
    ***
    @Override
    public String toString() {
        return "Dapartment [id=" + id + ", departmentName=" + departmentName + "]";
        }
}

Department.java(新增)

package com.fish.pojo;
public class Dapartment {   
    private Integer id;
    private String departmentName;
    ***
    ***
    @Override
    public String toString() {
        return "Dapartment [id=" + id + ", departmentName=" + departmentName + "]";
        }
}

tbl_employee(新增d_id:部门id字段)

tbl_dapartment

一, 级联属性封装结果

接口

/**
 * 根据员工id查询员工信息以及所属部门的相关信息
 * @Author ZFH
 * @Date 2019年7月20日
 */
public Employee findEmpAndDept(Integer id);

配置文件

<select id="findEmpAndDept" resultMap="emp2">
select e.*,d.id did,d.department_name from tbl_employee
e left join tbl_department d on e.d_id = d.id where e.id = #{id}
</select>

到了这里我们会有一个疑问,怎么把查询到的部门信息封装到Employe中去呢,这里还是用到了我们的resultMap中的级联查询,配置文件中作如下修改

<resultMap type="com.fish.pojo.Employee" id="emp2">
    <id column="id" property="id"/>
    <result column="last_name" property="lastName" />
    <result column="email" property="email" />
    <result column="gender" property="gender" />
    <result column="did" property="dapartment.id" />
    <result column="department_name" property="dapartment.departmentName" />
</resultMap>
<!-- 使用resultMap -->
<select id="findEmpAndDept" resultMap="emp2">
select e.*,d.id did,d.department_name from tbl_employee
e left join tbl_department d on e.d_id = d.id where e.id = #{id}
</select>

测试方法

@org.junit.Test
public void test02() throws IOException {
    //读取配置文件
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    //获取SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession openSession = sqlSessionFactory.openSession();
    //调用getMapper方法获取该接口的实现类
    EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
    Employee selectEmpById = mapper.findEmpAndDept(1);
    System.out.println(selectEmpById);
    //关闭Session
    openSession.close();
}

运行结果

Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1, dapartment=Dapartment [id=1, departmentName=123]]

二, 使用association标签封装结果

接口

/**
 * 根据员工id查询员工信息以及所属部门的相关信息
 * @Author ZFH
 * @Date 2019年7月20日
 */
public Employee findEmpAndDept2(Integer id);

配置文件

<!-- 使用级联属性封装结果集 -->
<resultMap type="com.fish.pojo.Employee" id="emp3">
    <id column="id" property="id" />
    <result column="last_name" property="lastName" />
    <result column="email" property="email" />
    <result column="gender" property="gender" />

    <!-- association标签可以指定联合的JavaBean对象 javaType:指定这个属性对象的类型(不能省略) -->
    <association property="department"
        javaType="com.fish.pojo.Department">
        <id column="did" property="id" />
        <result column="department_name" property="departmentName" />
    </association>
</resultMap>

<select id="findEmpAndDept2" resultMap="emp3">
    select e.*,d.id did,d.department_name from tbl_employee
    e left join tbl_department d on e.d_id = d.id where e.id = #{id}
</select>

测试类

@org.junit.Test
public void test03() throws IOException {
    //读取配置文件
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    //获取SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession openSession = sqlSessionFactory.openSession();
    //调用getMapper方法获取该接口的实现类
    EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
    Employee selectEmpById = mapper.findEmpAndDept2(1);
    System.out.println(selectEmpById);
    //关闭Session
    openSession.close();
}

运行结果

Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1, department=Department [id=1, departmentName=123]]

三, 使用association分步查询

优点:
- 可以使用简单的方法实现复杂的功能
- 可以使用延迟加载

接口

DepartmentMapper.java

package com.fish.dao;
import com.fish.pojo.Department;
public interface DepartmentMapper {
    /**
     * 根据id查询部门信息
     * @Author ZFH
     * @Date 2019年7月22日
     */
    public Department selDepartmentByID(Integer id);
}

EmployeeMapper.java

/**
 * 根据员工id查询员工信息以及所属部门的相关信息
 * 
 * @Author ZFH
 * @Date 2019年7月20日
 */
public Employee findEmpByIdStep(Integer id);

配置文件

DepartmentMapper.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.fish.dao.DepartmentMapper">
    <select id="selDepartmentByID"
        resultType="com.fish.pojo.Department">
        select id, department_name departmentName from tbl_department where id = #{id}
    </select>
</mapper>

EmployeeMapper.xml

<!-- 使用association分步查询
    1. 先按照员工id查询员工信息
    2. 根据员工信息中的d_id去查询部门信息
    3. 将部门信息设置到员工信息中
    流程: 调用select指定的方法传入column指定的值 查询对象 将结果封装到property对应的属性中
 -->
<resultMap type="com.fish.pojo.Employee" id="selEmployeeStep">
    <id column="id" property="id" />
    <result column="last_name" property="lastName" />
    <result column="email" property="email" />
    <result column="gender" property="gender" />
    <!-- association:定义关联对象的封装规则
        select:表名当前属性是调用select指定的方法查询出的结果
        column:指定将那一列的值传给这个方法
     -->
    <association property="department"
        select="com.fish.dao.DepartmentMapper.selDepartmentByID"
        column="d_id">
    </association>
</resultMap>

<select id="findEmpByIdStep" resultMap="selEmployeeStep">
    select * from
    tbl_employee where id = #{id}
</select>

主配置文件(mybatis-config.xml)

<mappers>
    <mapper resource="com/fish/dao/EmployeeMapper.xml" />
    <mapper resource="com/fish/dao/DepartmentMapper.xml" />
</mappers>

测试类

@org.junit.Test
public void test04() throws IOException {
    //读取配置文件
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    //获取SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession openSession = sqlSessionFactory.openSession();
    //调用getMapper方法获取该接口的实现类
    EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
    Employee selectEmpById = mapper.findEmpByIdStep(1);
    System.out.println(selectEmpById);
    //关闭Session
    openSession.close();

}

运行结果

Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1, department=Department [id=1, departmentName=123]]

association的延迟加载

延迟加载:每次查询的时候都是将员工信息与部门信息一起查询出来,使用延迟加载之后可以只查员工信息,部门的相关信息只有在用到的时候才会查询

实现:上面的方法不需改动,只需要在主配置文件里开启相应的配置即可

主配置文件(mybatis-config.xml)

<settings>
    <!-- 开启驼峰命名 -->
    <setting name="mapUnderscoreToCamelCase" value="false" />
    <!-- 开启懒加载 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- 关闭侵入式懒加载 即实现按需加载 -->
    <setting name="aggressiveLazyLoading" value="flase"/>
</settings>

四, collection嵌套结果集

需求:一个部门中包含多个员工,查询部门信息的时候将员工信息一块查出来

Department.java

package com.fish.pojo;
import java.util.List;
public class Department {

    private Integer id;
    private String departmentName;
    private List<Employee> employees;
}

接口(DepartmentMapper.java)

package com.fish.dao;
import com.fish.pojo.Department;
public interface DepartmentMapper {
    /**
     * 使用collection嵌套结果集查询
     * @Author ZFH
     * @Date 2019年7月24日
     */
    public Department selDeptByCol(Integer id);
}

配置文件(DepartmentMapper.xml)

<resultMap type="com.fish.pojo.Department" id="myDemp">
    <id column="d_id" property="id"/>
    <result column="department_name" property="departmentName"/>
    <!-- 
        collection:定义关联集合类型的属性的封装规则
        property:集合属性名称
        ofType:指定集合的元素的类型
     -->
    <collection property="employees" ofType="com.fish.pojo.Employee">
        <!-- 定义这个集合中元素的封装规则 -->
        <id column="id" property="id" />
        <result column="last_name" property="lastName" />
        <result column="email" property="email" />
        <result column="gender" property="gender" />
    </collection>
</resultMap>
<select id="selDeptByCol" resultMap="myDemp">
    select d.id d_id,department_name,e.* from tbl_department d LEFT JOIN tbl_employee e on d.id = e.d_id where d.id = 1
</select>

测试类

@org.junit.Test
public void test05() throws IOException {
    //读取配置文件
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    //获取SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession openSession = sqlSessionFactory.openSession();
    //调用getMapper方法获取该接口的实现类
    DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
    Department selDeptByCol = mapper.selDeptByCol(1);

    System.out.println(selDeptByCol);
    System.out.println(selDeptByCol.getEmployees());
    //关闭Session
    openSession.close();

}

运行结果

Department [id=1, departmentName=123]
[Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1], Employee [id=5, lastName=22, email=lisi@163.com, gender=1]]

collection分步查询(跟association分布查询相似)

接口(DepartmentMapper.java)

/**
 * 分步查询
 * 使用collection嵌套结果集查询
 * @Author ZFH
 * @Date 2019年7月24日
 */
public Department selDeptByColStep(Integer id);

EmployeeMapper.java

/**
 * 根据部门id查询员工信息
 * 
 * @Author ZFH
 * @Date 2019年7月20日
 */
public Employee findEmpByDeptId(Integer id);

配置文件(EmployeeMapper.xml)

<select id="findEmpByDeptId" resultType="com.fish.pojo.Employee">
    select id,last_name lastName,gender,email,d_id from
    tbl_employee where id = #{id}
</select>

DepartmentMapper.xml

<resultMap type="com.fish.pojo.Department" id="myDemp2">
    <id column="id" property="id"/>
    <result column="department_name" property="departmentName"/>
    <collection property="employees" 
        select="com.fish.dao.EmployeeMapper.findEmpByDeptId"
        column="id"></collection>
</resultMap>
<select id="selDeptByColStep"  resultMap="myDemp2">
    select * from tbl_department where id = #{id}
</select>

测试类

@org.junit.Test
public void test06() throws IOException {
    //读取配置文件
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    //获取SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession openSession = sqlSessionFactory.openSession();
    //调用getMapper方法获取该接口的实现类
    DepartmentMapper mapper = openSession.getMapper(DepartmentMapper.class);
    Department selDeptByCol = mapper.selDeptByColStep(1);

    System.out.println(selDeptByCol);
    System.out.println(selDeptByCol.getEmployees());
    //关闭Session
    openSession.close();

}

运行结果

Department [id=1, departmentName=123]
[Employee [id=1, lastName=222, email=ZFH_FISH@163.COM, gender=1]]

使用column传递多个参数

要想使用column传递多个参数的话,可以将参数封装成一个map,例如 column="{key1 = column1,key2 = column2}

局部懒加载(fetchType)

如果你的某一个方法不想使用懒加载,而你又不想修改主配置文件,那么你可以通过修改collection标签或association标签的fetchType属性的值来实现,fetchType取值:lazy(懒加载),eager(立即加载)

鉴别器(discriminator)

接口

EmployeeMapper.java

/**
 * 根据员工id查询员工信息以及所属部门的相关信息
 * 使用鉴别器
 * 
 * @Author ZFH
 * @Date 2019年7月20日
 */
public Employee findEmpByIdStep1(Integer id);

DepartmentMapper.java

/**
 * 根据id查询部门信息
 * @Author ZFH
 * @Date 2019年7月22日
 */

public Department selDepartmentByID(Integer id);

配置文件

DepartmentMapper.xml

<select id="selDepartmentByID"
    resultType="com.fish.pojo.Department">
    select id, department_name departmentName from tbl_department where id = #{id}
</select>

EmployeeMapper.xml

<resultMap type="com.fish.pojo.Employee" id="selEmployeeStep1">
    <id column="id" property="id" />
    <result column="last_name" property="lastName" />
    <result column="email" property="email" />
    <result column="gender" property="gender" />
    <!-- discriminator鉴别器:mybais可以使用discriminator去判断某一列的值,然后根据这个值去改变封装规则
            javaType:列值对应的java类型
            column:指定判定的列名
     -->
    <discriminator javaType="string" column="gender">
        <!-- 如果是女生,正常封装 resultType:指定封装的结果类型 -->
        <case value="0" resultType="com.fish.pojo.Employee">
            <association property="department"
                select="com.fish.dao.DepartmentMapper.selDepartmentByID"
                column="d_id">
            </association>
        </case>
        <!-- 如果是男生,email取last_name的值 -->
        <case value="1"  resultType="com.fish.pojo.Employee">
            <id column="id" property="id" />
            <result column="last_name" property="lastName" />
            <result column="last_name" property="email" />
            <result column="gender" property="gender" />
        </case>
    </discriminator>
    <!-- association:定义关联对象的封装规则
        select:表名当前属性是调用select指定的方法查询出的结果
        column:指定将那一列的值传给这个方法
     -->
</resultMap>

<select id="findEmpByIdStep1" resultMap="selEmployeeStep1">
    select * from
    tbl_employee where id = #{id}
</select>

测试类

@org.junit.Test
public void test07() throws IOException {
    //读取配置文件
    String resource = "mybatis-config.xml";
    InputStream inputStream = Resources.getResourceAsStream(resource);
    //获取SqlSessionFactory
    SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
    SqlSession openSession = sqlSessionFactory.openSession();
    //调用getMapper方法获取该接口的实现类
    EmployeeMapper mapper = openSession.getMapper(EmployeeMapper.class);
    Employee findEmpByIdStep1 = mapper.findEmpByIdStep1(5);

    System.out.println(findEmpByIdStep1);
    System.out.println(findEmpByIdStep1.getDepartment());
    //关闭Session
    openSession.close();

}

运行结果

---女
Employee [id=5, lastName=22, email=lisi@163.com, gender=0]
Department [id=1, departmentName=123]
---男
Employee [id=1, lastName=222, email=222, gender=1]
null