数据库存储过程
@H_502_3@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
流水号表
@H_502_3@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配置
@H_502_3@<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>
测试代码
@H_502_3@@Override
public Map<String,Object> generateSerialNumber(Map<String,Object> param) {
sysSerialNumberMapper.generateSerialNumber(param);
return param;
}
@H_502_3@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();
}
@H_502_3@### 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、检查数据源连接用户是否有存储过程执行权限