mysql+mybatis实现存储过程+事务 + 多并发流水号获取

PHP小丑

这篇文章主要介绍了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、检查数据源连接用户是否有存储过程执行权限
到此这篇关于mysql+mybatis实现存储过程+事务 + 多并发流水号获取的文章就介绍到这了,更多相关mysql mybatis存储过程流水号内容请搜索CodeAE代码之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持CodeAE代码之家！
原文链接：https://www.cnblogs.com/cocoat/p/5808684.html