JSP调用SQL Server存储过程的实例
创建表
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"
"
<html xmlns="
<head>
</head>
<body>
<%
//注意下面的连接方法采用SQL Server的JDBC先下载sqlserver驱动
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 = cndivpareCall(sql);
javautilUUID Guid = javautilUUIDrandomUUID();
String FilePath = applicationgetRealPath("") + "testlogogif";
javaioFileInputStream f = new javaioFileInputStream(FilePath);
Date rightNow = DatevalueOf("");
cmdsetString("UserName""mengxianhui");
//注意修改这里存储过程验证了UserName的唯一性
cmdsetString("Title""孟宪会");
&nb
sp; cmdsetString("Guid"GuidtoString());
cmdsetString("BirthDate""");
cmdsetDate("BirthDate"rightNow);
cmdsetString("Description""【孟子E章】");
cmdsetBinaryStream("Photo"ffavailable());
cmdsetString("Other"null);
cmdregisterOutParameter(javasqlTypesINTEGER);
cmdregisterOutParameter("UserID"javasqlTypesINTEGER);
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>
