最近做了个Java的小项目(第一次写Java的项目哦), 到网上搜索了半天,找到了一个比较好点的调用存储过程的例子,而且网上普遍采用的都是setXXX((int parameterIndex, XXX x)的形式。这种形式感觉不是很直观,下面就发布一个完整的采用setXXX(String parameterName, XXX x)的编写方法。创建数据表,存储过程的代码都完整发布。
创建表:
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
[ 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
@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代码:
<%
@ 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 " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head >
</ head >
< body >
<%
// 注意:下面的连接方法采用最新的SQL Server的JDBC,
// 请到 http://msdn2.microsoft.com/zh-cn/data/aa937724.aspx 下载
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.prepareCall(sql);
java.util.UUID Guid = java.util.UUID.randomUUID();
String FilePath = application.getRealPath( "" ) + " /test/logo.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 >
<% @ page import = " java.sql.* " %>
<! DOCTYPE html PUBLIC " -//W3C//DTD XHTML 1.0 Transitional//EN " " http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd " >
< html xmlns = " http://www.w3.org/1999/xhtml " >
< head >
</ head >
< body >
<%
// 注意:下面的连接方法采用最新的SQL Server的JDBC,
// 请到 http://msdn2.microsoft.com/zh-cn/data/aa937724.aspx 下载
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.prepareCall(sql);
java.util.UUID Guid = java.util.UUID.randomUUID();
String FilePath = application.getRealPath( "" ) + " /test/logo.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 >