日期+序列构建系统唯一序列号（二）

前一篇博客中介绍了如何简单构建时间+序号的唯一序列号，

本次介绍下构建带有按年、月、日复位的时间+序号的唯一序列号方法。

基本思路：

1、设计一张表存储序列号，字段要包含：序列号、复位标志、时间；

2、每次取序列号的时候，都需要拿当前时间和存储的时间比较；

3、判断复位标志，

     如果是按年复位，比较的年不同则序列号清零；

     如果是按月复位，比较的月不同则序列号清零；

     如果是按日复位，比较的日不同则序列号清零；

     如果比较的日期相同，则序列号+1后更新到表格，时间也要更新。

show code

一、建表sql

-- Create tablecreate table T_SERIAL(  id          VARCHAR2(32) not null,  name        VARCHAR2(50),  scope       VARCHAR2(50),  serial      VARCHAR2(10),  formatter   VARCHAR2(64),  description VARCHAR2(100),  subsys      CHAR(1),  st          TIMESTAMP(6),  revision    INTEGER)tablespace USERS  pctfree 10  initrans 1  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );-- Add comments to the table comment on table T_SERIAL is '序列号记录表';-- Add comments to the columns comment on column T_SERIAL.id is '物理主键';comment on column T_SERIAL.name is '序列号名称(英文或字母表示)';comment on column T_SERIAL.scope is '序列号取值范围';comment on column T_SERIAL.serial is '序列号';comment on column T_SERIAL.formatter is '序列号格式化位数(序列号补足位数前面补0)';comment on column T_SERIAL.description is '序列号中文描述';comment on column T_SERIAL.subsys is '序列号复位标志(1：按天复位 2：按月复位 3：按年复位)';comment on column T_SERIAL.st is '系统时间';comment on column T_SERIAL.revision is '序列版本号';-- Create/Recreate primary, unique and foreign key constraints alter table T_SERIAL add constraint PRIMARY_KEY_B3 primary key (ID)  using index   tablespace USERS  pctfree 10  initrans 2  maxtrans 255  storage  (    initial 64K    next 1M    minextents 1    maxextents unlimited  );--  插入序列号--  发送流水号 按天复位   格式如：20170911000000001insert into T_SERIAL(id, name, scope, serial, formatter, description, subsys, st, revision) values(sys_guid(), 'FSLSH','' , '0', '%09d', '发送流水号', '1', sysdate, '0');--  业务流水号 按月复位   格式如：201709000000001insert into T_SERIAL(id, name, scope, serial, formatter, description, subsys, st, revision) values(sys_guid(), 'YWLSH','' , '0', '%09d', '业务流水号', '2', sysdate, '0');--  角色编号 按年复位   格式如：201700001insert into T_SERIAL(id, name, scope, serial, formatter, description, subsys, st, revision) values(sys_guid(), 'ROLECODE','' , '0', '%05d', '角色编号', '3', sysdate, '0');

二、表实例

package serial;import java.util.Date; public class CtlSerial{   private String id;   private String name;   private String scope;   private String serial;   private String formatter;   private String description;   private String subsys;   private Date st;   private int revision;    public String getId(){     return this.id; }    public void setId(String id) {     this.id = id;   }    public String getName() {     return this.name; }    public void setName(String name) {     this.name = name;   }    public String getScope() {     return this.scope; }    public void setScope(String scope) {     this.scope = scope;   }    public String getSerial() {     return this.serial; }    public void setSerial(String serial) {     this.serial = serial;   }    public String getFormatter() {     return this.formatter; }    public void setFormatter(String formatter) {     this.formatter = formatter;   }    public String getDescription() {     return this.description; }    public void setDescription(String description) {     this.description = description;   }    public String getSubsys() {     return this.subsys; }    public void setSubsys(String subsys) {     this.subsys = subsys;   }    public Date getSt() {     return this.st; }    public void setSt(Date st) {     this.st = st;   }    public int getRevision() {     return this.revision; }    public void setRevision(int revision) {     this.revision = revision;   }    public int getRevisionNext() {     return (this.revision + 1);   } }

三、业务序列号获取

package serial;import java.util.Calendar;import com.tienon.framework.supports.SpringContext;/** * @author leiwei * @Description 业务序列号获取,如业务流水号   获取方法Serial.getYWLSH() * @date 2016年10月10日 */public class Serial { static CtlSerialService ctlSerialService = SpringContext.getApplicationContext().getBean(CtlSerialService.class);public static final String YEAR = "3";public static final String MONTH = "2";public static final String DAY = "1";public static String getBusinessCode(String name){String busiCode = null;CtlSerial serial = ctlSerialService.getCtlSerial(name);try{ if(serial != null){String formatter = serial.getFormatter();if(formatter != null && !"".equals(formatter))busiCode = String.format(formatter, Integer.parseInt(serial.getSerial()));elsebusiCode = serial.getSerial();}String subsys = serial.getSubsys();//复位标识符if(subsys != null) {String prefix = "";//前缀Calendar calendar = Calendar.getInstance();int year = calendar.get(Calendar.YEAR);int month = calendar.get(Calendar.MONTH) + 1;String months = String.format("%02d", month);int day = calendar.get(Calendar.DAY_OF_MONTH);String days = String.format("%02d", day);if(YEAR.equals(subsys)) {//按年复位     格式：yyyy+序列号prefix = String.valueOf(year);} else if(MONTH.equals(subsys)){//按月复位    格式：yyyyMM+序列号prefix = String.valueOf(year) + months;} else if(DAY.equals(subsys)){//按日复位    格式：yyyyMMdd+序列号prefix = String.valueOf(year) + months + days;}busiCode = prefix + busiCode;}}catch(Exception e){throw new RuntimeException("序列号{"+name+"}格式化出错!", e);}return busiCode;}/** * 获取交易流水号 * @return */public static String getYWLSH(){return getBusinessCode("YWLSH");}}

四、处理序列号核心service

package serial;import java.util.Calendar;import java.util.Date;import java.util.concurrent.ConcurrentHashMap;import java.util.concurrent.ConcurrentMap;import javax.annotation.Resource;import org.apache.commons.lang3.StringUtils;public class CtlSerialService {@ResourceCtlSerialMapper ctlSerialMapper;public CtlSerial getCtlSerial(String name) {//每次查询序列号的时候都使用悲观锁防止并发问题s  select ... from ...  where ... for updateCtlSerial ctlSerial = this.ctlSerialMapper.selectByName(name);//如果该name对应的序列号不存在，则新增一条记录if (ctlSerial == null) {ctlSerial = new CtlSerial();ctlSerial.setId(UUID.randomUUID().toString().replace("-", ""));ctlSerial.setName(name);ctlSerial.setRevision(1);ctlSerial.setSerial("1");ctlSerial.setSt(new Date());this.ctlSerialMapper.insert(ctlSerial);}String subsys = ctlSerial.getSubsys();Date st = ctlSerial.getSt();if ((StringUtils.isNotEmpty(subsys)) && (null != st)) {Calendar bizCal = Calendar.getInstance();bizCal.setTime(st);Calendar sysCal = Calendar.getInstance();sysCal.setTime(new Date());if (("1".equals(subsys)) && (bizCal.get(5) != sysCal.get(5))){//日复位ctlSerial.setSerial("0");} else if (("2".equals(subsys)) && (bizCal.get(2) != sysCal.get(2))){//月复位ctlSerial.setSerial("0");} else if (("3".equals(subsys)) && (bizCal.get(1) != sysCal.get(1))){//年复位ctlSerial.setSerial("0");}}//序号加一后更新序号记录表long oldValue = Long.parseLong(ctlSerial.getSerial());long newValue = oldValue + 1L;ctlSerial.setSerial(Long.toString(newValue));ctlSerial.setSt(new Date());this.ctlSerialMapper.updateByPrimaryKey(ctlSerial);return ctlSerial;}}

五、DAO接口

package serial;public abstract interface CtlSerialMapper {public abstract int insert(CtlSerial paramCtlSerial);public abstract int updateByPrimaryKey(CtlSerial paramCtlSerial);public abstract CtlSerial selectByName(String paramString);}

六、SQL

<?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="serial.CtlSerialMapper"><resultMap id="BaseResultMap" type="serial.CtlSerial"><id column="ID" property="id" jdbcType="VARCHAR" /><result column="NAME" property="name" jdbcType="VARCHAR" /><result column="SCOPE" property="scope" jdbcType="VARCHAR" /><result column="SERIAL" property="serial" jdbcType="VARCHAR" /><result column="FORMATTER" property="formatter" jdbcType="VARCHAR" /><result column="DESCRIPTION" property="description" jdbcType="VARCHAR" /><result column="SUBSYS" property="subsys" jdbcType="CHAR" /><result column="ST" property="st" jdbcType="TIMESTAMP" /><result column="REVISION" property="revision" jdbcType="INTEGER" /></resultMap><sql id="Base_Column_List">ID, NAME, SCOPE, SERIAL, FORMATTER, DESCRIPTION, SUBSYS,ST, REVISION</sql><insert id="insert" parameterType="serial.CtlSerial">insert into SS_CTL_SERIAL (ID,NAME,SCOPE,SERIAL,FORMATTER,DESCRIPTION,SUBSYS,ST,REVISION) values (#{id,jdbcType=VARCHAR},#{name,jdbcType=VARCHAR},#{scope,jdbcType=VARCHAR},#{serial,jdbcType=VARCHAR},#{formatter,jdbcType=VARCHAR},#{description,jdbcType=VARCHAR},#{subsys,jdbcType=CHAR},#{st,jdbcType=TIMESTAMP},1)</insert><update id="updateByPrimaryKey" parameterType="serial.CtlSerial">updateSS_CTL_SERIAL setSERIAL = #{serial,jdbcType=VARCHAR},ST = #{st,jdbcType=TIMESTAMP},REVISION = #{revisionNext,jdbcType=INTEGER}whereID = #{id,jdbcType=VARCHAR}AND REVISION = #{revision,jdbcType=INTEGER}</update><select id="selectByName" resultMap="BaseResultMap">select *from SS_CTL_SERIALwhereNAME = #{name,jdbcType=VARCHAR}for update</select></mapper>