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

<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、检查存储过程是否正确创建

2、检查数据源连接用户是否有存储过程执行权限