mysql+mybatis實現(xiàn)存儲過程+事務(wù)?+?多并發(fā)流水號獲取
數(shù)據(jù)庫存儲過程
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();
}如果運(yùn)行代碼報以下錯誤
### 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、檢查存儲過程是否正確創(chuàng)建
2、檢查數(shù)據(jù)源連接用戶是否有存儲過程執(zhí)行權(quán)限
到此這篇關(guān)于mysql+mybatis實現(xiàn)存儲過程+事務(wù) + 多并發(fā)流水號獲取的文章就介紹到這了,更多相關(guān)mysql mybatis存儲過程流水號內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信