mysql+mybatis+存储过程+事务 + 多并发流水号获取
发布时间:2020-12-12 02:09:20 所属栏目:MySql教程 来源:网络整理
导读:数据库存储过程 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 ,1)">IN param_period_date_format 20 )) begin declare old_date
数据库存储过程 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,1)">IN param_period_date_format 20)) begin declare old_datestr ); START TRANSACTION; if param_period_date_format='infinite' then set current_datestr = 00000000'; else = DATE_FORMAT(NOW(),param_period_date_format); end if; select numberfrom 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,values(param_key,param_org_id,param_period_date_format,current_datestr,1,add by procedure); elseif old_datestr != current_datestr sys_serial_number set number current_datestr param_key param_org_id param_period_date_format; update sys_serial_number = + 1 param_period_date_format; commit; end 流水号表 TABLE `sys_serial_number`; TABLE `sys_serial_number` ( `table_key` 100) NOT NULL COMMENT 主键(建议用表名)bigint(20) NULL DEFAULT 0' COMMENT 分公司IDnumber` 1流水号(存储过程控制递增,获取完后+1)流水号生成周期日期格式20) DEFAULT 流水号日期值100) 描述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,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,1)">(); 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(); serialNumberProvider.generateSerialNumber(param); System.out.println("thread-2:" + map.get("result" serialNumberProvider.generateSerialNumber(param); System.out.println("thread-3:" + map.get("result"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、检查数据源连接用户是否有存储过程执行权限 (编辑:李大同) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |