實例講解JSP調用SQL Server的存儲過程

　　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>

From:http://tw.wingwit.com/Article/program/Java/JSP/201311/20269.html