mybatis一对多的查询的两种方式

原创已于 2022-06-29 09:47:53 修改 · 2.4k 阅读

10 ·

CC 4.0 BY-SA版权

文章标签：

#mybatis #数据库 #sql

于 2022-06-29 09:45:36 首次发布

java 专栏收录该内容

4 篇文章

订阅专栏

本文介绍了如何使用MyBatis进行多级关联查询，包括部门、组织和员工的树形结构数据查询。提供了两种不同的SQL写法，一种是分别定义resultMap和单表查询SQL，另一种是通过多表联查实现。这两种方法各有优缺点，可以根据实际需求选择。文章还展示了对应的实体类和查询结果。

需求简介：

有3张表，部门 ---->组织 ---->员工，一个部门包含多个组织，每个组织下有多名员工，现在需要根据部门ID查询某个部门下的所有组织，以及每个组织下的所有员工信息，返回的是一个树形结构数据。

一、表结构

1.部门表
CREATE TABLE `department` (
  `id` int NOT NULL,
  `dept_name` varchar(255) DEFAULT NULL,
  `dept_location` varchar(255) DEFAULT NULL,
  `tenant_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

2.组织表
CREATE TABLE `groups` (
  `id` int NOT NULL,
  `group_name` varchar(255) DEFAULT NULL,
  `dept_id` int NOT NULL,
  `tenant_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

3.员工表
CREATE TABLE `employee` (
  `id` int NOT NULL,
  `employee_name` varchar(255) DEFAULT NULL,
  `group_id` int NOT NULL,
  `tenant_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

二、实体类

1.部门

@Data
@TableName("department")
public class Department {
	private Integer id;
	private String deptName;
	private String deptLocation;
	private List<Groups> groupList;
}
2.组织

@Data
@TableName("groups")
public class Groups {
	private Integer id;
	private String groupName;
	private List<Employee> employeeList;
}

3.员工
@Data
@TableName("employee")
public class Employee {
	private Integer id;
	private String employeeName;
}

三、查询方法

//根据部门ID查询
@Mapper
public interface DepartmentMapper extends BaseMapper<Department> {
    List<Department> testMybatis(@Param("id") Integer id);
}

四、mapper.xml，sql有两种写法，查询结果是一样的
第一种：

<resultMap type="com.entity.Department" id="departmentMap">
        <id property="id" column="id"/>
        <result property="deptName" column="dept_name"/>
        <result property="deptLocation" column="dept_location"/>
        <collection property="groupList" ofType="com.entity.Groups"
                    javaType="ArrayList" column="id" select="selectGroup"/>
    </resultMap>

    <resultMap type="com.entity.Groups" id="groupMap">
        <id property="id" column="id"/>
        <result property="groupName" column="group_name"/>
        <collection property="employeeList" ofType="com.entity.Employee"
                    javaType="ArrayList" column="id" select="selectEmployee"/>
    </resultMap>

    <resultMap type="com.entity.Employee" id="employeeMap">
        <id property="id" column="id"/>
        <result property="employeeName" column="employee_name"/>
    </resultMap>

    <select id="testMybatis" resultMap="departmentMap">
        SELECT
            a.id,
            a.dept_name,
            a.dept_location
        FROM
            department a
        WHERE a.id = #{id}
    </select>

    <select id="selectGroup" resultMap="groupMap">
        SELECT
            b.id,
            b.group_name
        FROM
            `groups` b
        WHERE
            b.dept_id = #{id}
    </select>

    <select id="selectEmployee" resultMap="employeeMap">
        SELECT
            c.id,
            c.employee_name
        FROM
            employee c
        WHERE
            c.group_id = #{id}
    </select>

第二种：

<resultMap id="departmentMap" type="com.entity.Department">
        <id property="id" column="id"/>
        <result column="dept_name" jdbcType="VARCHAR" property="deptName"/>
        <result column="dept_location" jdbcType="VARCHAR" property="deptLocation"/>
        <collection property="groupList" ofType="com.entity.Groups">
            <id property="id" column="gid"/>
            <result column="group_name" property="groupName"/>
            <collection property="employeeList" ofType="com.entity.Employee">
                <id property="id" column="eid"/>
                <result column="employee_name" property="employeeName"/>
            </collection>
        </collection>
    </resultMap>

    <select id="testMybatis" resultMap="departmentMap">
        SELECT
            a.id,
            a.dept_name,
            a.dept_location,
            b.id as gid,
            b.group_name,
            c.id as eid,
            c.employee_name
        FROM department a
        LEFT JOIN `groups` b ON a.id = b.dept_id
        LEFT JOIN `employee` c ON b.id = c.group_id
        WHERE a.id = #{id}
    </select>