[大数据][项目应用]生成某种特定规则的编号

一.前言

在项目中经常会有需求,要给订单或合同生成有特定规则的编号.

规则:固定大写字母DT +当前时间戳+五位编号[00001-99999]
生成编号:DT-20220421010101-00001

学习到用MySQL的函数,可以实现下一次生成编号时,自动取到00002.

二.MySQL新建一张表sys_sequence

seq_name 序列名称
current_val 当前编号
increment_val 步长

结构sql如下:

DROP TABLE IF EXISTS sys_sequence; CREATE TABLE sys_sequence (
seq_name VARCHAR ( 50 ) CHARACTER SET utf8 COLLATE
utf8_general_ci NOT NULL COMMENT ‘序列名称’, current_val INT ( 0 ) NOT
NULL COMMENT ‘当前值’, increment_val INT ( 0 ) NOT NULL DEFAULT 1
COMMENT ‘步长’, PRIMARY KEY ( seq_name ) USING BTREE ) ENGINE =
INNODB CHARACTER SET = utf8 COLLATE = utf8_general_ci COMMENT =
‘系统自增序列’ ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

表中数据如图
步长为1的意思是下次编号会比当前编号递增1

三.在MySQL新建函数

1. 在navicat中,函数部分,右键选新建函数
2. 填函数名,勾选函数,点击完成.
3. 这里需要新建两个函数,一个为currval,一个为nextval
   
   4.新建函数之后需要补全函数定义
   currval函数完整定义为:

CREATE DEFINER=root@% FUNCTION currval(v_seq_name VARCHAR(50)) RETURNS int
BEGIN
declare value integer;
set value = 0;
select current_val into value from sys_sequence where seq_name = v_seq_name;
return value;
END

nextval函数完整定义为:

CREATE DEFINER=root@% FUNCTION nextval(v_seq_name VARCHAR(50)) RETURNS int
BEGIN
update sys_sequence set current_val = current_val + increment_val where seq_name = v_seq_name;
return currval(v_seq_name);
END

四.java代码

1.SequenceMapper

package com.xx.common.seq.mapper;

import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.springframework.stereotype.Repository;

@Repository
public interface SequenceMapper {

    /**
     * 新建序列
     *
     * @param name  序列名
     * @param start 起始值
     * @param incr  递增值
     */
    @Insert("INSERT INTO sys_sequence VALUES(#{name},${start},${incr})")
    public void insertSequence(@Param("name") String name, @Param("start") int start, @Param("incr") int incr);


    /**
     * 重置序列到某个值
     *
     * @param name 序列名
     * @param val  起始值
     */
    @Update("UPDATE sys_sequence SET current_val = ${val} WHERE seq_name = #{name}")
    public void resetSequence(@Param("name") String name, @Param("val") int val);

    /**
     * 获取指定序列是否存在
     *
     * @param name 序列名
     * @return 0 不存在 1 存在
     */
    @Select("SELECT COUNT(1) FROM sys_sequence WHERE seq_name = #{name}")
    public int selectSequenceExists(String name);

    /**
     * 获取指定序列下一个值
     *
     * @param name 序列名
     * @return 值
     */
    @Select("SELECT nextval(#{name})")
    public Integer nextVal(String name);


    /**
     * 获取指定序列当前值
     *
     * @param name 序列名
     * @return 值
     */
    @Select("SELECT currval(#{name})")
    public Integer currVal(String name);
}

2.ISequenceService

package com.xx.common.seq.service;

public interface ISequenceService {

    public String next(String seq);

}

3.SequenceServiceImpl

package com.xx.common.seq.service.impl;


import com.agriculture.common.seq.mapper.SequenceMapper;
import com.agriculture.common.seq.service.ISequenceService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

@Service("sequenceService")
public class SequenceServiceImpl implements ISequenceService {

    @Autowired
    private SequenceMapper sequenceMapper;

    @Override
    public String next(String seq) {
        // 如果该序列不存在，那么新增
        int exists = sequenceMapper.selectSequenceExists(seq);
        if (exists == 0) {
            sequenceMapper.insertSequence(seq, 0, 1);
        }
        // 如果超出9999, 那么重置
        Integer val = sequenceMapper.currVal(seq);
        if (val >= 9999) {
            sequenceMapper.resetSequence(seq, 0);
        }

        String result = "0000" + sequenceMapper.nextVal(seq);

        return result.substring(result.length() - 5);
    }

}

五.添加工具类

package com.agriculture.common.utils;


import com.agriculture.common.seq.service.ISequenceService;
import com.agriculture.common.utils.spring.SpringUtils;

import java.text.SimpleDateFormat;
import java.util.Date;

public abstract class GenerateSerialUtilscOPR {


    private GenerateSerialUtilscOPR() {
    }


    private static volatile ISequenceService sequenceService;


    //订单编号
    public synchronized static String index(String seq) {
        return seq.concat("-").concat(timestamp()) .concat("-"). concat(getService().next(seq.toUpperCase()));
    }

    /**
     * 获取Service
     *
     * @return service
     */
    private static ISequenceService getService() {
        if (sequenceService == null){ sequenceService = SpringUtils.getBean(ISequenceService.class);}
        return sequenceService;
    }

    /**
     * 获取时间
     *
     * @return timestamp
     */
    private static String timestamp() {
        return new SimpleDateFormat("yyyyMMdd").format(new Date());
    }
}

六.测试方法

public static void main(String[] args) {
    System.out.println(index("DT"));
}

测试结果为:DT-20220421020101-00002

阅读世界，共赴山海

423全民读书节，邀你共读