首页 > 分享 > mybatis存储过程及mode=IN,mode=OUT的使用

mybatis存储过程及mode=IN,mode=OUT的使用

花匠小妙招
2024-12-14 18:12

[mybatis]Mapper.mxl中mode=IN，mode=OUT使用场景
需配合 statementType=“CALLABLE” 使用

存储过程有三种类型的参数，分别为 IN（输入参数），OUT（输出参数），INOUT（输入输出参数）。一个存储过程，可以有多个 IN 参数，至多有一个 OUT 或 INOUT 参数

只有 IN 参数的存储过程不多说

有 INOUT 或 OUT 参数的存储过程

addUser.sql

<select id="addUser" 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> 123

执行过程

/* Procedure structure for procedure `addDep` */ /*!50003 DROP PROCEDURE IF EXISTS `addDep` */; DELIMITER $$ /*!50003 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 ;

12345678910111213141516171819

<select id="deleteUserById" statementType="CALLABLE"> call deleteDep(#{id,mode=IN,jdbcType=INTEGER},#{result,mode=OUT,jdbcType=INTEGER}) </select> 123

执行过程

/* Procedure structure for procedure `deleteDep` */ /*!50003 DROP PROCEDURE IF EXISTS `deleteDep` */; DELIMITER $$ /*!50003 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 ;

12345678910111213141516171819202122232425262728

java部分

public interface UserMapper {void addUser(User user); void deleteUserById(User user); } 1234

@Autowired UserMapper usemapper; public addUser(User user) {usermapper.addUser(user); } 12345

@Autowired UserService userService; @PostMapping("/") public RespBean addUser(@RequestBody User user) { userService.addUser(user); if (user.getResult() == 1) { return RespBean.ok("添加成功", user); } return RespBean.error("添加失败"); } 1234567891011