MyBatis如何调用Mysql存储过程

阿里规约中不推荐使用存储过程。本文目的仅在于学习MyBatis如何调用存储过程。

本文以添加部门为例。

数据库相关

1. 表结构

   

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开发手册泰山版》