mysql 指定类型和指定位数创建序列号- 数据库

在这里插入图片描述

文章目录

一、脚本部分

1. 表结构

有注释

CREATE TABLE `ldmaxno` (
  `notype` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '号码类型',
  `nolimit` varchar(60) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '号码限制条件',
  `maxno` int NOT NULL COMMENT '当前最大值',
  PRIMARY KEY (`notype`,`nolimit`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 ROW_FORMAT=COMPACT COMMENT='流水号编码表';

无注释

CREATE TABLE `ldmaxno` (
  `notype` varchar(60) NOT NULL,
  `nolimit` varchar(60) NOT NULL,
  `maxno` int(11) NOT NULL,
  PRIMARY KEY (`notype`,`nolimit`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

2. 函数

CREATE DEFINER=`root`@`localhost` FUNCTION `CreateMaxNo`(
  cNoType VARCHAR ( 60 ),
  cNoLimit VARCHAR ( 60 )) RETURNS int(11)
BEGIN
  DECLARE
    tMaxNo INTEGER DEFAULT 0; -- 初始化赋值等于0，
  
  SET tMaxNo = ( SELECT MaxNo FROM LDMaxNo WHERE NoType = cNoType AND NoLimit = cNoLimit FOR UPDATE );
  IF
    isnull( tMaxNo ) = 1 THEN
      INSERT INTO LDMaxNo ( NOTYPE, NOLIMIT, MAXNO )
    VALUES
      ( cNoType, cNoLimit, 1 );
    
    SET tMaxNo = 1;
    ELSE 
      SET tMaxNo = tMaxNo + 1;
    UPDATE LDMaxNo 
    SET MaxNo = tMaxNo 
    WHERE
      NoType = cNoType 
      AND NoLimit = cNoLimit;
    
  END IF;
  RETURN tMaxNo;

END
78
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

二、代码部分

2.1. xml

DullMapper.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.gblfy.business.mapper.DullMapper">

    <select id="getMaxNo" resultType="java.lang.String">
         select createmaxno(#{cNoType},#{cNoLength}) from dual
    </select>
</mapper>

78
9

2.2. 接口

DullMapper.java

package com.gblfy.business.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Param;

public interface DullMapper extends BaseMapper {

    /**
     * 功能：产生指定长度的流水号，一个号码类型一个流水
     * @param cNoType 流水号的类型
     * @param cNoLength 流水号的长度
     * @return 返回产生的流水号码
     */
    String getMaxNo(@Param("cNoType") String cNoType, @Param("cNoLength") int cNoLength);
}

78
9
10
11
12
13
14
15
16

2.3. api接口

package com.gblfy.business.service;

public interface SysMaxNoService {

    /**
     * 功能：产生指定长度的流水号，一个号码类型一个流水
     *
     * @param cNoType   流水号的类型
     * @param cNoLength 流水号的长度
     * @return 返回产生的流水号码
     */
    String createMaxNo(String cNoType, int cNoLength);
}


78
9
10
11
12
13
14
15

2.4. api实例


package com.gblfy.business.service.impl;

import com.gblfy.business.mapper.DullMapper;
import com.gblfy.business.service.SysMaxNoService;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.math.BigInteger;

@Service
public class SysMaxNoServiceImpl implements SysMaxNoService {

    private final static Logger logger = LoggerFactory.getLogger(SysMaxNoServiceImpl.class);

    @Resource
    private DullMapper dullMapper;


    /**
     * 功能：产生指定长度的流水号，一个号码类型一个流水
     *
     * @param cNoType   流水号的类型
     * @param cNoLength 流水号的长度
     * @return 返回产生的流水号码
     */
    @Override
    public String createMaxNo(String cNoType, int cNoLength) {
        if ((cNoType == null) || (cNoType.trim().length() <= 0) ||
                (cNoLength <= 0)) {
            logger.info("NoType长度错误 {} NoLength错误", cNoType, cNoLength);
            return null;
        }
        cNoType = cNoType.toUpperCase();
        String tReturn = "";
        String cNoLimit = "SN";
        BigInteger tMaxNo = new BigInteger("0");
        tReturn = cNoLimit;

        try {
            String result = dullMapper.getMaxNo(cNoType, cNoLength);
            tMaxNo = new BigInteger(result);
        } catch (Exception e) {
            e.printStackTrace();
            logger.info("生成流水号出现异常，请核实！");
        }

        String tStr = tMaxNo.toString();

        //将生成的流水号进行加工处理
        tStr = LCh(tStr, "0", cNoLength);
        tReturn = tStr.trim();

        return tReturn;
    }

    /**
     * 将生成的流水号进行加工处理
     * <p>
     * 1.判断是否满足指定长度，如果不满足前面用0来补位
     * 2.将生成的流水号进行去空格处理
     * 3.将最终的流水号进行字符串拼接
     * </P>
     *
     * @param sourString
     * @param cChar
     * @param cLen
     * @return
     */
    private String LCh(String sourString, String cChar, int cLen) {
        int tLen = sourString.length();
        int i, iMax;
        String tReturn = "";
        if (tLen >= cLen) {
            return sourString;
        }
        //1.判断是否满足指定长度，如果不满足前面用0来补位
        iMax = cLen - tLen;
        for (i = 0; i < iMax; i++) {
            tReturn += cChar;
        }
        /https://files.jxasp.com/image/2.将生成的流水号进行去空格处理
        //3.将最终的流水号进行字符串拼接
        tReturn = tReturn.trim() + sourString.trim();
        return tReturn;
    }
}


78
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91

2.5. 控制层

package com.gblfy.business.controller;

import com.gblfy.business.service.SysMaxNoService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.RestController;

/**
 * 生成指定类型+位数的流水号
 *
 * @Author gblfy
 * @Date 2022-05-16 20:13
 **/
@RestController
public class SysMaxNoController {

    @Autowired
    private SysMaxNoService maxNoService;

    /**
     * 生成指定类型+位数的流水号
     *
     * @param cNoType
     * @param cNoLength
     * @return
     */
    @GetMapping("/generate/serial/number")
    public String generateSerialNumber(@RequestParam(name = "cNoType", required = false, defaultValue = "cNoType") String cNoType,
                                       @RequestParam int cNoLength) {
        return maxNoService.createMaxNo(cNoType, cNoLength);
    }
}

78
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34