mybatis调用mysql存储过程

mybatis调用mysql存储过程

1.mybastis的配置
<select id="grabRedPacketProcedure" parameterMap="paramMap" statementType="CALLABLE">
    call grabRedPacketProcedure(?,?,?)
    </select>
   
    <parameterMap type="java.util.Map" id="paramMap">
    <parameter property="redPacketId" mode="IN" javaType="INTEGER"/>
    <parameter property="userId" mode="IN" javaType="INTEGER"/>
    <parameter property="num" mode="OUT" javaType="INTEGER" jdbcType="INTEGER"/>
    </parameterMap>

2.mapper
@Mapper
public interface RedPacketMapper {

public int insertRedPacketUser(RedPacketUserPO po);

//调用存储过程
public void grabRedPacketProcedure(Map<String, Integer> map);
}
   
3.mysql存储过程
delimiter //
DROP PROCEDURE IF EXISTS grabRedPacketProcedure
//
create PROCEDURE grabRedPacketProcedure(IN red_packet_id INTEGER,IN user_id INTEGER,OUT num INTEGER)
begin
declare total int;
set num = -1;
select stock into total from red_packet where id = red_packet_id;
if total > 0 then
start transaction;
update red_packet set stock=stock-1 where id = red_packet_id;
insert into red_packet_user(red_packet_id,user_id,amount) values (red_packet_id,user_id,5);
commit;
set num = 1;
end if;
end
//
DELIMITER ;

存储过程测试:
DELIMITER //
set @num=0;
call grabRedPacketProcedure(1,1,@num);
select @num;
//
DELIMITER ;
4.存储过程的调用
public int grapRedPacketProc(Integer redPacketId, Integer userId) {
int num = -1;
Map<String,Integer> map = new HashMap<String, Integer>();
redPacketMapper.grabRedPacketProcedure(map);
if(map.containsKey("num")) {
num = map.get("num");
}
return num;
}