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配置

测试代码

@Override
    public Map generateSerialNumber(Map param) {
        sysSerialNumberMapper.generateSerialNumber(param);
        return param;
    }

final Map param = new HashMap();
        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 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 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 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存储过程流水号内容请搜索潘少俊衡以前的文章或继续浏览下面的相关文章希望大家以后多多支持潘少俊衡！