实例解JSP调用SQLServer的存储过程.doc

实例讲解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 PRIMARYGO 创建存储过程： CREATE PROCEDURE InsertUserUserName varchar(50),Title varchar(255),Guid uniqueidentifier,BirthDate DateTime,Description ntext,Photo image,Other nvarchar(50),UserID int output As Set NOCOUNT ONIf Exists (select UserID from BookUser Where UserName = UserName)RETURN 0ELSEBeginINSERT INTO BookUser (UserName,Title,Guid,BirthDate,Description,Photo,Other) VALUES(UserName,Title,Guid,BirthDate,Description,Photo,Other)SET UserID = IDENTITYRETURN 1EndGO JSP代码： %/注意：下面的连接方法采用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 t