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

数据库存储过程

?

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