欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

mybatis调用mysql存储过程

程序员文章站 2022-07-13 08:13:45
...
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;
}
相关标签: mysql