springboot mybatis调用mysql创建的带事务的存储过程并返回值

一、创建存储过程

CREATE ` PROCEDURE `alterSaleBespeakTime`(
in bespeakId int, in oldAmountId int, in newAmountId int,
in newBespeakDate date,in newStartTime varchar(50),in newEndTime varchar(50), 
in oldBespeakDate date,oldStartTime varchar(50),in oldEndTime varchar(50),
out result int)
BEGIN
	-- 定义错误码
	DECLARE t_error INTEGER DEFAULT 0;   
	-- 当出现异常时错误码赋1
	DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET t_error=1; 
	-- 开启事务
	start TRANSACTION;
	-- 开始事务处理
	-- 修改司机预约时间
	update sale_driver_bespeak set start_time = newStartTime,end_time = newEndTime,bespeak_date = newBespeakDate,alter_time = now() where id = bespeakId;
	-- 修改旧预约量的时间
	update sale_bespeak_amount set start_time = newStartTime,end_time = newEndTime,bespeak_date = newBespeakDate where id = oldAmountId;
	-- 修改新预约量的时间
	update sale_bespeak_amount set start_time = oldStartTime,end_time = oldEndTime,bespeak_date = oldBespeakDate where id = newAmountId;
	-- 如果出现异常回滚事务，否则提交事务
	IF t_error = 1 THEN  
		ROLLBACK; 
		set result = 0;
	ELSE    
    COMMIT;
		set result = 1;
  END IF;
	END

二、mybatis mapper.xml中调用存储过程并获取返回值

    <select id="alterSaleBespeakTime" resultType="java.lang.Integer">
        set @result=0;
        call alterSaleBespeakTime(#{param1},#{param2},#{param3},#{param4},#{param5},
            #{param6},#{param7},#{param8},#{param9},@result);
        select @result;
    </select>