------------------------------------------------------創建表
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 Title = @Title)--這一句判斷數據庫某一個字段的唯一性
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
------------------------------------查詢分析器中測試
exec InsertUser @UserName='111',@Title='測試1',@Guid='69807bfa-45a1-4f27-a21e-8b90025bff05',@BirthDate='2009-04-01',@Description='4455',@Photo=null,
@Other=null,@UserID='123'
--------------------------------------------頁面調用
<%@ page language="java" import="Java.util.*" pageEncoding="gbk"%>
<%@ page import="Java.sql.*"%>
<%@page import="Java.sql.Date;"%>
<%
String path = request.getContextPath();
String basePath = request.getScheme()+"://"+request.getServerName()+":"+request.getServerPort()+path+"/";
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD Html 4.01 Transitional//EN">
<Html>
<head>
<base href="<%=basePath%>">
<title>My JSP 'MyJSp.JSP' starting page</title>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta http-equiv="expires" content="0">
<meta http-equiv="keywords" content="keyword1,keyword2,keyWord3">
<meta http-equiv="description" content="This is my page">
<!--
<link rel="stylesheet" type="text/css" href="styles.CSS">
-->
</head>
<body>
<%
//注意：下面的連接方法采用最新的SQL Server的JDBC，
//請到 http://msdn2.microsoft.com/zh-cn/data/aa937724.ASPx 下載
Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
String url="jdbc:microsoft:sqlserver://localhost:1433;DatabaseName=owend;user=sa;passWord=";
String sql = "{? = call InsertUser(?,?,?,?,?,?,?,?)}";
Connection cn = null;
CallableStatement cmd = null;
String query = "select * from BookUser";
try
{
cn = DriverManager.getConnection(url);
cmd = cn.prepareCall(sql);
UUID Guid = UUID.randomUUID();
String FilePath = application.getRealPath("") + "/test/logo.gif";
Date rightNow =Date.valueOf("2009-07-04");
java.io.FileInputStream f = new Java.io.FileInputStream(FilePath);
cmd.setString(1,"mengxianhui"); //注意修改這裡，存儲過程驗證了UserName的唯一性。
cmd.setString(2,"孟憲會2");
cmd.setString(3,"15");
//cmd.setString("Guid","123");
cmd.setString(4,Guid.toString());
cmd.setDate(5,rightNow);
cmd.setString(6,"【孟子E章】");
cmd.setBinaryStream(7,f,f.available());
cmd.setString(8,"555");
cmd.registerOutParameter(9,Java.sql.Types.INTEGER);
//cmd.registerOutParameter(1,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>
