需求简介:
有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>
五、查询结果
备注:
关于查询sql的写法,第一种定义了3个resultMap,对应3个单表查询sql,比较容易理解,条理关系清晰,但是代码比较多。第二种代码量少一些,采用多表关联查询,只有一个resultMap,可以根据实际情况来选择使用哪种方式。至于两者性能,还没研究过,有空再补。