Shulou(Shulou.com)06/01 Report--

Use stored procedures to open transactions when operating the database, so as to avoid data duplication caused by 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 tasking errorists 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. Insert the initial value 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 -- determine whether you need to update 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 DaoInteger getFaultNo (Map parameterMap)

Xml

CALL GetSerialNo (?)

Call

Map parameterMap = new HashMap (); parameterMap.put ("tsCode", "a"); parameterMap.put ("result", "- 1"); faultMapper.getFaultNo (parameterMap); / / insert failure log (master table) if (! parameterMap.get ("result"). Equals ("- 1") & &! parameterMap.get ("result"). Equals ("Error") {/ / success} else {throw new RuntimeException ();}

Summary

The above is the realization code that the editor introduces to you that mybatis+mysql uses the stored procedure to generate the serial number. I hope it will be helpful to you. If you have any questions, please leave me a message and the editor will reply to you in time. Thank you very much for your support to the website!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.