Mybatis + mysql use the stored procedure to generate the serial number implementation code, mybatis serial number
Use stored procedures to enable transactions during database operations to avoid data duplication during concurrent operations
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; -- there IS no record in the factor table, and the initial value IS inserted IF tsValue is null then select concat (DATE_FORMAT (NOW (), '% y % m'), '123') 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; -- determine whether to update IF tdToday = nowdate then set tsValue = CONVERT (tsValue, SIGNED) + 1; else select concat (DATE_FORMAT (NOW (), '% y % m'), '000000') 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; daoInteger 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>
Call
Map <String, String> parameterMap = new HashMap <String, String> (); parameterMap. put ("tsCode", "a"); parameterMap. put ("result", "-1"); faultMapper. getFaultNo (parameterMap); // insert Fault Log (master table) if (! ParameterMap. get ("result"). equals ("-1 ")&&! ParameterMap. get ("result"). equals ("Error") {// success} else {throw new RuntimeException ();}
Summary
The above is the implementation code for mybatis + mysql to generate a serial number using the stored procedure. I hope it will be helpful to you. If you have any questions, please leave a message, the editor will reply to you in a timely manner. Thank you very much for your support for the help House website!