mysql+mybatis实现存储过程+事务 + 多并发流水号获取
数据库存储过程
DROP PROCEDURE IF EXISTS `generate_serial_number_by_date`;
CREATE PROCEDURE `generate_serial_number_by_date`(
IN param_key varchar(100),
IN param_org_id bigint,
IN param_period_date_format varchar(20),
OUT result bigint,
OUT current_datestr varchar(20))
begin
declare old_datestr varchar(20);
START TRANSACTION;
if param_period_date_format='infinite' then
set current_datestr = '00000000';
else
set current_datestr = DATE_FORMAT(NOW(), param_period_date_format);
end if;
select
number, datestr
from sys_serial_number
where table_key = param_key
and org_id = param_org_id
and period_date_format = param_period_date_format
into result, old_datestr
for update;
IF result is null then
set result = 1;
insert into sys_serial_number(table_key, org_id, period_date_format, datestr, number, description)
values(param_key, param_org_id, param_period_date_format, current_datestr, 1, 'add by procedure');
elseif old_datestr != current_datestr then
set result = 1;
update sys_serial_number
set number = 1,
datestr = current_datestr
where table_key = param_key
and org_id = param_org_id
and period_date_format = param_period_date_format;
end if;
update sys_serial_number set number = number + 1
where table_key = param_key
and org_id = param_org_id
and period_date_format = param_period_date_format;
commit;
end
流水号表
DROP TABLE IF EXISTS `sys_serial_number`;
CREATE TABLE `sys_serial_number` (
`table_key` varchar(100) NOT NULL COMMENT '主键（建议用表名）',
`org_id` bigint(20) NOT NULL DEFAULT '0' COMMENT '分公司ID',
`number` bigint(20) NOT NULL DEFAULT '1' COMMENT '流水号（存储过程控制递增，获取完后+1）',
`period_date_format` varchar(20) NOT NULL COMMENT '流水号生成周期日期格式',
`datestr` varchar(20) DEFAULT NULL COMMENT '流水号日期值',
`description` varchar(100) DEFAULT NULL COMMENT '描述',
PRIMARY KEY (`table_key`,`org_id`,`period_date_format`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='流水号生成表';
mybatis配置
<select id="generateSerialNumber" parameterType="java.util.HashMap" statementType="CALLABLE">
<![CDATA[
{
call generate_serial_number (
#{param_key,mode=IN,jdbcType=VARCHAR},
#{param_org_id,mode=IN,jdbcType=BIGINT},
#{result,mode=OUT,jdbcType=BIGINT}
)
}
]]>
</select>
测试代码
@Override
public Map<String, Object> generateSerialNumber(Map<String, Object> param) {
sysSerialNumberMapper.generateSerialNumber(param);
return param;
}
final Map<String, Object> param = new HashMap<String, Object>();
param.put("param_key","contract");
param.put("param_orgId", 84);
new Thread(new Runnable() {
@Override
public void run() {
for(int i =0; i<100; i++) {
Map<String, Object> map = serialNumberProvider.generateSerialNumber(param);
System.out.println("thread-1：" + map.get("result"));
}
}
}).start();
new Thread(new Runnable() {
@Override
public void run() {
for(int i =0; i<100; i++) {
Map<String, Object> map = serialNumberProvider.generateSerialNumber(param);
System.out.println("thread-2：" + map.get("result"));
}
}
}).start();
new Thread(new Runnable() {
@Override
public void run() {
for(int i =0; i<100; i++) {
Map<String, Object> map = serialNumberProvider.generateSerialNumber(param);
System.out.println("thread-3：" + map.get("result"));
}
}
}).start();
byte[] b = new byte[0];
synchronized(b) {
b.wait();
}
如果运行代码报以下错误
### SQL:
{
call generate_serial_number_by_date (
?, ?, ?, ?, ?
)
}
### Cause: java.sql.SQLException: Parameter number 4 is not an OUT parameter
; SQL []; Parameter number 4 is not an OUT parameter; nested exception is java.sql.SQLException: Parameter number 4 is not an OUT parameter
排查方法：
1、检查存储过程是否正确创建
2、检查数据源连接用户是否有存储过程执行权限
到此这篇关于mysql+mybatis实现存储过程+事务 + 多并发流水号获取的文章就介绍到这了,更多相关mysql mybatis存储过程流水号内容请搜索软件开发网以前的文章或继续浏览下面的相关文章希望大家以后多多支持软件开发网！
