云e办（后端）——数据库储存过程对部门分部管理

云e办（后端）——数据库储存过程对部门分部管理

数据存储过程就是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。

存储过程的介绍

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。
通俗来讲：存储过程其实就是能完成一定操作的一组SQL语句

优点：
存储过程可封装，并隐藏复杂的商业逻辑。
存储过程可以回传值，并可以接受参数。
存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。
存储过程可以用在数据检验，强制实行商业逻辑等。

缺点：
存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。
存储过程的性能调校与撰写，受限于各种数据库系统。

查看部门表：

parentId：表示父部门是谁
isParent：表示是否是父部门

depPath：

• 股东会1：是最高级别。
• 董事会2：parentId上一级是1股东会了。那么depPath路径就是1.2。
• 总办3：parentld上一级是2董事会，那么depPath路径就是1.2.3

除了我们获取所有部门之外，添加部门和删除部门都需要很多条sql语句进行执行，较为复杂。所以我们采用数据库存储过程来编译。

一、获取所有部门

1.pojo/Department

2.controller

package com.xxxx.server.controller;
 
import com.xxxx.server.pojo.Department;
import com.xxxx.server.service.IDepartmentService;
import io.swagger.annotations.ApiOperation;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestMapping;

import org.springframework.web.bind.annotation.RestController;

import java.util.List;
 
@RestController
@RequestMapping("/system/basic/department")
public class DepartmentController部门信息 {
    @Autowired
    private IDepartmentService iDepartmentService;

    /**
     * 获取所有的部门
     * @return
     */
    @ApiOperation(value = "获取所有部门")
    @GetMapping("/")
    public List<Department> getAllDepartments(){
        return iDepartmentService.getAllDepartments();
    }
}

3.service

    /**
     * 获取所有的部门
     * @return
     */
    List<Department> getAllDepartments();
---------------
    @Override
    public List<Department> getAllDepartments() {
        return departmentMapper.getAllDepartments(-1);
    }

4.mapper

    List<Department> getAllDepartments(Integer parentId);
<?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.xxxx.server.mapper.DepartmentMapper">

    <!-- 通用查询映射结果 -->
    <resultMap id="BaseResultMap" type="com.xxxx.server.pojo.Department">
        <id column="id" property="id" />
        <result column="name" property="name" />
        <result column="parentId" property="parentId" />
        <result column="depPath" property="depPath" />
        <result column="enabled" property="enabled" />
        <result column="isParent" property="isParent" />
    </resultMap>

    <resultMap id="DepartmentWithChildren" type="com.xxxx.server.pojo.Department" extends="BaseResultMap">
        <collection property="children" ofType="com.xxxx.server.pojo.Department"
                    select="com.xxxx.server.mapper.DepartmentMapper.getAllDepartments" column="id">
        </collection>
    </resultMap>

    <!-- 通用查询结果列 -->
    <sql id="Base_Column_List">
        id, name, parentId, depPath, enabled, isParent
    </sql>

    <!--获取所有部门-->
    <select id="getAllDepartments" resultMap="DepartmentWithChildren">
        select
        <include refid="Base_Column_List" />
        from t_department
        where parentId = #{parentId}
    </select>

二、添加部门

sql语句思路：

1.插入部门
2.查询插入部门id
3.查询插入id部门 的父部门的path
4.更新插入部门的path
5.更新插入部门的父部门的isParent为true

CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(in depName varchar(32),in parentId int,in enabled boolean,out result int,out result2 int)
begin
  declare did int;
  declare pDepPath varchar(64);
  insert into t_department set name=depName,parentId=parentId,enabled=enabled;
  select row_count() into result;
  select last_insert_id() into did;
  set result2=did;
  select depPath into pDepPath from t_department where id=parentId;
  update t_department set depPath=concat(pDepPath,'.',did) where id=did;
  update t_department set isParent=true where id=parentId;
end

1.controller

    @ApiOperation(value = "添加部门")
    @PostMapping("/")
    public RespBean addDep(@RequestBody Department dep){
        return iDepartmentService.addDep(dep);
    }

2.service

RespBean addDep(Department dep);
  
@Override
public RespBean addDep(Department dep) {
    dep.setEnabled(true);
    departmentMapper.addDep(dep);
    if (1 == dep.getResult()){
        return RespBean.success("添加成功",dep);
    }
    return RespBean.error("添加失败");
}

3.mapper

    /**
     * 添加部门
     * @param dep
     */
    void addDep(Department dep);
------------

 <!--添加部门-->
 <!--statementType=CALLABLE :调用执行过程-->
 <select id="addDep" statementType="CALLABLE">
     call addDep(#{name,mode=IN,jdbcType=VARCHAR},#{parentId,mode=IN,jdbcType=INTEGER},#{enabled,mode=IN,
     jdbcType=BOOLEAN},#{result,mode=OUT,jdbcType=INTEGER},#{id,mode=OUT,jdbcType=INTEGER})
 </select>

三、删除部门

sql语句思路：

1.查询删除部门的id的isParent=flase（因为如果所删除部门是其他部门的父部门，则不能删除）
如果为0，不能删除返回-2；
2.查询员工表的departmentId=did（因为部门下如果有员工也不能直接删除）
如果大于0，不能删除返回-1；
3.查询所删除部门的父部门id
4.如果isParent=false，则删除所要删除的部门
5.查询父部门id是否为0条数据了
6.如果为0，则更新父部门的isParent=false，如果不是0，说明父部门还有其他的子部门，则不需要更新父部门的isParent

CREATE DEFINER=`root`@`localhost` PROCEDURE `deleteDep`(in did int,out result int)
begin
  declare ecount int;
  declare pid int;
  declare pcount int;
  declare a int;
  select count(*) into a from t_department where id=did and isParent=false;
  if a=0 then set result=-2;
  else
  select count(*) into ecount from t_employee where departmentId=did;
  if ecount>0 then set result=-1;
  else 
  select parentId into pid from t_department where id=did;
  delete from t_department where id=did and isParent=false;
  select row_count() into result;
  select count(*) into pcount from t_department where parentId=pid;
  if pcount=0 then update t_department set isParent=false where id=pid;
  end if;
  end if;
  end if;
end

1.controller

    @ApiOperation(value = "删除部门")
    @DeleteMapping("/{id}")
    public RespBean deleteDep(@PathVariable Integer id){
        return iDepartmentService.deleteDep(id);
    }

2.service

    /**
     * 删除部门
     * @param id
     * @return
     */
    RespBean deleteDep(Integer id);
 
    /**
     * 删除部门
     * @param id
     * @return
     */
    @Override
    public RespBean deleteDep(Integer id) {
        Department dep = new Department();
        dep.setId(id);
        departmentMapper.deleteDep(dep);
        if(-2 == dep.getResult()){
            return RespBean.error("该部门下有子部门，删除失败");
        }
        if(-1 == dep.getResult()){
            return RespBean.error("该部门下有员工，删除失败");
        }
        if (1 == dep.getResult()){
            return RespBean.success("删除成功");
        }
        return RespBean.error("删除失败");
    }

3.mapper

    /**
     * 删除部门
     * @param dep
     */
    void deleteDep(Department dep);

---- 

 <!--删除部门-->
<select id="deleteDep" statementType="CALLABLE">
    call deleteDep(
        #{id,mode=IN,jdbcType=INTEGER},
        #{result,mode=OUT,jdbcType=INTEGER}
        )
</select>