Sequence implementation method based on MySQL

CREATE TABLE `t_sequence` (
`sequence_name` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT 'Sequence name' ,
`value` int(11) NULL DEFAULT NULL COMMENT 'Current value' ,
PRIMARY KEY (`sequence_name`)
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
ROW_FORMAT=COMPACT
;

CREATE DEFINER = `root`@`localhost` FUNCTION `nextval`(sequence_name varchar(64))
 RETURNS int(11)
BEGIN
 declare current integer;
 set current = 0;
 
 update t_sequence t set t.value = t.value + 1 where t.sequence_name = sequence_name;
 select t.value into current from t_sequence t where t.sequence_name = sequence_name;

 return current;
end;

CREATE TABLE `t_pub_sequence` (
 `SEQ_NAME` varchar(128) CHARACTER SET utf8 NOT NULL COMMENT 'Sequence name',
 `SEQ_VALUE` bigint(20) NOT NULL COMMENT 'Current sequence value',
 `MIN_VALUE` bigint(20) NOT NULL COMMENT 'Minimum value',
 `MAX_VALUE` bigint(20) NOT NULL COMMENT 'Maximum value',
 `STEP` bigint(20) NOT NULL COMMENT 'The number of values ​​taken each time',
 `TM_CREATE` datetime NOT NULL COMMENT 'Creation time',
 `TM_SMP` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Modification time',
 PRIMARY KEY (`SEQ_NAME`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Serial number generation table';

/**
 * <p></p>
 * @author coderzl
 * @Title MysqlSequence
 * @Description Sequence based on MySQL database * @date 2017/6/6 23:03
 */
public interface MysqlSequence {
 /**
  * <p>
  * Get the serial number of the specified sequence * </p>
  * @param seqName sequence name* @return String sequence number*/
 public String nextVal(String seqName);
}

/**
 * <p></p>
 *
 * @author coderzl
 * @Title SequenceRange
 * @Description Sequence interval, used to cache sequence * @date 2017/6/6 22:58
 */
 @Data
public class SequenceRange {
 private final long min;
 private final long max;
 /** */
 private final AtomicLong value;
 /** Is it over the limit? */
 private volatile boolean over = false;

 /**
  * Construction.
  *
  * @param min 
  * @param max 
  */
 public SequenceRange(long min, long max) {
  this.min = min;
  this.max = max;
  this.value = new AtomicLong(min);
 }

 /**
  * <p>Gets and increments</p>
  *
  * @return 
  */
 public long getAndIncrement() {
  long currentValue = value.getAndIncrement();
  if (currentValue > max) {
   over = true;
   return -1;
  }

  return currentValue;
 }

}

@Data
public class MysqlSequenceBo {
 /**
  * seq name */
 private String seqName;
 /**
  * Current value */
 private Long seqValue;
 /**
  * Minimum value */
 private Long minValue;
 /**
  * Maximum value */
 private Long maxValue;
 /**
  * The number of values ​​taken each time*/
 private Long step;
 /** */
 private Date tmCreate;
 /** */
 private Date tmSmp;

 public boolean validate(){
  //Some simple checks. For example, the current value must be between the maximum and minimum values. step value cannot be greater than the difference between max and minif (StringUtil.isBlank(seqName) || minValue < 0 || maxValue <= 0 || step <= 0 || minValue >= maxValue || maxValue - minValue <= step ||seqValue < minValue || seqValue > maxValue ) {
   return false;
  }
  return true; 
 }
}

public interface MysqlSequenceDAO {
 /**
 * 
 */
 public int createSequence(MysqlSequenceBo bo);

 public int updSequence(@Param("seqName") String seqName, @Param("oldValue") long oldValue ,@Param("newValue") long newValue);

 public int delSequence(@Param("seqName") String seqName);

 public MysqlSequenceBo getSequence(@Param("seqName") String seqName);

 public List<MysqlSequenceBo> getAll();
}

<?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.xxxxx.core.sequence.impl.dao.MysqlSequenceDAO" >
 <resultMap id="BaseResultMap" type="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >
  <result column="SEQ_NAME" property="seqName" jdbcType="VARCHAR" />
  <result column="SEQ_VALUE" property="seqValue" jdbcType="BIGINT" />
  <result column="MIN_VALUE" property="minValue" jdbcType="BIGINT" />
  <result column="MAX_VALUE" property="maxValue" jdbcType="BIGINT" />
  <result column="STEP" property="step" jdbcType="BIGINT" />
  <result column="TM_CREATE" property="tmCreate" jdbcType="TIMESTAMP" />
  <result column="TM_SMP" property="tmSmp" jdbcType="TIMESTAMP" />
 </resultMap>
 <delete id="delSequence" parameterType="java.lang.String" >
  delete from t_pub_sequence
  where SEQ_NAME = #{seqName,jdbcType=VARCHAR}
 </delete>
 <insert id="createSequence" parameterType="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >
  insert into t_pub_sequence (SEQ_NAME,SEQ_VALUE,MIN_VALUE,MAX_VALUE,STEP,TM_CREATE)
  values ​​(#{seqName,jdbcType=VARCHAR}, #{seqValue,jdbcType=BIGINT},
  #{minValue,jdbcType=BIGINT}, #{maxValue,jdbcType=BIGINT}, #{step,jdbcType=BIGINT},
  now())
 </insert>
 <update id="updSequence" parameterType="com.xxxxx.core.sequence.impl.MysqlSequenceBo" >
  update t_pub_sequence
  set SEQ_VALUE = #{newValue,jdbcType=BIGINT}
  where SEQ_NAME = #{seqName,jdbcType=VARCHAR} and SEQ_VALUE = #{oldValue,jdbcType=BIGINT}
 </update>

 <select id="getAll" resultMap="BaseResultMap" >
  select SEQ_NAME, SEQ_VALUE, MIN_VALUE, MAX_VALUE, STEP
  from t_pub_sequence
 </select>

 <select id="getSequence" resultMap="BaseResultMap" >
  select SEQ_NAME, SEQ_VALUE, MIN_VALUE, MAX_VALUE, STEP
  from t_pub_sequence
  where SEQ_NAME = #{seqName,jdbcType=VARCHAR}
 </select>
</mapper>

@Repository("mysqlSequence")
public class MysqlSequenceImpl implements MysqlSequence{

 @Autowired
 private MysqlSequenceFactory mysqlSequenceFactory;
 /**
  * <p>
  * Get the serial number of the specified sequence * </p>
  *
  * @param seqName sequence name * @return String sequence number * @author coderzl
  */
 @Override
 public String nextVal(String seqName) {
  return Objects.toString(mysqlSequenceFactory.getNextVal(seqName));
 }
}

@Component
public class MysqlSequenceFactory {

 private final Lock lock = new ReentrantLock();

 /** */
 private Map<String,MysqlSequenceHolder> holderMap = new ConcurrentHashMap<>();

 @Autowired
 private MysqlSequenceDAO msqlSequenceDAO;
 /** The number of retries for failed initialization of optimistic lock update for a single sequence*/
 @Value("${seq.init.retry:5}")
 private int initRetryNum;
 /** The number of retries for failed optimistic lock updates of a single sequence interval*/
 @Value("${seq.get.retry:20}")
 private int getRetryNum;

 @PostConstruct
 private void init(){
  //Initialize all sequences
  initAll();
 }


 /**
  * <p> Load all sequences in the table and complete initialization</p>
  * @return void
  * @author coderzl
  */
 private void initAll(){
  try {
   lock.lock();
   List<MysqlSequenceBo> boList = msqlSequenceDAO.getAll();
   if (boList == null) {
    throw new IllegalArgumentException("The sequenceRecord is null!");
   }
   for (MysqlSequenceBo bo : boList) {
    MysqlSequenceHolder holder = new MysqlSequenceHolder(msqlSequenceDAO, bo,initRetryNum,getRetryNum);
    holder.init();
    holderMap.put(bo.getSeqName(), holder);
   }
  }finally {
   lock.unlock();
  }
 }


 /**
  * <p> </p>
  * @param seqName
  * @return long
  * @author coderzl
  */
 public long getNextVal(String seqName){
  MysqlSequenceHolder holder = holderMap.get(seqName);
  if (holder == null) {
   try {
    lock.lock();
    holder = holderMap.get(seqName);
    if (holder != null){
     return holder.getNextVal();
    }
    MysqlSequenceBo bo = msqlSequenceDAO.getSequence(seqName);
    holder = new MysqlSequenceHolder(msqlSequenceDAO, bo,initRetryNum,getRetryNum);
    holder.init();
    holderMap.put(seqName, holder);
   }finally {
    lock.unlock();
   }
  }
  return holder.getNextVal();
 }

}

public class MysqlSequenceHolder {

 private final Lock lock = new ReentrantLock();

 /** seqName */
 private String seqName;

 /** sequenceDao */
 private MysqlSequenceDAO sequenceDAO;

 private MysqlSequenceBo sequenceBo;
 /** */
 private SequenceRange sequenceRange;
 /** Whether to initialize */
 private volatile boolean isInitialize = false;
 /** Sequence initialization retry times*/
 private int initRetryNum;
 /** sequence gets the number of retries*/
 private int getRetryNum;

 /**
  * <p>Constructor</p>
  * @Title MysqlSequenceHolder
  * @param sequenceDAO 
  * @param sequenceBo
  * @param initRetryNum The number of retries after a database update fails during initialization * @param getRetryNum The number of retries after a database update fails when getting nextVal * @return
  * @author coderzl
  */
 public MysqlSequenceHolder(MysqlSequenceDAO sequenceDAO, MysqlSequenceBo sequenceBo, int initRetryNum, int getRetryNum) {
  this.sequenceDAO = sequenceDAO;
  this.sequenceBo = sequenceBo;
  this.initRetryNum = initRetryNum;
  this.getRetryNum = getRetryNum;
  if(sequenceBo != null)
   this.seqName = sequenceBo.getSeqName();
 }

 /**
  * <p> Initialization </p>
  * @Title init
  * @param
  * @return void
  * @author coderzl
  */
 public void init(){
  if (isInitialize == true) {
   throw new SequenceException("[" + seqName + "] the MysqlSequenceHolder has inited");
  }
  if (sequenceDAO == null) {
   throw new SequenceException("[" + seqName + "] the sequenceDao is null");
  }
  if (seqName == null || seqName.trim().length() == 0) {
   throw new SequenceException("[" + seqName + "] the sequenceName is null");
  }
  if (sequenceBo == null) {
   throw new SequenceException("[" + seqName + "] the sequenceBo is null");
  }
  if (!sequenceBo.validate()){
   throw new SequenceException("[" + seqName + "] the sequenceBo validate fail. BO:"+sequenceBo);
  }
  // Initialize the sequence
  try {
   initSequenceRecord(sequenceBo);
  } catch (SequenceException e) {
   throw e;
  }
  isInitialize = true;
 }

 /**
  * <p>Get the next sequence number</p>
  * @Title getNextVal
  * @param
  * @return long
  * @author coderzl
  */
 public long getNextVal(){
  if(isInitialize == false){
   throw new SequenceException("[" + seqName + "] the MysqlSequenceHolder not initialized");
  }
  if (sequenceRange == null) {
   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;
 }

 /**
  * <p> Initialize the current record </p>
  * @Title initSequenceRecord
  * @Description
  * @param sequenceBo
  * @return void
  * @author coderzl
  */
 private void initSequenceRecord(MysqlSequenceBo sequenceBo){
  //Optimistic lock updates database records within a limited number of times for(int i = 1; i < initRetryNum; i++){
   //Query bo
   MysqlSequenceBo curBo = sequenceDAO.getSequence(sequenceBo.getSeqName());
   if(curBo == null){
    throw new SequenceException("[" + seqName + "] the current sequenceBo is null");
   }
   if (!curBo.validate()){
    throw new SequenceException("[" + seqName + "] the current sequenceBo validate fail");
   }
   //Change the current value long newValue = curBo.getSeqValue()+curBo.getStep();
   //Check the current value if(!checkCurrentValue(newValue,curBo)){
    newValue = resetCurrentValue(curBo);
   }
   int result = sequenceDAO.updSequence(sequenceBo.getSeqName(),curBo.getSeqValue(),newValue);
   if(result > 0){
    sequenceRange = new SequenceRange(curBo.getSeqValue(),newValue - 1);
    curBo.setSeqValue(newValue);
    this.sequenceBo = curBo;
    return;
   }else{
    continue;
   }
  }
  //If the update fails within the specified number of times, an exception is thrown throw new SequenceException("[" + seqName + "] sequenceBo update error");
 }

 /**
  * <p> Check whether the new value is legal and whether the new current value is between the maximum and minimum values</p>
  * @param curValue
  * @param curBo
  * @return boolean
  * @author coderzl
  */
 private boolean checkCurrentValue(long curValue,MysqlSequenceBo curBo){
  if(curValue > curBo.getMinValue() && curValue <= curBo.getMaxValue()){
   return true;
  }
  return false;
 }

 /**
  * <p> Reset the current value of the sequence: when the current sequence reaches the maximum value, it starts again from the minimum value</p>
  * @Title resetCurrentValue
  * @param curBo
  * @return long
  * @author coderzl
  */
 private long resetCurrentValue(MysqlSequenceBo curBo){
  return curBo.getMinValue();
 }

 /**
  * <p> When the cache interval is used up, re-read the database records and cache the new sequence segment</p>
  * @Title retryRange
  * @param SequenceRange
  * @author coderzl
  */
 private SequenceRange retryRange(){
  for(int i = 1; i < getRetryNum; i++){
   //Query bo
   MysqlSequenceBo curBo = sequenceDAO.getSequence(sequenceBo.getSeqName());
   if(curBo == null){
    throw new SequenceException("[" + seqName + "] the current sequenceBo is null");
   }
   if (!curBo.validate()){
    throw new SequenceException("[" + seqName + "] the current sequenceBo validate fail");
   }
   //Change the current value long newValue = curBo.getSeqValue()+curBo.getStep();
   //Check the current value if(!checkCurrentValue(newValue,curBo)){
    newValue = resetCurrentValue(curBo);
   }
   int result = sequenceDAO.updSequence(sequenceBo.getSeqName(),curBo.getSeqValue(),newValue);
   if(result > 0){
    sequenceRange = new SequenceRange(curBo.getSeqValue(),newValue - 1);
    curBo.setSeqValue(newValue);
    this.sequenceBo = curBo;
    return sequenceRange;
   }else{
    continue;
   }
  }
  throw new SequenceException("[" + seqName + "] sequenceBo update error");

 }
}

@Service
public class JrnGeneratorService {
 private static final String SEQ_NAME = "T_SEQ_TEST";

 /** sequence service */
 @Autowired
 private MySqlSequence mySqlSequence;
 
 public String generateJrn() {
  try {
   String sequence = mySqlSequence.getNextValue(SEQ_NAME);
   sequence = leftPadding(sequence,8);
   Calendar calendar = Calendar.getInstance();
   SimpleDateFormat sDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
   String nowdate = sDateFormat.format(calendar.getTime());
   nowdate.substring(4, nowdate.length());
   String jrn = nowdate + sequence + RandomUtil.getFixedLengthRandom(6); //10-digit time + 8-digit sequence + 6-digit random number = 24-digit serial number return jrn;
  } catch (Exception e) {
   //TODO
  }
 }
 
 private String leftPadding(String seq,int len){
  String res = "";
  String str = "";
  if(seq.length()<len){
   for(int i=0;i<len-seq.length();i++){
    str +="0"; 
   }   
  }
  res =str+seq;
  return res;
  
 }

}