mybatis+mysql 使用存储过程生成流水号的实现代码

使用存储过程，在操作数据库时开启事务，避免并发时同时操作造成数据重复

create definer=`root`@`localhost` procedure `getserialno`(in tscode varchar(50),out result varchar(200) )
begin 
 declare tsvalue varchar(50); 
 declare tdtoday varchar(20);  
 declare nowdate varchar(20);  
 declare tsqz  varchar(50); 
 declare t_error integer default 0; 
 declare continue handler for sqlexception set t_error=1; 
 start transaction; 
  /* update sys_sno set svalue=svalue where scode=tscode; */
  select svalue into tsvalue from sys_sno where scode=tscode; 
  select sqz into tsqz from sys_sno where scode=tscode ; 
 -- 因子表中没有记录，插入初始值  
  if tsvalue is null then 
   select concat(date_format(now(),'%y%m'),'0001') into tsvalue; 
   update sys_sno set svalue=tsvalue where scode=tscode ; 
   select concat(tsqz,tsvalue) into result; 
  else     
   select substring(tsvalue,1,4) into tdtoday; 
   select convert(date_format(now(),'%y%m'),signed) into nowdate;
   -- 判断年月是否需要更新
   if tdtoday = nowdate then 
    set tsvalue=convert(tsvalue,signed) + 1; 
   else 
    select concat(date_format(now(),'%y%m') ,'0001') into tsvalue ; 
   end if; 
   update sys_sno set svalue =tsvalue where scode=tscode; 
   select concat(tsqz,tsvalue) into result; 
  end if; 
  if t_error =1 then 
  rollback; 
  set result = 'error'; 
  else 
  commit; 
  end if; 
  select result ;  
end;
dao
integer getfaultno(map<string, string> parametermap);

xml

<update id="getfaultno" parametermap="getfaultmap" statementtype="callable">
  call getserialno(?,?)
 </update>
 <!--
  parametermap.put("tscode", 0);
  parametermap.put("result", -1);
  -->
 <parametermap type="java.util.map" id="getfaultmap">
  <parameter property="tscode" mode="in" jdbctype="varchar"/>
  <parameter property="result" mode="out" jdbctype="varchar"/>
 </parametermap>

调用

map<string, string> parametermap = new hashmap<string, string>();
  parametermap.put("tscode", "a");
  parametermap.put("result", "-1");
  faultmapper.getfaultno(parametermap);
  // insert 故障日志 (主表)
  if (!parametermap.get("result").equals("-1") && 
    !parametermap.get("result").equals("error")) {
   //成功
  } else {
   throw new runtimeexception();
  }

总结

以上所述是小编给大家介绍的mybatis+mysql 使用存储过程生成流水号的实现代码，希望对大家有所帮助