數(shù)據(jù)庫存儲過程
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
|
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 |
流水號表
|
1
2
3
4
5
6
7
8
9
10
|
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配置
|
1
2
3
4
5
6
7
8
9
10
11
|
<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> |
測試代碼
|
1
2
3
4
5
|
@Override public Map<String, Object> generateSerialNumber(Map<String, Object> param) { sysSerialNumberMapper.generateSerialNumber(param); return param; } |
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
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、檢查存儲過程是否正確創(chuàng)建
2、檢查數(shù)據(jù)源連接用戶是否有存儲過程執(zhí)行權(quán)限
到此這篇關(guān)于mysql+mybatis實現(xiàn)存儲過程+事務(wù) + 多并發(fā)流水號獲取的文章就介紹到這了,更多相關(guān)mysql mybatis存儲過程流水號內(nèi)容請搜索服務(wù)器之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持服務(wù)器之家！
原文鏈接：https://www.cnblogs.com/cocoat/p/5808684.html
