基于Oracle Sequence的流水号生成规则

流水号在各种系统中随处可见，一般都是使用自增、年月日时分秒+自增、UUID等，要么纯数字，要么纯字母，这种流水号缺乏一定的辨识度。

下面为大家介绍一种具有辨识度的流水号的生成方式：领域或者应用的标识 + 表名的英文缩写(单词的第一个字母) + 年月日(或者年月日时分秒) + Oracle Sequence自增。

代码示例：

package com.pab.bloan.order.service;

/**

 * Created by

 */

public interface SequenceGeneratorService {

    /**

     * 根据SequenceName获取流水号

     * @param prefix

     * @param seqName

     * @return

     */

    String getSerialNo(String prefix, String seqName);

    /**

     * 根据指定的流水号的前缀、长度、SequenceName获取流水号

     * @param prefix

     * @param idLength

     * @param seqName

     * @return

     */

    String getSerialNo(String prefix, Integer idLength, String seqName);

}

package com.pab.bloan.order.service.impl;

import java.util.Date;

import lombok.extern.slf4j.Slf4j;

import com.pab.bloan.order.utils.DateUtils;

import org.apache.commons.lang3.StringUtils;

import org.springframework.stereotype.Service;

import com.pab.bloan.order.dao.bloanb.SequencePojoMapper;

import com.pab.bloan.order.service.SequenceGeneratorService;

import org.springframework.beans.factory.annotation.Autowired;

/**

 * 获取流水号服务

 * Created by

 */

@Slf4j

@Service

public class SequenceGeneratorServiceImpl implements SequenceGeneratorService {

    @Autowired

    private SequencePojoMapper sequencePojoMapper;

    /**

     * {@inheritDoc}

     * @param seqName

     * @return

     */

    @Override

    public String getSerialNo(String prefix, String seqName) {

        return getSerialNo(prefix, 0, seqName);

    }

    /**

     * {@inheritDoc}

     * @param prefix

     * @param idLength

     * @param seqName

     * @return

     */

    @Override

    public String getSerialNo(String prefix, Integer idLength, String seqName) {

        try {

            if (StringUtils.isEmpty(seqName)) {

                return "";

            }

            if (idLength < 8){

                idLength = 8;

            } else if (idLength > 12){

                idLength = 12;

            }

            String dateString = DateUtils.formatDate(new Date(),"yyyy/MM/dd");

            dateString = dateString.replace("/", "");

            StringBuilder sequenceSqlBuilder = new StringBuilder();

            sequenceSqlBuilder.append(" select ");

            sequenceSqlBuilder.append(seqName + ".NEXTVAL");

            sequenceSqlBuilder.append(" FROM DUAL ");

            Long sequenceId = sequencePojoMapper.getSequenceBySequenceName(sequenceSqlBuilder.toString());

            String seqIdString = String.format("%0" + idLength + "d", sequenceId);

            String serialNo;

            if (seqIdString.length() > idLength) {

                serialNo = String.format("%s%s%s", prefix, dateString, seqIdString.substring(seqIdString.length() - idLength, seqIdString.length()));

            } else {

                serialNo = String.format("%s%s%s", prefix, dateString, seqIdString);

            }

            return serialNo;

        } catch (Exception e) {

            log.error("SequenceGeneratorService.getSerialNo", e);

        }

        return "";

    }

}

Mybatis Mapper.xml文件(不能使用缓存，因为在打开缓存的情况下，连续获取Sequence，会得到重复的值)

<?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.pab.bloan.order.dao.bloanb.SequencePojoMapper">

    <select id="getSequenceBySequenceName" resultType="java.lang.Long"

            useCache="false" flushCache="true">

        ${sql}

    </select>

</mapper>

让自己业务中流水号具有辨识度，在关联查询时，一眼便可以看出数据源自何处。