欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-06-15 15:53:18
(sun企业级应用的首选)调用sql server(windows平台上强大的平台)存储过程的实例: 创建表: create table [bookuser] ( &nb...

(sun企业级应用的首选)调用sql server(windows平台上强大的平台)存储过程的实例:


创建表:


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] prima(最完善的虚拟主机管理)ry key  clustered
    (
        [userid]
    )  on [prima(最完善的虚拟主机管理系统)ry]
) on [prima(最完善的虚拟主机管理系统)ry] textimage_on [prima(最完善的虚拟主机管理系统)ry]
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(sun企业级应用的首选)代码:


<%@ page language="java" contenttype="text/html; charset=utf-8" pageencoding="utf-8"%>
<%@ page import = "java.sql.*"%>
<!doctype html public "-//w3c//dtd xhtml 1.0 transitional//en"
"https://www.w3.org/tr/xhtml1/dtd/xhtml1-transitional.dtd">
<html xml(标准化越来越近了)ns="https://www.w3.org/1999/xhtml">
<head>
</head>
<body>
<%
//注意:下面的连接方法采用sql server(windows平台上强大的数据库平台)的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.pparecall(sql);
    java.util.uuid guid = java.util.uuid.randomuuid();
    string filepath = application.getrealpath("") + " estlogo.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("<li>添加成功!");
        out.print("<li>userid = " + userid);
        out.print("<li>returnvalue = " + returnvalue);
    }
    else
    {
        out.print("<li>添加失败!");
    }
    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();
    }
}
%>
</body>
</html>