数据库表SEQUENCE产生
sagacity（睿智）架构
第三讲：数据库sequence产生
先抛一个问题：
????? 大家在做系统时对一些表要产生主键(一般以流水或日期加流水方式产生)，大家一般怎么做呢？
????? 是不是：
????? select max(id) from table 或则通过hibernate自带的uuid等方式产生呢？
????? 这种做法非常痛苦，要想sequence有点意义（如日期+流水）就比较麻烦，效率也低，每次都去对应的表里面做全量查询！
????? sagacity中怎么做呢？
????? 我们通过一个TABLE_SEQUENCE表存放各个表的当前sequence，表结构如下：
???? create table SYS_TABLE_SEQUENCE
???? (
?????? SEQUENCE_NAME??????? varchar(200) not null,
?????? CURRENT_KEY_VALUE??? numeric(20,0) not null,
?????? DATE_VALUE?????????? numeric(8,0),
?????? primary key (SEQUENCE_NAME)
??? );
??? alter table SYS_TABLE_SEQUENCE comment '系统流水表';
??? 同时通过hibernate建立一个对象，SysTableSequence.java放在sagacity核心库中
??? 项目中的用法：
??? 在applicationContext.xml中配置sessionFactory时添加
??? <property name="mappingResources">
???<list>
????<value>
?????org/sagacity/framework/dao/model/SysTableSequence.hbm.xml
????</value>
????
???? 在DAO中我们如此调用
???? 1.单个流水：
???? BigDecimal seq=this.getBigDecimalSequence(PmPurchaseOrderList.class);
???? 2. 批量流水,取得的流水为第一个，其它的流水通过seq+1
???? BigDecimal seq=this.getBigDecimalSequence(PmPurchaseOrderList.class,size);
???? 3.日期+几位流水
???? BigDecimal getDateSequence(Class entityClass, Date nowDate,String dateStyle, int length);
???? 4.批量取日期+几位流水
???? BigDecimal getDateSequence(Class entityClass, Date nowDate,String dateStyle, int length, int size)；
?
??? 代码片段如下：
???? /**
? * 根据对象类获取序号
? *
? * @param entityClass
? * @return
? * @throws CreateSequenceException
? */
?protected BigDecimal getBigDecimalSequence(Class entityClass)
???throws CreateSequenceException {
??return getBigDecimalSequence(entityClass, 1);
?}
?
?/**
? * @todo example 获取以日期开头的序列号:seqName:orgData size:10 now seqence:10000
? *?????? return: 10001 now seqence:10010
? * @param seqName
? * @param size
? * @return
? * @throws CreateSequenceException
? */
?protected BigDecimal getBigDecimalSequence(Class entityClass, int size)
???throws CreateSequenceException {
??if (entityClass == null || size < 1)
???throw new CreateSequenceException("Input Parameters has Null!");
??else
???return getSequence(entityClass.getName(), null, null, null, size,
?????false);
?}
?
?/**
? * 根据日期获取SEQNO
? * @param entityClass
? * @param nowDate
? * @param dateStyle
? * @param length
? * @return
? * @throws CreateSequenceException
? */
?protected BigDecimal getDateSequence(Class entityClass, Date nowDate,
???String dateStyle, int length) throws CreateSequenceException {
??return getDateSequence(entityClass, nowDate, dateStyle, length, 1);
?}
?
?/**
? * @todo 获取以日期开头的序列号example:nowSeq=20051118001 ,
? *?????? condition:dateValue=20051118;length=3,size=4 update seq:20051118005
? *?????? return:20051118002
? * @param entityClass
? * @param nowDate
? * @param dateStyle
? * @param length
? * @param size
? * @return
? * @throws CreateSequenceException
? */
?protected BigDecimal getDateSequence(Class entityClass, Date nowDate,
???String dateStyle, int length, int size)
???throws CreateSequenceException {
??if (entityClass == null || length < 1 || size < 1)
???throw new CreateSequenceException("Input Parameters has Null!");
??else
???return getSequence(entityClass.getName(), nowDate, dateStyle,
?????new Integer(length), size, true);
?}
?
/**
? * @todo 获取以日期开头的序列号:example:nowSeq=20051118001 ,
? *?????? condition:dateValue=20051118;length=3,size=4 update seq:20051118005
? *?????? return:20051118002
? * @param seqName
? * @param nowDate
? * @param dateStyle
? * @param length
? * @param size
? * @param hasDate
? * @return
? * @throws CreateSequenceException
? */
?private BigDecimal getSequence(String seqName, Date nowDate,
???String dateStyle, Integer length, int size, boolean hasDate)
???throws CreateSequenceException {
??if (seqName == null || size < 1)
???throw new CreateSequenceException("Input Parameters has Null!");
??logger.debug("获取" + seqName + "的连续" + size + "个SEQUENCE!");
??try {
???synchronized (seqName) {
????TableSequence tableSeq = null;
????Object obj = this.getHibernateTemplate().get(
??????TableSequence.class, seqName);
????Long seqValue = new Long(0);
????// 非第一次创建表序号,取出当前的SEQNO
????if (obj != null) {
?????tableSeq = (TableSequence) obj;
?????seqValue = tableSeq.getCurrentKeyValue();
????} else {
?????// 第一次SEQNO=0
?????tableSeq = new TableSequence();
?????tableSeq.setSequenceName(seqName);
????}
????Long dateValue = null;
????// 有日期
????if (hasDate) {
?????// 如果日期值为空，则取系统当前日期
?????Date seqDate = (nowDate == null) ? DateUtil.getNowTime()
???????: nowDate;
?????dateValue = Long.valueOf(DateUtil.formatDate(seqDate,
???????dateStyle));
?????// 当前日期大于现有表里的日期，用当前日期替换seq 日期,seqNo从零开始
?????if (tableSeq.getDateValue() == null
???????|| dateValue.longValue() > tableSeq.getDateValue()
?????????.longValue()) {
??????tableSeq.setDateValue(dateValue);
??????seqValue = new Long(0);
?????}
????}
????// SEQNO 设置为当前SEQNO+增加量
????tableSeq.setCurrentKeyValue(new Long(seqValue.longValue()
??????+ size));
????if (obj != null)
?????this.getHibernateTemplate().update(tableSeq);
????else
?????this.getHibernateTemplate().save(tableSeq);
????// 返回起始seqNO
????BigDecimal startSeqNo = new BigDecimal(seqValue.longValue() + 1);
????// 日期
????if (hasDate)
?????return new BigDecimal(dateValue
???????+ StringUtil.addLeftZero2Len(startSeqNo.toString(),
?????????length));
????else
?????return startSeqNo;
???}
??} catch (Exception e) {
???e.printStackTrace();
???throw new CreateSequenceException("创建表:" + seqName
?????+ "的Sequence未成功!", e);
??}
?}
?
??请期待我的第四讲，excel导入工具！
1 楼 hot66hot 2009-02-20 受教了，搂住辛苦了 2 楼 yingfang05 2009-02-21 据说MySQL是不支持SEQUENCE的,支持IDENTITY和TABLE方式 3 楼 huangyh 2009-02-21 不错,跨数据库的sequence,兄弟,集群环境下如何? 4 楼 hot66hot 2009-02-22 好象只有oracle,db2支持sequence吧 5 楼 yuanyao 2009-02-22 postgreSQL也支持sequence... 6 楼 hot66hot 2009-02-23 哦，谢谢楼上的提醒