MyBatis如何调用Mysql存储过程

叁歲伎倆 2023-02-24 08:12 14阅读 0赞

### 文章目录 ###

*   *   *  数据库相关
         *  代码相关

> 阿里规约中不推荐使用存储过程。本文目的仅在于学习MyBatis如何调用存储过程。
> 
> 本文以添加部门为例。

### 数据库相关 ###

1.  表结构
    
    ![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWFpODYyNA_size_16_color_FFFFFF_t_70_pic_center]
2.  添加部门的函数addDep()
    
        CREATE DEFINER=`root`@`%` 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

### 代码相关 ###

Department.java

public class Department implements Serializable { 
        private Integer id;
        private String name;
        privateInteger parentId;
        private String depPath;
        private Boolean enabled;
        private Boolean isParent;
        private List<Department> children = new ArrayList<>();
        private Integer result; //临时存储添加成功的条数
        //省略get set

DepartmentMapper.xml

<insert id="addDept" parameterType="com.chouxiaozi.vhr.model.Department" 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})
      </insert>

DepartmentMapper.java

void addDept(Department department);

> \[强制\] 调用存储过程还是比较简单的存储过程难以调试和扩展，更没有移植性------《阿里巴巴Java开发手册泰山版》

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NodWFpODYyNA_size_16_color_FFFFFF_t_70_pic_center]: /images/20230209/122b591cbf884f709614249bd50808de.png