Springboot mybatis存储过程

一、什么是mysql存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。MySQL 5.0 版本开始支持存储过程。

直白点就是，本该在service层完成的逻辑操作，直接放到数据库内完成了。

好处：减少了与数据进行连接完成操作的过程，提高了执行效率

缺点：增加了数据库的负担，且移植性较差

二、存储过程的语法

以添加部门的代码为例

DELIMITER $$
 
USE `vhr`$$
 
DROP PROCEDURE IF EXISTS `addDep`$$
 
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 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 department WHERE id=parentId;
  UPDATE department SET depPath=CONCAT(pDepPath,'.',did) WHERE id=did;
  UPDATE department SET isParent=TRUE WHERE id=parentId;
END$$
 
DELIMITER ;
DELIMITER

定义
定义
为语句的结束符， 默认语句的结束符为";" 分号 ，防止语句写一句提交一句先设置为$$

USE `vhr`$$  使用数据库

DROP PROCEDURE IF EXISTS `addDep`$$   如果存在就删除addDep程序

CREATE DEFINER=`root`@`localhost` PROCEDURE `addDep`(IN depName VARCHAR(32),IN parentId INT,IN enabled BOOLEAN,OUT result INT,OUT result2 INT)    创建addDep程序 IN代表传入参数，OUT代表传出结果  

BEGIN 开始   END 结束

DECLARE 声明变量 

INSERT INTO department SET NAME=depName,parentId=parentId,enabled=enabled;  用传进来的参数添加一条语句

SELECT ROW_COUNT() INTO result;  查询刚才成功添加语句的行数  并把结果存入 result

  SELECT LAST_INSERT_ID() INTO did;  查询刚刚添加的语句 生成的id  并把结果存入did

  SET result2=did; 把did赋值给result2

  SELECT depPath INTO pDepPath FROM department WHERE id=parentId; 查询parentId的depPath  存入 pDepPath

  UPDATE department SET depPath=CONCAT(pDepPath,'.',did) WHERE id=did;  更新新添加的语句的 depPath， CONCAT(pDepPath,'.',did)拼接字符串

  UPDATE department SET isParent=TRUE WHERE id=parentId;  将父ID下的isParent设置为true

执行完成之后  数据库中显示创建的存储过程

三、controller、service、mapper层以及mapper.xml的语句

1、mapper.xml  

Statement的实现类：PreparedStatement 和 CallableStatement。

CALLABLE对应CallableStatement

call addDep（）就是调用addDep方法 里面就是创建的时候 设置的输入参数 与输出结果

 <!--调用存储过程进行数据存储-->

  <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>

2、mapper层、service层、controller层

其中controller层中值得说的一点是  传入的参数dep  会在执行完语句后 将存储过程返回的结果封装到dep里  不需要返回值 直接调用

//mapper层
void addDep(Department dep);
 
 
//service层
 public void addDepartment(Department dep) {
        dep.setEnabled(true);
        departmentMapper.addDep(dep);
  }
 
 
//controller层
 @PostMapping("/")
    public RespBean addDepartment(@RequestBody Department dep){
        departmentService.addDepartment(dep);
        if(dep.getResult() == 1){
            return RespBean.ok("添加成功",dep);
        }
        return RespBean.error("添加失败");
    }

四、测试结果fuji

 "status": 200,
    "msg": "添加成功",
    "obj": {
        "id": 108,
        "name": "运维三部",
        "parentId": 92,
        "depPath": null,
        "enabled": true,
        "children": [],
        "result": 1,
        "parent": null
    }
}

 附加：删除部门的存储过程的语句

语句逻辑：先判断要删除的部门是否是父部门，如果是，删除失败，返回结果为-2

                  再判断该部门是否有员工，如果有删除失败

                  然后就是直接删除 并判断是否还存在parentId与删除部门相同的部门，如果不存在

                   将 父部门的isParent设置为false

DELIMITER $$
 
USE `vhr`$$
 
DROP PROCEDURE IF EXISTS `deleteDep`$$
 
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 department WHERE id=did AND isParent=FALSE;
  IF a=0 THEN SET result=-2;
  ELSE
  SELECT COUNT(*) INTO ecount FROM employee WHERE departmentId=did;
  IF ecount>0 THEN SET result=-1;
  ELSE
  SELECT parentId INTO pid FROM department WHERE id=did;
  DELETE FROM department WHERE id=did AND isParent=FALSE;
  SELECT ROW_COUNT() INTO result;
  SELECT COUNT(*) INTO pcount FROM department WHERE parentId=pid;
  IF pcount=0 THEN UPDATE department SET isParent=FALSE WHERE id=pid;
  END IF;
  END IF;
  END IF;
END$$
 
DELIMITER ;