极简教程，MyBatis调用MySQL存储过程

MyBatis调用MySQL存储过程

1，创建两个表

CREATE TABLE `test_user`  (
  `user_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `user_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `user_password` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `dept_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`user_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

CREATE TABLE `test_dept`  (
  `dept_id` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `dept_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`dept_id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

2，创建存储过程

存储过程语法
CREATE PROCEDURE 过程名（【入参或出参】 【参数名】 【参数类型】）
begin
存储过程要执行的SQL语句
end

CREATE PROCEDURE insert_user (
	IN u_id VARCHAR ( 32 ),
	IN u_name VARCHAR ( 64 ),
	IN u_password VARCHAR ( 64 ),
	IN d_id VARCHAR ( 32 ),
	IN d_name VARCHAR ( 64 ) ) 
BEGIN
	INSERT INTO test_user ( user_id, user_name, user_password, dept_id )
		VALUES
			( u_id, u_name, u_password, d_id );
	INSERT INTO test_dept ( dept_id, dept_name )
		VALUES
		( d_id, d_name );
END

3，创建DAO层

@Repository
public interface UserDao {

    public int insertUser(User user);
    
}

4，创建Mapper

<!--insertUser对应dao层方法名，statementType="CALLABLE"必须要加，标注为调用存储过程
  insert_user对应存储过程名，userId对应传过来的参数名-->
<insert id="insertUser" parameterType="user" statementType="CALLABLE">
		{call insert_user(
		#{userId,mode=IN},#{userName,mode=IN},#{userPassword,mode=IN},#{deptId,mode=IN},#{deptName,mode=IN}
			)
		}
	</insert>

5，创建Controller

	@Transactional
    @PostMapping("/insertUser")
    public Result<Object> saveUser(@NotNull @RequestBody User user) {
        try {
            user.setUserId(CommonUtil.UUID());
            user.setDeptId(CommonUtil.UUID());
            userService.insertUser(user);
            return Result.success("保存数据成功!");
        } catch (Exception ex) {
            logger.error("更新数据失败!");
            return Result.error(ResultCode.updateFail, "更新数据失败!" + ex.getMessage());
        }
    }

6，PostMan测试结果

7，数据库结果