JSP调用中SQL Server的存储过程
JSP调用SQLServer存储过程的实例：
创建表：
CREATETABLE[BookUser](
[UserID][int]IDENTITY(1,1)NOTNULL,
[UserName][varchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[Title][nvarchar](50)COLLATEChinese_PRC_CI_ASNOTNULL,
[Guid][uniqueidentifier]NOTNULLCONSTRAINT[DF_BookUser_Guid]DEFAULT(newid()),
[BirthDate][datetime]NOTNULL,
[Description][ntext]COLLATEChinese_PRC_CI_ASNOTNULL,
[Photo][image]NULL,
[Other][varchar](50)COLLATEChinese_PRC_CI_ASNULL
CONSTRAINT[DF_BookUser_Other]DEFAULT('默认值'),
CONSTRAINT[PK_BookUser]PRIMARYKEYCLUSTERED
(
[UserID]
)ON[PRIMARY]
)ON[PRIMARY]TEXTIMAGE_ON[PRIMARY]
GO
创建存储过程：
CREATEPROCEDUREInsertUser
@UserNamevarchar(50),
@Titlevarchar(255),
@Guiduniqueidentifier,
@BirthDateDateTime,
@Descriptionntext,
@Photoimage,
@Othernvarchar(50),
@UserIDintoutput
As
SetNOCOUNTON
IfExists(selectUserIDfromBookUserWhereUserName=@UserName)
RETURN0
ELSE
Begin
INSERTINTOBookUser(UserName,Title,Guid,BirthDate,Description,Photo,Other)
VALUES(@UserName,@Title,@Guid,@BirthDate,@Description,@Photo,@Other)
SET@UserID=@@IDENTITY
RETURN1
End
GO
JSP代码：
<%@pagelanguage="java"contentType="text/html;charset=UTF-8"pageEncoding="UTF-8"%>
<%@pageimport="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，先下载sqlserver驱动。
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.divpareCall(sql);
java.util.UUIDGuid=java.util.UUID.randomUUID();
StringFilePath=application.getRealPath("")+"\test\logo.gif";
java.io.FileInputStreamf=newjava.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();
intreturnValue=cmd.getInt(1);
intUserID=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>