數據庫存儲過程
|
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、檢查存儲過程是否正確創建
2、檢查數據源連接用戶是否有存儲過程執行權限
到此這篇關于mysql+mybatis實現存儲過程+事務 + 多并發流水號獲取的文章就介紹到這了,更多相關mysql mybatis存儲過程流水號內容請搜索服務器之家以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持服務器之家！
原文鏈接：https://www.cnblogs.com/cocoat/p/5808684.html
