Java/JSP中调用SQL Server存储过程完整示例

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

创建表：

CREATE TABLE [ BookUser ] (
[ UserID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ UserName ] [ varchar ] ( 50 )COLLATEChinese_PRC_CI_AS NOT NULL ,
[ Title ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NOT NULL ,
[ Guid ] [ uniqueidentifier ] NOT NULL CONSTRAINT [ DF_BookUser_Guid ] DEFAULT ( newid ()),
[ BirthDate ] [ datetime ] NOT NULL ,
[ Description ] [ ntext ] COLLATEChinese_PRC_CI_AS NOT NULL ,
[ Photo ] [ image ] NULL ,
[ Other ] [ varchar ] ( 50 )COLLATEChinese_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 ( 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代码：

<% @pagelanguage = " java " contentType = " text/html;charset=UTF-8 " pageEncoding = " UTF-8 " %>
<% @page import = " java.sql.* " %>
<! DOCTYPEhtmlPUBLIC " -//W3C//DTDXHTML1.0Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< htmlxmlns = " http://www.w3.org/1999/xhtml " >
< head >
</ head >
< body >
<%
// 注意：下面的连接方法采用最新的SQLServer的JDBC，
// 请到 http://msdn2.microsoft.com/zh-cn/data/aa937724.aspx 下载
Class.forName( " com.microsoft.sqlserver.jdbc.SQLServerDriver " );
Stringurl = " jdbc:sqlserver://localhost:1433;databaseName=Book;user=sa;password= " ;
Stringsql = " {?=callInsertUser(?,?,?,?,?,?,?,?)} " ;
Connectioncn = null ;
CallableStatementcmd = null ;
try
{
cn = DriverManager.getConnection(url);
cmd = cn.prepareCall(sql);
java.util.UUIDGuid = java.util.UUID.randomUUID();
StringFilePath = application.getRealPath( "" ) + " \test\logo.gif " ;
java.io.FileInputStreamf = new java.io.FileInputStream(FilePath);
DaterightNow = 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 (Exceptionex)
{
out.print(ex.getLocalizedMessage());
}
finally
{
try
{
if (cmd != null )
{
cmd.close();
cmd = null ;
}
if (cn != null )
{
cn.close();
cn = null ;
}
}
catch (Exceptione)
{
e.printStackTrace();
}
}
%>
</ body >
</ html >