实例讲解JSP调用SQL Server的存储过程
(sun企业级应用的首选)调用sql server(windows平台上强大的平台)存储过程的实例：
创建表：
create table [bookuser] (
[userid] [int] identity (1, 1) not null ,
[username] [varchar] (50) collate chinese_prc_ci_as not null ,
[title] [nvarchar] (50) collate chinese_prc_ci_as not null ,
[guid] [uniqueidentifier] not null constraint [df_bookuser_guid] default (newid()),
[birthdate] [datetime] not null ,
[description] [ntext] collate chinese_prc_ci_as not null ,
[photo] [image] null ,
[other] [varchar] (50) collate chinese_prc_ci_as null
constraint [df_bookuser_other] default (默认值),
constraint [pk_bookuser] prima(最完善的虚拟主机管理)ry key clustered
(
[userid]
) on [prima(最完善的虚拟主机管理系统)ry]
) on [prima(最完善的虚拟主机管理系统)ry] textimage_on [prima(最完善的虚拟主机管理系统)ry]
go
创建存储过程：
create procedure insertuser
@username varchar(50),
@title varchar(255),
@guid uniqueidentifier,
@birthdate datetime,
@description ntext,
@photo image,
@other nvarchar(50),
@userid int output
as
set nocount on
if exists (select userid from bookuser where username = @username)
return 0
else
begin
insert into bookuser (username,title,guid,birthdate,description,photo,other)
values(@username,@title,@guid,@birthdate,@description,@photo,@other)
set @userid = @@identity
return 1
end
go
jsp(sun企业级应用的首选)代码：
<%@ page language="java" contenttype="text/html; charset=utf-8" pageencoding="utf-8"%>
<%@ page import = "java.sql.*"%>
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en"
"https://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xml(标准化越来越近了)ns="https://www.w3.org/1999/xhtml">
<head>
</head>
<body>
<%
//注意：下面的连接方法采用sql server(windows平台上强大的数据库平台)的jdbc，先下载sqlserver驱动。
class.forname("com.microsoft.sqlserver.jdbc.sqlserverdriver");
string url="jdbc:sqlserver://localhost:1433;databasename=book;user=sa;password=";
string sql = "{? = call insertuser(?,?,?,?,?,?,?,?)}";
connection cn = null;
callablestatement cmd = null;
try
{
cn = drivermanager.getconnection(url);
cmd = cn.pparecall(sql);
java.util.uuid guid = java.util.uuid.randomuuid();
string filepath = application.getrealpath("") + " estlogo.gif";
java.io.fileinputstream f = new java.io.fileinputstream(filepath);
date rightnow = date.valueof("2007-9-9");
cmd.setstring("username","mengxianhui");
//注意修改这里，存储过程验证了username的唯一性。
cmd.setstring("title","孟宪会");
cmd.setstring("guid",guid.tostring());
cmd.setstring("birthdate","2007-9-9");
cmd.setdate("birthdate",rightnow);
cmd.setstring("description","【孟子e章】");
cmd.setbinarystream("photo",f,f.available());
cmd.setstring("other",null);
cmd.registeroutparameter(1,java.sql.types.integer);
cmd.registeroutparameter("userid",java.sql.types.integer);
cmd.execute();
int returnvalue = cmd.getint(1);
int userid = cmd.getint("userid");
if(returnvalue == 1)
{
out.print("<li>添加成功！");
out.print("<li>userid = " + userid);
out.print("<li>returnvalue = " + returnvalue);
}
else
{
out.print("<li>添加失败！");
}
f.close();
}
catch(exception ex)
{
out.print(ex.getlocalizedmessage());
}
finally
{
try
{
if(cmd != null)
{
cmd.close();
cmd = null;
}
if(cn != null)
{
cn.close();
cn = null;
}
}
catch(exception e)
{
e.printstacktrace();
}
}
%>
</body>
</html>