mysql中存储过程:
CREATE DEFINER = ‘root’@‘localhost’
PROCEDURE sosogt_test.get_code(IN ctype
int, OUT outCode
varchar(50))
COMMENT ‘根据输入值生成相应流水号 0-企业号 1-订单号2-积分订单’
BEGIN
DECLARE code1 varchar(50);
DECLARE num1 int;
DECLARE num2 int;
DECLARE ex int DEFAULT 1;
IF(ctype = 2) THEN
SELECT
num INTO num2
FROM ss_code
WHERE code_type = ctype;
IF (num2 IS NULL) THEN
START TRANSACTION;
INSERT INTO ss_code (num, code_type)
VALUES (2, ctype);
COMMIT;
SET num2 = 1;
END IF;
IF (num2 < 10000) THEN
START TRANSACTION;
UPDATE ss_code
SET num = num + 1
WHERE code_type = ctype;
COMMIT;
ELSE
START TRANSACTION;
UPDATE ss_code
SET num = 2
WHERE code_type = ctype;
COMMIT;
SET num2 = 1;
END IF;
SET code1 = DATE_FORMAT(NOW(), ‘%Y%m%d%H%i’);
SET outCode = conCode(code1, num2, 3);
END IF;
END
Controller层:
@Autowired
private OrderMapper orderMapper;
Map<Object, Object> map = new HashMap<>();
map.put(“userId”, user.getuId());
this.reportDetailService.insertReportHead(map);// 调用存储过程,查询生成的订单号
String num = “”;
if (map != null) {
num = String.valueOf(map.get(“outCode”) != null ? map.get(“outCode”) : “”);
}
Mapper层:
// 调用存储过程查询生成的订单号
public Integer getOcode(Map<Object, Object> code);
xml层代码:
{call get_code( #{ctype,mode=IN,jdbcType=INTEGER}, #{outCode,mode=OUT,jdbcType=VARCHAR}) }