jsp如何调用mssql 存储过程

本文章来告诉你jsp教程如何调用mssql 存储过程吧，其实调用mssql的存储过程很简单的，下面我们先来创建表：

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

<%@ page language="java" contenttype="text/html; charset=utf-8" pageencoding="utf-8"%>
<%@ page import = "java.sql.*"%>
"http://www.111com.net/tr/xhtml1/dtd/xhtml1-transitional.dtd">
http://www.111com.net/1999/xhtml">



<%
//注意：下面的连接方法采用sql server的jdbc，先下载sqlserver驱动。
class.forname("com.microsoft.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 = drivermanager.getconnection(url);
    cmd = cn.divparecall(sql);
    java.util.uuid guid = java.util.uuid.randomuuid();
    string filepath = application.getrealpath("") + "testlogo.gif";
    java.io.fileinputstream f = new java.io.fileinputstream(filepath);
    date rightnow = 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("

添加成功！");
        out.print("

userid = " + userid);
        out.print("

returnvalue = " + returnvalue);
    }
    else
    {
        out.print("

添加失败！");
    }
    f.close();
}
catch(exception ex)
{
    out.print(ex.getlocalizedmessage());
}
finally
{
    try
    {
        if(cmd != null)
        {
            cmd.close();
            cmd = null;
        }
        if(cn != null)
        {
            cn.close();
            cn = null;
        }
    }
    catch(exception e)
    {
        e.printstacktrace();
    }
}
%>