Mysql中的Sequence實現方法實例詳解

CREATE?TABLE?`t_sequence`?(  `sequence_name`?varchar(64)?CHARACTER?SET?utf8?COLLATE?utf8_general_ci?NOT?NULL?COMMENT?'序列名稱'?,  `value`?int(11)?NULL?DEFAULT?NULL?COMMENT?'當前值'?,  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?'序列名稱',  ?`SEQ_VALUE`?bigint(20)?NOT?NULL?COMMENT?'目前序列值',  ?`MIN_VALUE`?bigint(20)?NOT?NULL?COMMENT?'最小值',  ?`MAX_VALUE`?bigint(20)?NOT?NULL?COMMENT?'最大值',  ?`STEP`?bigint(20)?NOT?NULL?COMMENT?'每次取值的數量',  ?`TM_CREATE`?datetime?NOT?NULL?COMMENT?'創建時間',  ?`TM_SMP`?datetime?NOT?NULL?DEFAULT?CURRENT_TIMESTAMP?ON?UPDATE?CURRENT_TIMESTAMP?COMMENT?'修改時間',  ?PRIMARY?KEY?(`SEQ_NAME`)  )?ENGINE=InnoDB?DEFAULT?CHARSET=utf8mb4?COMMENT='流水號生成表';

/**  ?*?<p></p>  ?*?@author?coderzl  ?*?@Title?MysqlSequence  ?*?@Description?基于mysql數據庫實現的序列  ?*?@date?2017/6/6?23:03  ?*/  public?interface?MysqlSequence?{  ?/**  ??*?<p>  ??*?獲取指定sequence的序列號  ??*?</p>  ??*?@param?seqName?sequence名  ??*?@return?String?序列號  ??*/  ?public?String?nextVal(String?seqName);  }

/**  ?*?<p></p>  ?*  ?*?@author?coderzl  ?*?@Title?SequenceRange  ?*?@Description?序列區間，用于緩存序列  ?*?@date?2017/6/6?22:58  ?*/  ?@Data  public?class?SequenceRange?{  ?private?final?long??min;  ?private?final?long??max;  ?/**?*/  ?private?final?AtomicLong?value;  ?/**?是否超限?*/  ?private?volatile?boolean?over?=?false;    ?/**  ??*?構造.  ??*  ??*?@param?min?  ??*?@param?max?  ??*/  ?public?SequenceRange(long?min,?long?max)?{  ??this.min?=?min;  ??this.max?=?max;  ??this.value?=?new?AtomicLong(min);  ?}    ?/**  ??*?<p>Gets?and?increment</p>  ??*  ??*?@return?  ??*/  ?public?long?getAndIncrement()?{  ??long?currentValue?=?value.getAndIncrement();  ??if?(currentValue?&gt;?max)?{  ???over?=?true;  ???return?-1;  ??}    ??return?currentValue;  ?}    }

@Data  public?class?MysqlSequenceBo?{  ?/**  ??*?seq名  ??*/  ?private?String?seqName;  ?/**  ??*?當前值  ??*/  ?private?Long?seqValue;  ?/**  ??*?最小值  ??*/  ?private?Long?minValue;  ?/**  ??*?最大值  ??*/  ?private?Long?maxValue;  ?/**  ??*?每次取值的數量  ??*/  ?private?Long?step;  ?/**?*/  ?private?Date?tmCreate;  ?/**?*/  ?private?Date?tmSmp;    ?public?boolean?validate(){  ??//一些簡單的校驗。如當前值必須在最大最小值之間。step值不能大于max與min的差  ??if?(StringUtil.isBlank(seqName)?||?minValue?=?maxValue?||?maxValue?-?minValue??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();  }</mysqlsequencebo>

<?xml  version="1.0" encoding="UTF-8" ?>nbsp;mapper?PUBLIC?"-//mybatis.org//DTD?Mapper?3.0//EN"?"http://mybatis.org/dtd/mybatis-3-mapper.dtd"?&gt;  <mapper>  ?<resultmap>  ??<result></result>  ??<result></result>  ??<result></result>  ??<result></result>  ??<result></result>  ??<result></result>  ??<result></result>  ?</resultmap>  ?<delete>  ??delete?from?t_pub_sequence  ??where?SEQ_NAME?=?#{seqName,jdbcType=VARCHAR}  ?</delete>  ?<insert>  ??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>  ??update?t_pub_sequence  ??set?SEQ_VALUE?=?#{newValue,jdbcType=BIGINT}  ??where?SEQ_NAME?=?#{seqName,jdbcType=VARCHAR}?and?SEQ_VALUE?=?#{oldValue,jdbcType=BIGINT}  ?</update>    ?<select>  ??select?SEQ_NAME,?SEQ_VALUE,?MIN_VALUE,?MAX_VALUE,?STEP  ??from?t_pub_sequence  ?</select>    ?<select>  ??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>  ??*?獲取指定sequence的序列號  ??*?</p>  ??*  ??*?@param?seqName?sequence名  ??*?@return?String?序列號  ??*?@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>?holderMap?=?new?ConcurrentHashMap();    ?@Autowired  ?private?MysqlSequenceDAO?msqlSequenceDAO;  ?/**?單個sequence初始化樂觀鎖更新失敗重試次數?*/  ?@Value("${seq.init.retry:5}")  ?private?int?initRetryNum;  ?/**?單個sequence更新序列區間樂觀鎖更新失敗重試次數?*/  ?@Value("${seq.get.retry:20}")  ?private?int?getRetryNum;    ?@PostConstruct  ?private?void?init(){  ??//初始化所有sequence  ??initAll();  ?}      ?/**  ??*?<p>?加載表中所有sequence，完成初始化?</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();  ?}    }</mysqlsequencebo></string>

public?class?MysqlSequenceHolder?{    ?private?final?Lock?lock????=?new?ReentrantLock();    ?/**?seqName?*/  ?private?String?seqName;    ?/**?sequenceDao?*/  ?private?MysqlSequenceDAO?sequenceDAO;    ?private?MysqlSequenceBo?sequenceBo;  ?/**?*/  ?private?SequenceRange?sequenceRange;  ?/**?是否初始化?*/  ?private?volatile?boolean??isInitialize??=?false;  ?/**?sequence初始化重試次數?*/  ?private?int?initRetryNum;  ?/**?sequence獲取重試次數?*/  ?private?int?getRetryNum;    ?/**  ??*?<p>?構造方法?</p>  ??*?@Title?MysqlSequenceHolder  ??*?@param?sequenceDAO?  ??*?@param?sequenceBo  ??*?@param?initRetryNum?初始化時，數據庫更新失敗后重試次數  ??*?@param?getRetryNum?獲取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>?初始化?</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);  ??}  ??//?初始化該sequence  ??try?{  ???initSequenceRecord(sequenceBo);  ??}?catch?(SequenceException?e)?{  ???throw?e;  ??}  ??isInitialize?=?true;  ?}    ?/**  ??*?<p>?獲取下一個序列號?</p>  ??*?@Title?getNextVal  ??*?@param  ??*?@return?long  ??*?@author?coderzl  ??*/  ?public?long?getNextVal(){  ??if(isInitialize?==?false){  ???throw?new?SequenceException("["?+?seqName?+?"]?the?MysqlSequenceHolder?not?inited");  ??}  ??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>?初始化當前這條記錄?</p>  ??*?@Title?initSequenceRecord  ??*?@Description  ??*?@param?sequenceBo  ??*?@return?void  ??*?@author?coderzl  ??*/  ?private?void?initSequenceRecord(MysqlSequenceBo?sequenceBo){  ??//在限定次數內，樂觀鎖更新數據庫記錄  ??for(int?i?=?1;?i??0){  ????sequenceRange?=?new?SequenceRange(curBo.getSeqValue(),newValue?-?1);  ????curBo.setSeqValue(newValue);  ????this.sequenceBo?=?curBo;  ????return;  ???}else{  ????continue;  ???}  ??}  ??//限定次數內，更新失敗，拋出異常  ??throw?new?SequenceException("["?+?seqName?+?"]?sequenceBo?update?error");  ?}    ?/**  ??*?<p>?檢查新值是否合法?新的當前值是否在最大最小值之間</p>  ??*?@param?curValue  ??*?@param?curBo  ??*?@return?boolean  ??*?@author?coderzl  ??*/  ?private?boolean?checkCurrentValue(long?curValue,MysqlSequenceBo?curBo){  ??if(curValue?&gt;?curBo.getMinValue()?&amp;&amp;?curValue??重置sequence當前值?：當前sequence達到最大值時，重新從最小值開始?  ??*?@Title?resetCurrentValue  ??*?@param?curBo  ??*?@return?long  ??*?@author?coderzl  ??*/  ?private?long?resetCurrentValue(MysqlSequenceBo?curBo){  ??return?curBo.getMinValue();  ?}    ?/**  ??*?<p>?緩存區間使用完畢時，重新讀取數據庫記錄，緩存新序列段?</p>  ??*?@Title?retryRange  ??*?@param?SequenceRange  ??*?@author?coderzl  ??*/  ?private?SequenceRange?retryRange(){  ??for(int?i?=?1;?i??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服務?*/  ?@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位時間＋8位序列?+?6位隨機數＝24位流水號  ???return?jrn;  ??}?catch?(Exception?e)?{  ???//TODO  ??}  ?}  ?  ?private?String?leftPadding(String?seq,int?len){  ??String?res?="";  ??String?str?="";  ??if(seq.length()<len></len>