Java通过Mysql数据库实现指定格式序列，实现业务编号每天重新从00001开始

做新系统开始，业务要求实现编号每天重置为1，例如：今天是2020年6月15日，贷款合同的编号需要为2020DKHT00001，2020DKHT00002。。依次递增，第二天再重新从1开始，即2020061600001，2020061600002。。

同事写好了，我直接给搬运过来，供大家做个参考。

数据库表结构如下图所示

//序列实体
public class SequenceEntity {
    //序列名称
    private String seqName;
    //当前值
    private long seqValue;
    //最小值
    private long minValue;
    //最大值
    private long maxValue;
    //一次缓存序列数量
    private long step;
    //创建时间
    private Date createDate;
    //更新时间
    private Date updateDate;

    public String getSeqName() {
        return seqName;
    }

    public void setSeqName(String seqName) {
        this.seqName = seqName;
    }

    public long getSeqValue() {
        return seqValue;
    }

    public void setSeqValue(long seqValue) {
        this.seqValue = seqValue;
    }

    public long getMinValue() {
        return minValue;
    }

    public void setMinValue(long minValue) {
        this.minValue = minValue;
    }

    public long getMaxValue() {
        return maxValue;
    }

    public void setMaxValue(long maxValue) {
        this.maxValue = maxValue;
    }

    public long getStep() {
        return step;
    }

    public void setStep(long step) {
        this.step = step;
    }

    public Date getCreateDate() {
        return createDate;
    }

    public void setCreateDate(Date createDate) {
        this.createDate = createDate;
    }

    public Date getUpdateDate() {
        return updateDate;
    }

    public void setUpdateDate(Date updateDate) {
        this.updateDate = updateDate;
    }

    public boolean validate(){
        //一些简单的校验。如当前值必须在最大最小值之间。step值不能大于max与min的差
        if (StringUtil.isBlankNew(seqName) || minValue < 0 || maxValue <= 0 || step <= 0 || minValue >= maxValue || maxValue - minValue <= step ||seqValue < minValue || seqValue > maxValue ) {
            return false;
        }
        return true;
    }


}

//DAO层
public interface MysqlSequenceDao {
    SequenceEntity getSequenceEntity(String seqName);

    int updateNewSequenceValue(String seqName, long parseLong, long newSequence);

    int insertSequenceEntity(SequenceEntity se);
}

//DAO实现，这边@DalDao是自己写的注解，换成你们常用开发注解即可
@DalDao
public class MysqlSequenceDaoImpl extends DalBaseDaoMyBatis<SequenceEntity> implements MysqlSequenceDao {
    @Override
    public int insertSequenceEntity(SequenceEntity se){
        int result = this.getSqlSession().insert(this.getEntityClass().getName()+".insertSequenceEntity",se);
        return result;
    }

    @Override
    public SequenceEntity getSequenceEntity(String seqName) {
        //查询单个序列对象
        SequenceEntity se = (SequenceEntity)this.getSqlSession().selectOne(this.getEntityClass().getName()+".selectSeqEntity",seqName);
        return se;
    }

    @Override
    public int updateNewSequenceValue(String seqName, long initSequence, long newSequence) {
        Map map = new HashMap<String,Object>();
        //where条件加上序列名称
        map.put("seqName", seqName);
        map.put("oldValue",initSequence);
        map.put("newValue",newSequence);
        map.put("updateDate",new Date());
        int count = this.getSqlSession().update(this.getEntityClass().getName()+".updateSeqValue",map);
        return count;
    }
}

//对应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.suning.cms.entity.SequenceEntity">
    <resultMap id="sequenceEntity" type="com.suning.cms.entity.SequenceEntity">
        <result column="seq_name" property="seqName" />
        <result column="seq_value" property="seqValue" />
        <result column="seq_min_value" property="minValue" />
        <result column="seq_max_value" property="maxValue" />
        <result column="seq_step" property="step" />
        <result column="createdate" property="createDate" />
        <result column="updatedate" property="updateDate" />
    </resultMap>
    <sql id="sequenceEntityColumn">
        <trim suffixOverrides=",">
            <if test="seqName!=null and seqName!=''">
                seq_name,
            </if>
            <if test="seqValue!=null and seqValue!=''">
                seq_value,
            </if>
            <if test="minValue!=null and minValue!=''">
                seq_min_value,
            </if>
            <if test="maxValue!=null and maxValue!=''">
                seq_max_value,
            </if>
            <if test="step!=null and step!=''">
                seq_step,
            </if>
            <if test="createDate!=null and createDate!=''">
                createdate,
            </if>
            <if test="updateDate!=null and updateDate!=''">
                updatedate,
            </if>
        </trim>
    </sql>
    <sql id="sequenceEntityValue">
        <trim suffixOverrides=",">
            <if test="seqName!=null and seqName!=''">
                #{seqName},
            </if>
            <if test="seqValue!=null and seqValue!=''">
                #{seqValue},
            </if>
            <if test="minValue!=null and minValue!=''">
                #{minValue},
            </if>
            <if test="maxValue!=null and maxValue!=''">
                #{maxValue},
            </if>
            <if test="step!=null and step!=''">
                #{step},
            </if>
            <if test="createDate!=null and createDate!=''">
                #{createDate},
            </if>
            <if test="updateDate!=null and updateDate!=''">
                #{updateDate},
            </if>
        </trim>
    </sql>
    <insert id="insertSequenceEntity" parameterType="com.suning.cms.entity.SequenceEntity">
        insert into cm_biz_sequence
        (
        <include refid="sequenceEntityColumn"/>
        )
        value
        (
        <include refid="sequenceEntityValue"/>
        )
    </insert>

    <select id="selectSeqEntity" parameterType="java.lang.String" resultMap="sequenceEntity">
        select s.* from cm_biz_sequence s
        where s.seq_name = #{seqName}
    </select>

    <update id="updateSeqValue" parameterType="Map">
        update cm_biz_sequence b set seq_value = #{newValue}, updatedate = #{updateDate}
        where seq_value = #{oldValue} and seq_name = #{seqName}
    </update>
</mapper>

//用来获取缓存序列，每次缓存5个数
public class SequenceRange {
    private final AtomicLong value;
    private final long minCacValue;
    private final long maxCacValue;
    //是否超限
    private volatile boolean over =false;

    public SequenceRange(long oldSeqValue,long newSeqValue){
        this.minCacValue=oldSeqValue;
        this.maxCacValue=newSeqValue;
        this.value=new AtomicLong(oldSeqValue);
    }

    public long getNextSequence(){
        long currValue = value.getAndIncrement();
        if(currValue>maxCacValue){
            over=true;
            return -1;
        }
        return currValue;
    }
}

//缓存序列控制类
public class MysqlSequenceHolder {
    private static final Logger LOG = LoggerFactory.getLogger(MysqlSequenceHolder.class);

    private final Lock lock = new ReentrantLock();
    @Resource
    private MysqlSequenceDao mysqlSequenceDao;

    private SequenceEntity sequenceEntity;

    private SequenceRange sequenceRange;

    /** 是否初始化 */
    private volatile boolean isInitialize = false;

    MysqlSequenceHolder(MysqlSequenceDao msDao, SequenceEntity se){
        this.mysqlSequenceDao=msDao;
        this.sequenceEntity=se;
    }

    long getNextVal(){
        if(!isInitialize){
            LOG.info("序列名" + sequenceEntity.getSeqName() + "未初始化");
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "未初始化");
        }
        long nextSeq = sequenceRange.getNextSequence();
        if(nextSeq==-1){
            try{
                lock.lock();
                nextSeq = sequenceRange.getNextSequence();
                if(nextSeq!=-1){
                    return nextSeq;
                }
                sequenceRange = retryRange();
                nextSeq = sequenceRange.getNextSequence();
            }finally {
                lock.unlock();
            }
        }
        return nextSeq;
    }

    //缓存序列已用完,重新取数据库更新
    private SequenceRange retryRange() {
        SequenceEntity sequeEntity = mysqlSequenceDao.getSequenceEntity(sequenceEntity.getSeqName());
        if(sequeEntity==null){
            LOG.info("序列名" + sequenceEntity.getSeqName() + "为空");
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "为空");
        }
        if(!sequeEntity.validate()){
            LOG.info("序列名" + sequenceEntity.getSeqName() + "参数异常");
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "参数异常");
        }
        //原来值
        long currentSequence = sequeEntity.getSeqValue();
        //更改后值
        long newSequence = currentSequence+sequeEntity.getStep();
        //校验当前值
        if(!checkCurrentValue(newSequence,sequeEntity)){
            LOG.info("序列名" + sequenceEntity.getSeqName() + "超出最大值!");
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "超出最大值!");
        }
        int result = mysqlSequenceDao.updateNewSequenceValue(sequeEntity.getSeqName(),sequeEntity.getSeqValue(),newSequence);
        if(result>0){
            sequenceRange = new SequenceRange(currentSequence,newSequence-1);
            sequeEntity.setSeqValue(newSequence);
            this.sequenceEntity =sequeEntity;
            return sequenceRange;
        }else{
            LOG.info("序列名" + sequenceEntity.getSeqName() + "更新异常");
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "更新异常");
        }
    }

    void initSequence(){
        if(isInitialize){
            LOG.info("序列已初始化");
            return;
        }
        // 初始化该sequence
        try {
            initSequenceRecord(sequenceEntity);
        } catch (Exception e) {
            LOG.info("序列初始化异常",e);
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "初始化异常");
        }
        isInitialize = true;
    }

    private void initSequenceRecord(SequenceEntity sequenceEntity) {
        SequenceEntity seqE = mysqlSequenceDao.getSequenceEntity(sequenceEntity.getSeqName());
        //当前序列值
        long currVal = seqE.getSeqValue();
        //改变当前值
        long newSeqVal= currVal+seqE.getStep();

        if(!checkCurrentValue(newSeqVal,seqE)){
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "超出最大值!");
        }
        //更新数据库记录当前值为最新值
        int result = mysqlSequenceDao.updateNewSequenceValue(sequenceEntity.getSeqName(),seqE.getSeqValue(),newSeqVal);
        if(result>0){
            sequenceRange = new SequenceRange(sequenceEntity.getSeqValue(),newSeqVal-1);
            //更新记录后，把当前entity序列值更新为最新
            sequenceEntity.setSeqValue(newSeqVal);
            this.sequenceEntity = seqE;
        }else{
            LOG.info("序列名" + sequenceEntity.getSeqName() + "更新异常");
            throw new BusinessException("序列名" + sequenceEntity.getSeqName() + "更新异常");
        }
    }

    private boolean checkCurrentValue(long newSeqVal, SequenceEntity sequenceEntity) {
        //判断新序列在最大值和最小值之间
        return newSeqVal >= sequenceEntity.getMinValue() && sequenceEntity.getMaxValue() > newSeqVal;
    }
}

//序列工厂类
@Service("mysqlSequenceFactory")
public class MysqlSequenceFactory {
    private static final Logger LOG = LoggerFactory.getLogger(MysqlSequenceFactory.class);
    //同步锁
    private final Lock lock = new ReentrantLock();

    private Map<String, MysqlSequenceHolder> holderMap = new ConcurrentHashMap<>();
    @Resource
    private MysqlSequenceDao mysqlSequenceDao;

    //初始化序列值
    private final long initSequence = 1;

    //初始化序列最小值
    private final long minSequence = 1;

    @Transactional(rollbackFor = Exception.class, propagation = Propagation.REQUIRES_NEW)
    public long getNextVal(String seqName) {
        MysqlSequenceHolder msHolder = holderMap.get(seqName);
        if (msHolder == null) {
            try {
                lock.lock();
                msHolder = holderMap.get(seqName);
                if (msHolder != null) {
                    return msHolder.getNextVal();
                }
                //先要根据序列名查询数据库有么有插入或者提前配置(防止正常情况下服务停掉重启)
                SequenceEntity se = mysqlSequenceDao.getSequenceEntity(seqName);
                //数据库可以根据业务需求进行配置，没有配置的话，按照上边的默认值
                if (se == null) {
                    //再次确定没有缓存,那么就插入一条记录
                    //初始化序列最大值
                    String maxSequence = SCMUtil.getScmPropValidResult(ScmConstants.MAXSEQUENCE);
                    //初始化缓存数STEP
                    String sequenceStep = SCMUtil.getScmPropValidResult(ScmConstants.SEQUENCESTEP);
                    if(StringUtil.isBlankNew(maxSequence)){
                        maxSequence = "10000";
                    }
                    if(StringUtil.isBlankNew(sequenceStep)){
                        sequenceStep = "5";
                    }
                    se = new SequenceEntity();
                    se.setSeqName(seqName);
                    se.setSeqValue(initSequence);
                    se.setStep(Long.valueOf(sequenceStep));
                    se.setMinValue(minSequence);
                    se.setMaxValue(Long.valueOf(maxSequence));
                    se.setCreateDate(new Date());
                    int result = mysqlSequenceDao.insertSequenceEntity(se);
                    LOG.info("序列bean插入成功:" + result);
                }
                msHolder = new MysqlSequenceHolder(mysqlSequenceDao, se);
                msHolder.initSequence();
                holderMap.put(se.getSeqName(), msHolder);
            }catch (Exception e){
                LOG.info("获取序列异常",e);
                throw new BusinessException("获取序列异常");
            } finally {
                //不管插入成功与否都要释放锁
                lock.unlock();
            }
        }
        return msHolder.getNextVal();
    }
}

//序列service接口
public interface MysqlSequenceService {
    String getNextSequnce(String seqName);

    String getNextLogId();

    String getNextCode(String type);

    String getNextPayReqNo(String bizSubCateg,String distributeSys);
}

//序列Service实现类，重点关注第一个方法即可
@Service("mysqlSequenceServiceImpl")
public class MysqlSequenceServiceImpl implements MysqlSequenceService {
    @Resource
    private MysqlSequenceFactory mysqlSequenceFactory;
    @Override
    public String getNextSequnce(String seqName) {
        String nextSeq = String.valueOf(mysqlSequenceFactory.getNextVal(seqName));
        String maxSequence = SCMUtil.getScmPropValidResult(ScmConstants.MAXSEQUENCE);
        if(StringUtil.isBlankNew(maxSequence)){
            maxSequence="10000";
        }
        return StringUtil.leftFillZero(nextSeq,maxSequence.length());
    }

    /**
     * 功能描述: 获取下一个日志ID<br>
     *
     * @param: []
     * @return: java.lang.String
     * @author: 88449366
     * @Date: 2019/10/15
     */
    @Override
    public String getNextLogId(){
        String seqName = new SimpleDateFormat("yyyyMMdd").format(DateUtil.getNow())+"LOG";
        String seqNum = getNextSequnce(seqName);
        return seqName+seqNum;
    }

    /**
     * 功能描述: 获得业务表主键<br>
     *
     * @param: [type]
     * @return: java.lang.String
     * @author: 88449366
     * @Date: 2019/10/15
     */
    @Override
    public String getNextCode(String type){
        String seqName = new SimpleDateFormat("yyyyMMdd").format(DateUtil.getNow())+type;
        String seqNum = getNextSequnce(seqName);
        return seqName+seqNum;
    }

    /**
     * 获取支付请求单号
     * @param bizSubCateg
     * @param distributeSys
     * @return
     */
    @Override
    public String getNextPayReqNo(String bizSubCateg,String distributeSys) {
        String seqTime = new SimpleDateFormat("yyyyMMdd").format(DateUtil.getNow());
        String seqName = new SimpleDateFormat("yyyyMMdd").format(DateUtil.getNow())+"payReqNo";
        String nextSeq = String.format("%04d",mysqlSequenceFactory.getNextVal(seqName));
        return bizSubCateg+distributeSys+seqTime+nextSeq;
    }


}

以上就是写好的Service，仅仅做个记录，防止自己以后可能要用到。

原文链接：https://blog.csdn.net/DAI_Zeekin/article/details/106762648

你可能也喜欢