Java/JSP中调用SQL Server存储过程完整示例-java-IT落伍者

最近做了个Java的小项目（第一次写Java的项目）到网上搜索了半天找到了一个比较好点的调用存储过程的例子而且网上普遍采用的都是setXXX((int parameterIndex XXX x)的形式这种形式感觉不是很直观下面就发布一个完整的采用setXXX(String parameterName XXX x)的编写方法创建数据表存储过程的代码都完整发布

创建表

CREATE TABLE [BookUser] (
[UserID] [int] IDENTITY ( ) NOT NULL 
[UserName] [varchar] () COLLATE Chinese_PRC_CI_AS NOT NULL 
[Title] [nvarchar] () 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] () COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT [DF_BookUser_Other] 
       DEFAULT (默认值)
CONSTRAINT [PK_BookUser] PRIMARY KEY CLUSTERED 
(
[UserID]
) ON [PRIMARY] 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

创建存储过程

CREATE PROCEDURE InsertUser
@UserName varchar()
@Title varchar()
@Guid uniqueidentifier
@BirthDate DateTime
@Description ntext
@Photo image
@Other nvarchar()
@UserID int output 
As
Set NOCOUNT ON
If Exists (select UserID from BookUser Where UserName = @UserName)
RETURN 
ELSE
Begin
INSERT INTO BookUser (UserNameTitleGuidBirthDateDescriptionPhotoOther) 
VALUES(@UserName@Title@Guid@BirthDate@Description@Photo@Other)
SET @UserID = @@IDENTITY
RETURN 
End
GO

JSP代码

<%@ page language=java contentType=text/html; charset=UTF

   pageEncoding=UTF%>
<%@ page import = javasql*%>
<!DOCTYPE html PUBLIC //WC//DTD XHTML  Transitional//EN

   http://wwwworg/TR/xhtml/DTD/xhtmltransitionaldtd>
<html xmlns=http://wwwworg//xhtml>
<head>
</head>
<body>
<%
//注意下面的连接方法采用最新的SQL Server的JDBC
//请到 http://msdnmicrosoftcom/zhcn/data/aaaspx 下载
ClassforName(commicrosoftsqlserverjdbcSQLServerDriver);
String url=jdbc:sqlserver://localhost:;databaseName=Book;user=sa;password=; 
String sql = {? = call InsertUser(????????)}; 
Connection cn = null;
CallableStatement cmd = null;
try
{
cn = DriverManagergetConnection(url);
cmd = cnprepareCall(sql);
javautilUUID Guid = javautilUUIDrandomUUID();
String FilePath = applicationgetRealPath() + \test\logogif;
javaioFileInputStream f = new javaioFileInputStream(FilePath); 
Date rightNow = DatevalueOf();
cmdsetString(UserNamemengxianhui);

      //注意修改这里存储过程验证了UserName的唯一性
cmdsetString(Title孟宪会);
cmdsetString(GuidGuidtoString());
cmdsetString(BirthDate);
cmdsetDate(BirthDaterightNow);
cmdsetString(Description【孟子E章】);
cmdsetBinaryStream(Photoffavailable());
cmdsetString(Othernull);
cmdregisterOutParameter(javasqlTypesINTEGER);
cmdregisterOutParameter(UserIDjavasqlTypesINTEGER);
cmdexecute();
int returnValue = cmdgetInt();
int UserID = cmdgetInt(UserID);
if(returnValue == )
{
outprint(<li>添加成功！);
outprint(<li>UserID =  + UserID);
outprint(<li>returnValue =  + returnValue);
}
else
{
outprint(<li>添加失败！);
}
fclose();
}
catch(Exception ex)
{
outprint(exgetLocalizedMessage());
}
finally
{
try
{
if(cmd != null)
{
cmdclose();
cmd = null;
}
if(cn != null)
{
cnclose();
cn = null;
}
}
catch(Exception e)
{
eprintStackTrace();
}
}
%>
</body>
</html>