实现类似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);
}