谢谢!
我写的是一个PRO
CREATE procedure epos_getIdentity (
@seqname varchar(50),
@needCheck int,
@sDropCond varchar(10),
@sSeqFormat varchar(30),
@sSeqValue varchar(60) output
)
AS
BEGIN
declare @seqTable varchar(100)
declare @needReset int
declare @tableExists int
declare @INSERT_SQL varchar(200)
declare @DT datetime
set @DT= getDate()
set @seqTable = '_SEQ_' + @seqName
set @INSERT_SQL = 'insert into ' + @seqTable + ' DEFAULT VALUES'
if exists (select * from dbo.sysobjects
where name=@seqTable and xtype='U')
set @TableExists = 1
else
set @TableExists = 0
if @TableExists = 0
begin
execute ('create table dbo.' + @seqTable + ' (S_VALUE int IDENTITY (1, 1) NOT NULL ) ')
execute (@Insert_sql)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,'1',null,@DT)
-- ** 2004/10/09 tangy 加入
if not exists (select * from Seq where SEQNAME=@seqname)
begin
set @sDropCond = [dbo].epos_GetIdentDropToken (@sDropCond,@DT)
insert into seq (SEQNAME,DROPCOND) values (@seqname,@sDropCond)
end
-- **
return 0
end
if @needCheck = 0
begin
execute (@insert_Sql)
set @sSeqValue = IDENT_CURRENT (@seqTable)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,@sSeqValue,null,@DT)
return 0
end
declare @sOldDropCond varchar(10)
set @sDropCond = [dbo].epos_GetIdentDropToken (@sDropCond,@DT)
select @sOldDropCond=dropCond from seq where SEQNAME=@seqname
if ( @@rowcount <= 0 )
begin
-- 找不到 , insert 一筆
insert into seq (SEQNAME,DROPCOND) values (@seqname,@sDropCond)
set @needReset = 1
end else begin
-- 找到了,比對 drop condition
if @sOldDropCond <> @sDropCond
begin
set @needReset = 1
-- Drop Condition 不相等,要 Drop identity(Table) , 重來
update seq set DROPCOND=@sDropCond where SEQNAME=@seqname
end
end
if @needReset > 0
begin
execute ('drop table ' + @seqTable)
execute ('create table dbo.' + @seqTable + ' (S_VALUE int IDENTITY (1, 1) NOT NULL ) ')
execute (@Insert_sql)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,'1',null,@DT)
return 0
end
execute (@insert_Sql)
set @sSeqValue = IDENT_CURRENT (@seqTable)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,@sSeqValue,null,@DT)
return 0
END
GOselect * from epos_getIdentity
我写的是一个PRO
CREATE procedure epos_getIdentity (
@seqname varchar(50),
@needCheck int,
@sDropCond varchar(10),
@sSeqFormat varchar(30),
@sSeqValue varchar(60) output
)
AS
BEGIN
declare @seqTable varchar(100)
declare @needReset int
declare @tableExists int
declare @INSERT_SQL varchar(200)
declare @DT datetime
set @DT= getDate()
set @seqTable = '_SEQ_' + @seqName
set @INSERT_SQL = 'insert into ' + @seqTable + ' DEFAULT VALUES'
if exists (select * from dbo.sysobjects
where name=@seqTable and xtype='U')
set @TableExists = 1
else
set @TableExists = 0
if @TableExists = 0
begin
execute ('create table dbo.' + @seqTable + ' (S_VALUE int IDENTITY (1, 1) NOT NULL ) ')
execute (@Insert_sql)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,'1',null,@DT)
-- ** 2004/10/09 tangy 加入
if not exists (select * from Seq where SEQNAME=@seqname)
begin
set @sDropCond = [dbo].epos_GetIdentDropToken (@sDropCond,@DT)
insert into seq (SEQNAME,DROPCOND) values (@seqname,@sDropCond)
end
-- **
return 0
end
if @needCheck = 0
begin
execute (@insert_Sql)
set @sSeqValue = IDENT_CURRENT (@seqTable)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,@sSeqValue,null,@DT)
return 0
end
declare @sOldDropCond varchar(10)
set @sDropCond = [dbo].epos_GetIdentDropToken (@sDropCond,@DT)
select @sOldDropCond=dropCond from seq where SEQNAME=@seqname
if ( @@rowcount <= 0 )
begin
-- 找不到 , insert 一筆
insert into seq (SEQNAME,DROPCOND) values (@seqname,@sDropCond)
set @needReset = 1
end else begin
-- 找到了,比對 drop condition
if @sOldDropCond <> @sDropCond
begin
set @needReset = 1
-- Drop Condition 不相等,要 Drop identity(Table) , 重來
update seq set DROPCOND=@sDropCond where SEQNAME=@seqname
end
end
if @needReset > 0
begin
execute ('drop table ' + @seqTable)
execute ('create table dbo.' + @seqTable + ' (S_VALUE int IDENTITY (1, 1) NOT NULL ) ')
execute (@Insert_sql)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,'1',null,@DT)
return 0
end
execute (@insert_Sql)
set @sSeqValue = IDENT_CURRENT (@seqTable)
set @sSeqValue = [dbo].epos_getIdentFormat(@sSeqFormat,@sSeqValue,null,@DT)
return 0
END
GOselect * from epos_getIdentity
private static SimpleDateFormat sdf=new SimpleDateFormat("yyyyMMdd");
private static int id=0;
private static String day;
static {
day=sdf.format(new Date());
} public synchronized static String next(){
String d=sdf.format(new Date());
if(d.equals(day)){
return d+getString(id++);
}
else{
id=0;
day=d;
return d+getString(id);
}
} private static String getString(int id){
//这里用id生成一个六位数(用StringBuffer)
}
}使用:
String s=Sequence.next();
每次用户登录时,调用到登录的 login 方法内,增加查找当前用户表登录序号的最大值
select max(dlxh) from t_user..............
找到最大值以后,该登录用户的 dlxh 就是刚查询到的 max 值加1,这是个方法,不过要注意数据库同步的问题,否则会出错.
dateid
date1 id
20041011 000000String sql = select max(id) from dateid where date1 = ?synchronized method
...if (rs.next()){
String did = curdate+String.valueOf(Integer.parseInt(rs.getString(1))+1);
}else{
String did = curdate+"000000";
}
---->
int i =Integer.parseInt(rs.getString(1))+1;
if(i>=100000){
String did = curdate+String.valueOf(i);
}else{
String t = String.valueOf(i);
StringBuffer sb = new StringBuffer(6);
for (int j =6; j > t.length; j--){
sb.append("0");}
sb.append(t);
}
}
需要插入或更新表
if (rs.next()){
int i =Integer.parseInt(rs.getString(1))+1;
String temp;
if(i>=100000){
temp = String.valueOf(i);}else{
String t = String.valueOf(i);
StringBuffer sb = new StringBuffer(6);
for (int j =6; j > t.length; j--){
sb.append("0");}
sb.append(t);
temp = sb.toString();
}
String did = curdate+temp;
}
---------------------
update dateid set id=? where date1=?;pstmt.setString(1,temp);
pstmt setString(2,curdate);
}else{
String did = curdate+"000000";
---------------------
insert into dateid (date1,id) values(?,?);pstmt.setString(1,curdate);
pstmt setString(2,"000000");excuteUpdate();
}