如何确保一个序列号是按自己定义的规则依次递增？
实际项目需求如下：在商户表有商户费率模板号这个字段，我们要求该字段的规则是：ST+当前日期YYYYMMDD+6位序列号(左边不够是用0填补) 如：TS20200610000001和TS20200610000002
备注：当前字段非自增主键，单我们又要求该字段的值是按照我们约束的规则依次增加！！！！
在一个项目中，这种字段可能会很多，那怎么去很好的解决这种问题呢？
通常，对于这种需求，我们都会用一个专门的序列表(sequence)，表结构如下：
CREATE TABLE `sequence` (
`id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '记录流水号',
`seq_name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '序列名称',
`current_value` bigint(20) NOT NULL DEFAULT 0 COMMENT '当前值',
`increment_value` int(10) UNSIGNED NOT NULL DEFAULT 1 COMMENT '递增值',
`minimum` bigint(20) NOT NULL DEFAULT 1 COMMENT '最小值ֵ',
`maximum` bigint(20) NOT NULL DEFAULT 9223372036854775807 COMMENT '最大值ֵ',
`cycle_flg` char(1) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '循环使用标记Y:是N:否',
`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) COMMENT '创建时间',
`update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP(0) ON UPDATE CURRENT_TIMESTAMP(0) COMMENT '修改时间',
PRIMARY KEY (`id`) USING BTREE,
UNIQUE INDEX `uq_seqname`(`seq_name`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 71 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = 'star序列生成表' ROW_FORMAT = Dynamic;
备注：
seq_name序列名称通常是字段名称，如商户费率模板号、服务商号等对应的seq_name分别是TEMP_ID_SEQ和BAGENT_ID
increment_value 字段代表每次的递增值，通常为1
current_value 字段代表当前值 如当前只是1，当经过一次递增之后，当前值变成2，下次生成的值就是3
通过sequence这张表，我们会统一的管理需要按我们自定义要求递增的一个唯一序列
1、公共常量类CommonConstants中 定义不够长度依次用0补全、定义目标序列号前缀
public static final char SEQUENCE_PREFIX = '0';
public static final String TEMP_ID_SEQ_PREFIX = "ST";
2、实体类、mybatis接口文件和数据库映射文件
@Data
public class SequenceDTO {
private Long seqId;
private String seqName;
private Long currentValue;
private Long incrementValue;
private Long minimum;
private Long maximum;
private String cycleFlg;
private Date createTime;
private Date updTime;
}
@Mapper
public interface SequenceRepository {
/**
* 功能描述: 获取指定的序列行，并进行for update的行锁操作
*/
SequenceDTO getSequenceByIdForUpdate(@Param("seqName") String seqName);
/**
* 功能描述: 更新指定的Sequence值
*/
int updateSequenceById(SequenceDTO sequenceDTO);
/**
* 功能描述: 获取全部序列行
*/
List<SequenceDTO> getAllSequence();
/**
* 功能描述: 从mysql获取uuid
*/
String getUuidFromMysql();
}
<?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.mtx.kyrieboot.sequence.SequenceRepository">
<resultMap id="BaseResultMap" type="com.mtx.kyrieboot.sequence.SequenceDTO">
<id column="id" jdbcType="BIGINT" property="seqId" />
<result column="seq_name" jdbcType="VARCHAR" property="seqName" />
<result column="current_value" jdbcType="BIGINT" property="currentValue" />
<result column="increment_value" jdbcType="BIGINT" property="incrementValue" />
<result column="minimum" jdbcType="BIGINT" property="minimum" />
<result column="maximum" jdbcType="BIGINT" property="maximum" />
<result column="cycle_flg" jdbcType="CHAR" property="cycleFlg" />
<result column="create_time" jdbcType="TIMESTAMP" property="createTime" />
<result column="update_time" jdbcType="TIMESTAMP" property="updTime" />
</resultMap>
<select id="getSequenceByIdForUpdate" parameterType="java.lang.String" resultMap="BaseResultMap">
select * from sequence where seq_name = #{seqName,jdbcType=VARCHAR} for update
</select>
<select id="getAllSequence" resultMap="BaseResultMap">
select * from sequence
</select>
<update id="updateSequenceById" parameterType="com.mtx.kyrieboot.sequence.SequenceDTO">
update sequence
<set>
<if test="currentValue != null">
current_value = #{currentValue,jdbcType=BIGINT},
</if>
<if test="updTime != null">
update_time = #{updTime,jdbcType=TIMESTAMP},
</if>
</set>
where seq_name = #{seqName,jdbcType=VARCHAR}
</update>
<select id="getUuidFromMysql" resultType="java.lang.String">
select uuid() as uuid
</select>
</mapper>
3、序列号工厂类
@Service
@Slf4j
public class MySqlSequenceFactory {
private final Lock lock = new ReentrantLock();
private int getRetryNum = 5;
private Map<String, MySqlSequenceHolder> holderMap = new ConcurrentHashMap<>();
private int initRetryNum = 5;
@Autowired
private SequenceRepository sequenceRepository;
@Autowired
private SequenceService sequenceService;
public long getNextVal(String seqName) {
log.info("开始获取{}序列值", seqName);
MySqlSequenceHolder holder = holderMap.get(seqName);
if (holder == null) {
log.warn("获取{}序列值出现异常，指定sequenceHolder在map中不存在，开始重新获取操作", seqName);
try {
lock.lock();
holder = holderMap.get(seqName);
if (holder != null) {
return holder.getNextVal();
}
SequenceRange sequenceRange = new SequenceRange(-1, -1);
holder = new MySqlSequenceHolder(seqName, sequenceRepository, sequenceService, sequenceRange, initRetryNum, getRetryNum);
holder.init();
holderMap.put(seqName, holder);
} finally {
lock.unlock();
}
}
long finalLongNum = holder.getNextVal();
log.info("获取{}序列值完成，获取的值为{}", seqName, finalLongNum);
return finalLongNum;
}
private void init() {
log.info("开始初始化所有sequence序列");
initAll();
log.info("初始化sequence序列完成");
}
private void initAll() {
try {
lock.lock();
List<SequenceDTO> sequenceDTOList = sequenceRepository.getAllSequence();
if (sequenceDTOList == null || sequenceDTOList.size() == 0) {
throw new SequenceException("The sequenceDTOList is null!");
}
for (SequenceDTO SequenceDTO : sequenceDTOList) {
SequenceRange sequenceRange = new SequenceRange(-1, -1);
MySqlSequenceHolder holder = new MySqlSequenceHolder(SequenceDTO.getSeqName(), sequenceRepository, sequenceService, sequenceRange, initRetryNum, getRetryNum);
holder.init();
holderMap.put(SequenceDTO.getSeqName(), holder);
}
} finally {
lock.unlock();
}
}
}
4、MySQL序列生成器
@Slf4j
public class MySqlSequenceHolder {
private final Lock lock = new ReentrantLock();
private String seqName;
private SequenceRepository sequenceRepository;
private SequenceService sequenceService;
private SequenceRange sequenceRange;
private volatile boolean isInitialize = false;
private int initRetryNum;
private int getRetryNum;
public MySqlSequenceHolder(String seqName, SequenceRepository SequenceRepository, SequenceService sequenceService, SequenceRange sequenceRange, int initRetryNum, int getRetryNum) {
this.seqName = seqName;
this.sequenceRepository = SequenceRepository;
this.sequenceService = sequenceService;
this.sequenceRange = sequenceRange;
this.initRetryNum = initRetryNum;
this.getRetryNum = getRetryNum;
}
public void init() {
log.info("开始初始化{}序列", seqName);
if (isInitialize == true) {
log.error("初始化{}序列失败，序列已经初始化", seqName);
throw new SequenceException("[" + seqName + "] the MySqlSequenceHolder has inited");
}
if (sequenceRepository == null) {
log.error("初始化{}序列失败，SequenceRepository对象为空", seqName);
throw new SequenceException("[" + seqName + "] the SequenceRepository has inited");
}
if (sequenceService == null) {
log.error("初始化{}序列失败，sequenceService", seqName);
throw new SequenceException("[" + seqName + "] the sequenceService has inited");
}
if (StringUtils.isBlank(seqName)) {
log.error("初始化序列失败，seqName没有置入");
throw new SequenceException("[" + seqName + "] the seqName has inited");
}
initSequenceRecord();
isInitialize = true;
log.info("初始化{}序列完成", seqName);
}
public long getNextVal() {
if (isInitialize == false) {
log.error("获取下一个序列值失败，此序列还未初始化");
throw new SequenceException("[" + seqName + "] the MySqlSequenceHolder not inited");
}
if (sequenceRange == null) {
log.error("获取下一个序列值失败，此序列区间对象还未初始化");
throw new SequenceException("[" + seqName + "] the sequenceRange is null");
}
long curValue = sequenceRange.getAndIncrement();
if (curValue == -1) {
try {
lock.lock();
curValue = sequenceRange.getAndIncrement();
if (curValue != -1) {
return curValue;
}
sequenceRange = retryRange();
curValue = sequenceRange.getAndIncrement();
} finally {
lock.unlock();
}
}
return curValue;
}
public void initSequenceRecord() {
for (int i = 0; i < initRetryNum; i++) {
SequenceRange sequenceRangeTmp = sequenceService.getNewSequenceRange(seqName);
if (sequenceRangeTmp == null) {
continue;
} else {
sequenceRange = sequenceRangeTmp;
return;
}
}
}
private SequenceRange retryRange() {
for (int i = 0; i < getRetryNum; i++) {
SequenceRange sequenceRangeTmp = sequenceService.getNewSequenceRange(seqName);
if (sequenceRangeTmp == null) {
log.error("初始化{}序列异常，无法获取指定的数据", seqName);
continue;
} else {
return sequenceRangeTmp;
}
}
throw new SequenceException("[" + seqName + "] CardSequence update error");
}
}
5、序列区间，用于缓存序列
public class SequenceRange {
/**
* 最小值
*/
private final long min;
/**
* 最大值
*/
private final long max;
/**
* 当前值
*/
private final AtomicLong value;
/**
* 是否超限
*/
private volatile boolean over = false;
public SequenceRange(long min, long max) {
this.min = min;
this.max = max;
this.value = new AtomicLong(min);
}
public long getAndIncrement() {
long currentValue = value.getAndIncrement();
if (min == -1 || max == -1 || currentValue > max) {
over = true;
return -1;
}
return currentValue;
}
}
6、异常
public class SequenceException extends RuntimeException {
private static final long serialVersionUID = -6896586285280037814L;
public SequenceException() {
super();
}
public SequenceException(String s) {
super(s);
}
public SequenceException(String message, Throwable cause) {
super(message, cause);
}
public SequenceException(Throwable cause) {
super(cause);
}
}
7、序列号服务类
@Service
@Slf4j
public class SequenceService {
@Autowired
private SequenceRepository sequenceRepository;
private static final long overLong = 8223372036854775807L;
@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
public SequenceRange getNewSequenceRange(String seqName) {
log.info("开始获取序列{}的数据", seqName);
SequenceRange sequenceRange;
SequenceDTO sequenceDTO = sequenceRepository.getSequenceByIdForUpdate(seqName);
if (sequenceDTO == null) {
return null;
} else {
if ("N".equals(sequenceDTO.getCycleFlg()) && sequenceDTO.getCurrentValue().equals(sequenceDTO.getMaximum())) {
log.error("获取{}的数据失败，序列已经使用完，请速度重新设计解决方案", seqName);
return null;
} else {
long newMinValue = sequenceDTO.getCurrentValue();
long newMaxValue = sequenceDTO.getCurrentValue() + sequenceDTO.getIncrementValue();
if (newMaxValue > sequenceDTO.getMaximum()) {
newMaxValue = sequenceDTO.getMaximum();
/*新的起始值*/
if ("Y".equals(sequenceDTO.getCycleFlg())) {
sequenceDTO.setCurrentValue(1L);
} else {
sequenceDTO.setCurrentValue(sequenceDTO.getMaximum());
}
} else {
/*新的起始值*/
sequenceDTO.setCurrentValue(newMaxValue);
}
/*最后更新时间*/
if (sequenceDTO.getCurrentValue() > overLong) {
log.error("{}序列已经只剩10^19次方个，请及时分配新的，请速度重新设计解决方案", seqName);
}
sequenceDTO.setUpdTime(new Date());
int result = sequenceRepository.updateSequenceById(sequenceDTO);
if (result > 0) {
log.info("获取序列{}的数据成功，新的序列范围为[{},{}]", seqName, newMinValue, newMaxValue - 1);
sequenceRange = new SequenceRange(newMinValue, newMaxValue - 1);
return sequenceRange;
} else {
log.error("获取{}的数据失败，数据库中没有查询到此数据", seqName);
return null;
}
}
}
}
}
8、生成各种Sequence序列
@Service
@Slf4j
public class SequenceClient {
@Autowired
MySqlSequenceFactory mySqlSequenceFactory;
/**
* 功能描述: 获取新的序列号
*/
public String getSeqNo(String seqName,String prefix,int len){
log.info("准备获取新的{}序列号",seqName);
Long initNum = mySqlSequenceFactory.getNextVal(seqName);
StringBuffer stringBuffer = new StringBuffer(prefix);
stringBuffer.append(StringUtil.leftPad(String.valueOf(initNum), len, CommonConstants.SEQUENCE_PREFIX));
String newCashTokenNo = stringBuffer.toString();
log.info("获取新的{}序列号成功，编号为{}", seqName, newCashTokenNo);
return newCashTokenNo;
}
/**
* 功能描述: 获取新的序列号
*/
public String getSeqNoEnum(SequenceEnum seqName, String prefix) {
log.info("准备获取新的{}序列号", seqName.getCode());
Long initNum = mySqlSequenceFactory.getNextVal(seqName.getCode());
StringBuffer stringBuffer = new StringBuffer(prefix);
stringBuffer.append(StringUtil.leftPad(String.valueOf(initNum), seqName.getLen(), CommonConstants.SEQUENCE_PREFIX));
String newCashTokenNo = stringBuffer.toString();
log.info("获取新的{}序列号成功，编号为{}", seqName.getCode(), newCashTokenNo);
return newCashTokenNo;
}
}
@Test
public void 唯一序列验证(){
//商户费率模板号要求 TS+当前日期YYYYMMDD+6位序列号 如TS20200610000001
String prefix = CommonConstants.TEMP_ID_SEQ_PREFIX+DateUtil.getCurrentDate();
String tempId = sequenceClient.getSeqNo(KeyConstants.TEMP_ID_SEQ,prefix,6);
System.err.println("运行结果："+tempId);
}
运行结果：
生成序列号后，该序列号的当前值：
