mysql+mybatis實現(xiàn)存儲過程+事務?+?多并發(fā)流水號獲取

更新時間：2021年12月31日 15:13:28 作者：旋轉(zhuǎn)的夢

這篇文章主要介紹了mysql+mybatis+存儲過程+事務?+?多并發(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();
        }

如果運行代碼報以下錯誤

### 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)存儲過程+事務 + 多并發(fā)流水號獲取的文章就介紹到這了,更多相關(guān)mysql mybatis存儲過程流水號內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: