Mybatis + mysql use the stored procedure to generate the serial number implementation code, mybatis serial number

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!