Mybatis+mysql uses stored procedures to generate serial number implementation code
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!
