在JSP页面自动生成带有日期和数字的序列号

[lss21633]

请问怎样才能在JSP页面自动生成带有日期和数字的序列号
例如:S20090323001
S20090323002
随日期不同,又会在001开始
我是用SQL 2005

[dawugui]

declare @rtn as varchar(12)
if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
   set @rtn = select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112)
else
   set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'

print @rtn

[dawugui]

create table tb(col varchar(12))
insert into tb values('S20090323001')
insert into tb values('S20090323002')
go

declare @rtn as varchar(12)
if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
   set @rtn = (select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
else
   set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'

print @rtn

drop table tb

/*
s20090323003
*/

--将系统时间更改为2009-03-24,再次运行.
create table tb(col varchar(12))
insert into tb values('S20090323001')
insert into tb values('S20090323002')
go

declare @rtn as varchar(12)
if exists(select 1 from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
   set @rtn = (select 's' + convert(varchar(8),getdate(),112) + right('000'+cast(cast(right(max(col),3) as int) + 1 as varchar),3) from tb where substring(col,2,8) = convert(varchar(8),getdate(),112))
else
   set @rtn = 's' + convert(varchar(8),getdate(),112) + '001'

print @rtn

drop table tb

/*
s20090324001
*/

[dawugui]

上面为SQL中的运行结果,至于JSP中如何获取这个结果就不知道了.帮顶.

[dawugui]

如果你想自动完成上面的东西,把相关代码放入触发器中.

[Arc365]

不一定非要用sql生成吧
自己可以写一个静态变量来保存后面的序列号，
写一个方法来构造这个字符串，根据日期的不同序列号每次从0开始，
static long sequence_=1;
static long last_date;
public String getString(){
if(last_date<>now_date){
sequence_=1;
}
//format 一下sequence_，如果不足三位前面补0
String returnValue="s"+now_date+sequence_;
sequence_++;
}
最后在jsp页面上调用这个方法就行了。。

[ks_reny]

幫頂,學習.

[yongyuanzhiyin]

学习，一起分享

[claro]

帮顶。

[lss21633]

我已在SQL2005 建立了一个student表
有以下几个属性：学号、姓名、性别、籍贯
就是想自动生成学号，如s20090324001、s20090324002之类，怎样插进呢？

[sdhdy]

create trigger trig_insert_students on student
for insert 
as
update a set 学号='s'+convert(varchar(8),getdate(),112)+right('000'+rtrim(isnull((select max(right(学号,3)) from  student where left(学号,9)='s'+convert(varchar(8),getdate(),112)),'000')+1),3)
from student a,  inserted  b 
where a.姓名=b.姓名 and a.性别=b.性别 and a.籍贯=b.籍贯

[sdhdy]

--只需要插入这3个值就可以了，在插入以后，学号自动生成。
insert student(姓名,性别,籍贯) values() 

[kye_jufei]

關注，幫頂.

[fcuandy]

create table tb(col varchar(12))
insert into tb values('S20090323001')
insert into tb values('S20090323002')
go



DECLARE @currentMaxV VARCHAR(12),@theValue VARCHAR(12)
SELECT @currentMaxV=MAX(col) FROM tb WHERE col LIKE 'S' + CONVERT(VARCHAR(10),GETDATE(),112) + '[0-9][0-9][0-9]'
SELECT @theValue = CASE WHEN @currentMaxV IS NULL THEN	'S' + CONVERT(VARCHAR(10),GETDATE(),112) + '001' ELSE LEFT(@currentMaxV,9) + RIGHT(1000 + RIGHT(@currentMaxV,3) * 1+1,3) END
SELECT @theValue
/*
S20090324001
*/

GO

DROP TABLE tb
GO

[MJunnnn]

[Quote=引用 8 楼 Arc365 的回复:]
不一定非要用sql生成吧
自己可以写一个静态变量来保存后面的序列号，
写一个方法来构造这个字符串，根据日期的不同序列号每次从0开始，
static long sequence_=1;
static long last_date;
public String getString(){
if(last_date <>now_date){
sequence_=1;
}
//format 一下sequence_，如果不足三位前面补0
String returnValue="s"+now_date+sequence_;
sequence_++;
}
最后在jsp页面…
[/Quote]
是的
<%
int year,month,day,hour,minute,second;
String appno,sy1="",sy2="",appno2;
GregorianCalendar calendar;
calendar = new GregorianCalendar();
year = calendar.get(Calendar.YEAR);
month = calendar.get(Calendar.MONTH)+1;
day = calendar.get(Calendar.DAY_OF_MONTH);
hour = calendar.get(Calendar.HOUR_OF_DAY);
minute = calendar.get(Calendar.MINUTE);
second = calendar.get(Calendar.SECOND);
if( month < 10)
{sy1 = "0"+month;}
else
{sy1 = ""+month;}
if( day <= 9)
{sy2 = "0"+day;}
else
{sy2 = ""+day;}
appno = "LR"+year +sy1+ sy2;
......省阅
strSQL = "SELECT count(*) as recordcount FROM rohsitem WHERE app_no like '"+appno+"%'";
rs = stmt.executeQuery(strSQL);
if (rs.next())
totalrecord = rs.getInt("recordcount");

if(totalrecord ==0)
appno2 = appno +"01";
else if(totalrecord <=9)
appno2 = appno +"0"+(totalrecord+1);
else
appno2 = appno +(totalrecord+1);
%>