多级联联动查询

部门多级联动

1、部门都在一张表中

1.1 数据库表

/*
 Navicat Premium Data Transfer

 Source Server         : SuggestCollection
 Source Server Type    : MySQL
 Source Server Version : 80016
 Source Host           : rm-uf6k821nmo8j76x81to.mysql.rds.aliyuncs.com:3306
 Source Schema         : dev_jianpu_suggest

 Target Server Type    : MySQL
 Target Server Version : 80016
 File Encoding         : 65001

 Date: 25/09/2020 17:50:04
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for sys_dept
-- ----------------------------
DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept`  (
  `dept_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '部门id',
  `parent_id` bigint(20) NULL DEFAULT 0 COMMENT '父部门id',
  `ancestors` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '祖级列表',
  `dept_name` varchar(30) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '部门名称',
  `order_num` int(4) NULL DEFAULT 0 COMMENT '显示顺序',
  `leader` varchar(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '负责人',
  `phone` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '联系电话',
  `email` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL COMMENT '邮箱',
  `status` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '部门状态（0正常 1停用）',
  `del_flag` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '0' COMMENT '删除标志（0代表存在 2代表删除）',
  `create_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '创建者',
  `create_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `update_by` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT '' COMMENT '更新者',
  `update_time` datetime(0) NULL DEFAULT NULL COMMENT '更新时间',
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 279 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci COMMENT = '部门表' ROW_FORMAT = Dynamic;

1.2 前端html

	<li>
        <label>提交部门：</label>
        <!-- 工厂选择 -->
        <select id="factoryId" name="factoryId">
        </select>
        <!-- 一级部门选择 -->
        <select id="departmentId" name="departmentId" >
        </select>
        <!-- 二级部门选择 -->
        <select id="workshopId" name="workshopId" >
        </select>
    </li>

1.3 js

<script th:inline="javascript">
	$(function() {
		getFactory();//加载工厂

        $("#factoryId").change(function(){//点击工厂时
            getDeptNameOne();//重新加载一级部门
        });
        $("#departmentId").change(function(){//点击一级部门时候
            getDeptNameTwo();//加载二级部门的数据
        });
	});

	function getFactory() {
       $.ajax({
            url: ctx + "system/dept/getFactory",
            cache: false,
            contentType: false,
            processData: false,
            type: 'GET',
            success: function (result) {
                if(result.status == 200){
                    var factory = result.deptArray;
                    // var str = "";
                    var str = "<option value='' selected>"+'默认工厂'+"</option>";
                    for(var i=0;i<factory.length;i++){
                        str += "<option value='"+factory[i].value+"'>"+factory[i].text+"</option>";
                        $("#factoryId").html(str);
                    }
                }
            }
        });
    }

	function getDeptNameOne() {
        var factoryId = $("#factoryId").val();//找工厂的父级代号，工厂选中项的值
        if(factoryId === null || factoryId === undefined || factoryId === ''){
            var str1 = "";
            str1 += "";
            $("#departmentId").html(str1);
            $("#workshopId").html(str1);
            return;
        }
        debugger
        console.log(factoryId);
        $.ajax({
            url: ctx + "system/dept/getFactory?parentId="+factoryId,
            cache: false,
            contentType: false,
            processData: false,
            type: 'GET',
            success: function (result) {
                if(result.status == 200){
                    var department = result.deptArray;
                    console.log(department);
                    // var str = "";
                    // var str = "<option value='1' selected>"+'默认一级部门'+"</option>";
                    var str = "<option value='' selected>"+'全部'+"</option>";
                    if(department.length === 0){
                        str += "";
                        $("#departmentId").html(str);
                    }else{
                        for(var i=0;i<department.length;i++){
                            str += "<option value='"+department[i].value+"'>"+department[i].text+"</option>";
                            $("#departmentId").html(str);
                        }
                        getDeptNameTwo();
                    }
                }
            }
        });
    }
    function getDeptNameTwo() {
        debugger
        var departmentId = $("#departmentId").val();//找一级部门的父级代号，一级部门选中项的值
        if(departmentId === null || departmentId === undefined || departmentId === ''){
            var str1 = "";
            str1 += "";
            $("#workshopId").html(str1);
            return;
        }
        $.ajax({
            url: ctx + "system/dept/getFactory?parentId="+departmentId,
            cache: false,
            contentType: false,
            processData: false,
            type: 'GET',
            success: function (result) {
                if(result.status == 200){
                    var workshop = result.deptArray;
                    // var str = "";
                    // var str = "<option value='1' selected>"+'默认二级部门'+"</option>";
                    var str = "<option value='' selected>"+'全部'+"</option>";
                    if(workshop.length === 0) {
                        str += "";
                        $("#workshopId").html(str);
                    }else{
                        for (var i = 0; i < workshop.length; i++) {
                            str += "<option value='" + workshop[i].value + "'>" + workshop[i].text + "</option>";
                            $("#workshopId").html(str);
                        }

                    }
                }
            }
        });
    }
</script>

1.4 controller

	/**
     * 根据父部门id查询其下的子部门（部门的联动）
     * @return
     */
    @GetMapping("getFactory")
    @ResponseBody
    public JSONObject getFactory(Long parentId){

        if(parentId == null || parentId < 0){
            parentId = 102L;
        }
        JSONObject result = new JSONObject();
        JSONArray deptArray = deptService.reqDeptListByParentId(parentId);
        result.put("deptArray", deptArray);
        result.put("status", 200);
        result.put("msg", "获取数据成功");
        return result;
    }

1.5 service

	/**
     *
     * 根据父级id获取数据
     * @param parentId
     * @return
     */
    @Override
    public JSONArray reqDeptListByParentId(long parentId) {
        List<Dept> deptList = deptMapper.selectDeptList(new Dept(){{setParentId(parentId);}});
        JSONArray deptArray = new JSONArray();
        deptList.forEach(
                i->{
                    JSONObject deptObj = new JSONObject();
//                    deptObj.put("id",i.getDeptId());
//                    deptObj.put("name",i.getDeptName());
                    deptObj.put("value",i.getDeptId());
                    deptObj.put("text",i.getDeptName());
                    deptArray.add(deptObj);
                }
        );
        return deptArray;
    }

1.6 map.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.ruoyi.project.system.dept.mapper.DeptMapper">

	<resultMap type="Dept" id="DeptResult">
		<id     property="deptId"     column="dept_id"     />
		<result property="parentId"   column="parent_id"   />
		<result property="ancestors"  column="ancestors"   />
		<result property="deptName"   column="dept_name"   />
		<result property="orderNum"   column="order_num"   />
		<result property="leader"     column="leader"      />
		<result property="phone"      column="phone"       />
		<result property="email"      column="email"       />
		<result property="status"     column="status"      />
		<result property="delFlag"    column="del_flag"    />
		<result property="parentName" column="parent_name" />
		<result property="createBy"   column="create_by"   />
		<result property="createTime" column="create_time" />
		<result property="updateBy"   column="update_by"   />
		<result property="updateTime" column="update_time" />
	</resultMap>

	<sql id="selectDeptVo">
        select d.dept_id, d.parent_id, d.ancestors, d.dept_name, d.order_num, d.leader, d.phone, d.email, d.status, d.del_flag, d.create_by, d.create_time 
        from sys_dept d
    </sql>

	<select id="selectDeptList" parameterType="Dept" resultMap="DeptResult">
        <include refid="selectDeptVo"/>
        where d.del_flag = '0'
        <if test="parentId != null and parentId != 0">
			AND parent_id = #{parentId}
		</if>
		<if test="deptName != null and deptName != ''">
			AND dept_name like concat('%', #{deptName}, '%')
		</if>
		<if test="status != null and status != ''">
			AND status = #{status}
		</if>
		<!-- 数据范围过滤 -->
		${params.dataScope}
		order by d.parent_id, d.order_num
    </select>

</mapper>