基于数据库实现ID自动生成策略

实现类似mysql的主键递增功能， 并在此基础上做了增强， 可对于不同分类， 不同主键前缀自动递增。

表结构设计

t_sequence

字段	描述
ID	数据库主键
SEQ_PREFIX	生成的主键前缀
SEQ_TYPE	生成的主键类型
SEQ_NUM	生成的主键的最新序号
SEQ_GEN_DATE	主键最新更新时间
SEQ_CATEGORY	主键分类
SEQ_CHAR_WIDTH	主键数字部分的最大宽度， 如果seq_num的位数小于这个宽度， 则最终生成的主键前面补0

上代码：SequenceGenerator.java

public final class SequenceGenerator {
    private static final Log logger = LogFactory.getLog(SequenceGenerator.class);

    private static final String DEFAULT_SEQ_CATEGORY = "common";

    private static final Integer DEFAULT_SEQ_CHAR_WIDTH=4;

    private static final String SEQ_TYPE_NUMERIC="INT";

    private static final String SEQ_TYPE_CHAR="VARCHAR2";

    private final ReentrantLock lock = new ReentrantLock();



    private static class SequenceGeneratorHolder{
        private static SequenceGenerator instance = new SequenceGenerator();
    }

    private SequenceGenerator(){}

    public static SequenceGenerator getInstance(){
        return SequenceGeneratorHolder.instance;
    }

    public Integer getId(SequenceModel seqCriteria){
        final ReentrantLock lock = this.lock;
        lock.lock();
        try{
            SequenceService sequenceService =  ApplicationContextProvider.getBean(SequenceService.class);
            return sequenceService.getSeqId(seqCriteria);  //查询数据库在最新的Seq_num基础上加1
        }finally {
            lock.unlock();
        }
    }

    /**
     * 默认生成不带前缀的字符串id
     *
     * @return
     */
    public String generateStringId(){
        return generateStringId(DEFAULT_SEQ_CATEGORY,DEFAULT_SEQ_CHAR_WIDTH);
    }

    /**
     * 根据类别生成字符串ID, 使用默认字符宽度为4， 并且带前缀和category 一致
     * @param category
     * @return
     */
    public String generateStringId(String category){
        return generateStringId(category,category,DEFAULT_SEQ_CHAR_WIDTH);
    }

    public String generateStringId(String prefix,String category){
        return generateStringId(prefix,category,DEFAULT_SEQ_CHAR_WIDTH);
    }

    /**
     * generate varchar type id without prefix
     * @param category
     * @return
     */
    public String generateStringId(String category, Integer charWidth){
        return generateStringId(null,category,charWidth);
    }

    /**
     *
     * @param prefix
     * @param category
     * @param charWidth
     * @return
     */
    public String generateStringId(String prefix,String category, Integer charWidth){
        SequenceModel seqCriteria = new SequenceModel();
        seqCriteria.setSeqCategory(category);
        seqCriteria.setSeqPrefix(prefix);
        seqCriteria.setSeqCharWidth(charWidth);
        seqCriteria.setSeqType(SEQ_TYPE_CHAR);
        Integer result = getId(seqCriteria);
        String numStr = StrUtil.padPre(String.valueOf(result),charWidth,'0');
        String id = StrUtil.nullToEmpty(prefix)+numStr;
        return id;
    }

    public Integer generateNumericId(){
        return generateNumericId(DEFAULT_SEQ_CATEGORY);
    }

    /**
     * generate long type id
     * @return
     */
    public Integer generateNumericId(String category){
        SequenceModel seqCriteria = new SequenceModel();
        seqCriteria.setSeqCategory(category);
        seqCriteria.setSeqType(SEQ_TYPE_NUMERIC);
        Integer result = getId(seqCriteria);
        return result;
    }
}

SequenceModel.java  ---表结构对象封装

@Data
public class SequenceModel {
    private Integer id;

    private String seqPrefix;

    private String seqType;

    private Integer seqNum;

    private String seqCategory;

    private Integer seqCharWidth;

    private Date seqGenDate;
}

SequenceService.java 

这里才是整个生成策略的核心， 查询数据库的时候需要加悲观锁， 锁住整行数据， 查询，插入， 更新需要在同一个事务内完成， 否则容易发生死锁。

使用Mybatis操作数据库

@Service
public class SequenceService {
    @Autowired
    private SequenceMapper sequenceMapper;

    @Transactional(transactionManager = "businessDataSourceTM") // 这行不能少
    public Integer getSeqId(SequenceModel seqCriteria){
        try {
            SequenceModel seqResult = sequenceMapper.findSequence(seqCriteria);
            AtomicInteger atomicInteger = new AtomicInteger();
            if(seqResult == null){
                // 新建
                seqResult = seqCriteria;
                seqResult.setSeqNum(atomicInteger.incrementAndGet());
                seqResult.setSeqGenDate(DateUtil.nowDate());
                sequenceMapper.insertSequenceNum(seqResult);
            }else{
                //更新
                Integer seqNum = seqResult.getSeqNum();
                atomicInteger.set(seqNum);
                seqResult.setSeqNum(atomicInteger.incrementAndGet());
                seqResult.setSeqGenDate(DateUtil.nowDate());
                sequenceMapper.updateSequenceNum(seqResult);
            }
            return atomicInteger.get();
        } catch (Exception e) {
            log.error("generate Id occurs error.",e);
            return 0;
        }
    }
}

sequence-generator.xml -- mybatis 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="com.ey.general.mapper.SequenceMapper">
    <resultMap id="sequenceMap" type="com.ey.general.model.SequenceModel">
        <id property="id" column="ID"/>
        <result property="seqPrefix" column="SEQ_PREFIX"/>
        <result property="seqType" column="SEQ_TYPE"/>
        <result property="seqNum" column="SEQ_NUM"/>
        <result property="seqCategory" column="SEQ_CATEGORY"/>
        <result property="seqCharWidth" column="SEQ_CHAR_WIDTH"/>
        <result property="seqGenDate" column="SEQ_GEN_DATE"/>
    </resultMap>

    <select id="findSequence" parameterType="com.ey.general.model.SequenceModel" resultMap="sequenceMap">
        select
          seq.ID,
          seq.SEQ_PREFIX,
          seq.SEQ_TYPE,
          seq.SEQ_NUM,
          seq.SEQ_CHAR_WIDTH,
          seq.SEQ_GEN_DATE,
          seq.SEQ_CATEGORY
        from t_sequence seq
        where 1=1
            and seq.SEQ_CATEGORY = #{seqCriteria.seqCategory}
        <if test="seqCriteria.seqPrefix != null">
            and seq.SEQ_PREFIX = #{seqCriteria.seqPrefix}
        </if>
        for update
    </select>

    <update id="updateSequenceNum"  parameterType="com.ey.general.model.SequenceModel">
        update t_sequence set seq_num=#{seq.seqNum}, seq_gen_date=#{seq.seqGenDate,jdbcType=TIMESTAMP} where id=#{seq.id}
    </update>

    <insert id="insertSequenceNum" parameterType="com.ey.general.model.SequenceModel" >
        <selectKey keyProperty="id" resultType="java.lang.Integer" order="BEFORE">
            SELECT CZBANK_COMMON_SEQ.NEXTVAL as ID from DUAL
        </selectKey>
        insert into t_sequence (
          id,
          SEQ_PREFIX,
          SEQ_TYPE,
          SEQ_NUM,
          SEQ_CHAR_WIDTH,
          SEQ_GEN_DATE,
          SEQ_CATEGORY
        ) VALUES (
          #{id},
          #{seq.seqPrefix,jdbcType=VARCHAR},
          #{seq.seqType,jdbcType=VARCHAR},
          #{seq.seqNum},
          #{seq.seqCharWidth,jdbcType=INTEGER},
          #{seq.seqGenDate},
          #{seq.seqCategory}
        )
    </insert>
</mapper>

SequenceMapper.java

@Mapper
public interface SequenceMapper {
    SequenceModel findSequence(@Param("seqCriteria") SequenceModel seqCriteria);

    void updateSequenceNum(@Param("seq") SequenceModel seq);

    void insertSequenceNum(@Param("seq") SequenceModel seq);
}

 

转载于:https://my.oschina.net/u/857431/blog/3022482