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

Source: Internet
Author: User

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!

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.