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;
}
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;
}
上一篇: mysqlbinlog 导出指定时间段sql到文件 mysql
下一篇: oracle知识杂记