熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Java編程 >> JSP教程 >> 正文

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

2013-11-15 12:05:39  來源: JSP教程 

  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
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.