实例讲解JSP调用SQL Server的存储过程

JSP调用SQL Server存储过程的实例：

创建表：

CREATE TABLE [BookUser] (
    [UserID] [int] IDENTITY (1, 1) NOT NULL ,
    [UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
    [Title] [nvarchar] (50) 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] (50) 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(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代码：

<%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%>
<%@ page import = "java.sql.*"%>
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
http://ww***.org/1999/xhtml">http://ww***.org/1999/xhtml">



<%
//注意：下面的连接方法采用SQL Server的JDBC，先下载sqlserver驱动。
Cl***.forName("co***icrosoft.sqlserver.jdbc.SQLServerDriver");
String url="jdbc:sqlserver://localhost:1433;databaseName=Book;user=sa;password=";
String sql = "{? = call InsertUser(?,?,?,?,?,?,?,?)}";
Connection cn = null;
CallableStatement cmd = null;
try
{
    cn = Dr***rManager.getConnection(url);
    cmd = cn***vpareCall(sql);
    ja***util.UUID Guid = ja***util.UUID.randomUUID();
    String FilePath = ap***cation.getRealPath("") + "testlogo.gif";
    ja***io.FileInputStream f = new ja***io.FileInputStream(FilePath);
    Date rightNow = Da***valueOf("2007-9-9");
    cm***etString("UserName","mengxianhui"); 
    //注意修改这里，存储过程验证了UserName的唯一性。
    cm***etString("Title","孟宪会");
    cm***etString("Guid",Gu***toString());
    cm***etString("BirthDate","2007-9-9");
    cm***etDate("BirthDate",rightNow);
    cm***etString("Description","【孟子E章】");
    cm***etBinaryStream("Photo",f,f.***ilable());
    cm***etString("Other",null);
    cm***egisterOutParameter(1,ja***sql.Types.INTEGER);
    cm***egisterOutParameter("UserID",ja***sql.Types.INTEGER);
    cm***xecute();
    int returnValue = cm***etInt(1);
    int UserID = cm***etInt("UserID");
    if(returnValue == 1)
    {
        ou***rint("