Java中调用SQL Server存储过程示例

       最近做了个Java的小项目（第一次写Java的项目哦）， 到网上搜索了半天，找到了一个比较好点的调用存储过程的例子，而且网上普遍采用的都是setXXX((int parameterIndex, XXX x)的形式。这种形式感觉不是很直观，下面就发布一个完整的采用setXXX(String parameterName, XXX x)的编写方法。创建数据表，存储过程的代码都完整发布。 {)?a+C@&  
aj< 'P<To<  
创建表： ah_Qc$  
#e#0J+  
CREATE TABLE [BookUser] ( |F%{Z "  
    [UserID] [int] IDENTITY (1, 1) NOT NULL , H`_p)=2  
    [UserName] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , s2H ~2$h3  
    [Title] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , !].rWjw  
    [Guid] [uniqueidentifier] NOT NULL CONSTRAINT [DF_BookUser_Guid] DEFAULT (newid()), kDANfyASt  
    [BirthDate] [datetime] NOT NULL , >ex0kxOs8  
    [Description] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL , 0)+'FY#  
    [Photo] [image] NULL , VA)NZ0{J  
    [Other] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL CONSTRAINT G_#691uL  
          [DF_BookUser_Other] DEFAULT ('默认值'), ))\V&N2m  
    CONSTRAINT [PK_BookUser] PRIMARY KEY  CLUSTERED &~W':M  
    ( R;?vn6  
        [UserID] gs>qCDp  
    )  ON [PRIMARY] %6ia\b4  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] <kVq}txH  
GO Z[\ xQu  
8pyd3@  
z<%2 (%}&  
创建存储过程： By^m,^njf  
*-=>[rZRfq  
CREATE PROCEDURE InsertUser hv*qEJ[k-  
@UserName varchar(50), 0`4dYNG[L  
@Title varchar(255), S1(;Jxdk  
@Guid  uniqueidentifier, (\q5NOG  
@BirthDate DateTime, a=ItJ  
@Description ntext, JZ#{6v  
@Photo image, eT+j_F)  
@Other nvarchar(50), #(!3.4"  
@UserID int output [L 4I_d  
As =iH^@9O  
c))o@q  
Set NOCOUNT ON " tL;"EI\  
If Exists (select UserID from BookUser Where UserName = @UserName) s6Ajm  
RETURN 0 WiNx  
ELSE kluPI  
Begin e6>Jx9,sZ  
INSERT INTO BookUser (UserName,Title,Guid,BirthDate,Description,Photo,Other) VALUES(@UserName,@Title,@Guid,@BirthDate,@Description,@Photo,@Other) !oc"mmCc  
SET @UserID = @@IDENTITY ur62:HuG  
RETURN 1 -lBB}g  
End dSAhZXW  
GO 'gv.n  
>3R_ZN\  
NoniQG>x  
JSP代码： lHU 0"xwL  
+.5  
＜%@ page language="java" contentType="text/html; charset=UTF-8" pageEncoding="UTF-8"%＞ v wbNAKc  
＜%@ page import = "java.sql.*"%＞ s*Y(UQ-K  
＜!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"＞ C*8 I1j @N  #p#分页标题#e#
＜html xmlns="http://www.w3.org/1999/xhtml"＞ *x7 K|v<&  
＜head＞ Z,DE,+1K  
＜/head＞ hk4-@JQ@  
＜body＞ 54_9#YK*  
＜% ~)%)v-t  
//注意：下面的连接方法采用最新的SQL Server的JDBC， ")q IQc  
//请到 http://msdn2.microsoft.com/zh-cn/data/aa937724.aspx 下载 ;(} R<{'$  
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver"); \4b\g,_E[  
String url="jdbc:sqlserver://localhost:1433;databaseName=Book;user=sa;password="; i&!2nKcaw  
String sql = "{? = call InsertUser(?,?,?,?,?,?,?,?)}"; B(y}`m  
Connection cn = null; Jv}5b>H  
CallableStatement cmd = null; >Hhea$  
try ym9Qc p  
{ x-[=,EB\H  
cn = DriverManager.getConnection(url); qF@L47Qh  
cmd = cn.prepareCall(sql); Z1 GG9:c  
java.util.UUID Guid = java.util.UUID.randomUUID(); r3@^XZ  
String FilePath = application.getRealPath("") + "\test\logo.gif"; Vm({9xc1  
java.io.FileInputStream f = new java.io.FileInputStream(FilePath); 5v=C.wY3  
Date rightNow = Date.valueOf("2007-9-9"); h[ Lpborq  
cmd.setString("UserName","mengxianhui"); //注意修改这里，存储过程验证了UserName的唯一性。 * 3{x$y1  
cmd.setString("Title","孟宪会"); ^ lK n  
cmd.setString("Guid",Guid.toString()); E*<YCZ  
cmd.setString("BirthDate","2007-9-9"); ba 4~\Y/  
cmd.setDate("BirthDate",rightNow); ?nat%86  
cmd.setString("Description","【孟子E章】"); PK!+tDw _  
cmd.setBinaryStream("Photo",f,f.available()); ~\}p_;D  
cmd.setString("Other",null); %L<|.W  
cmd.registerOutParameter(1,java.sql.Types.INTEGER); 5Pf2(XUh  
cmd.registerOutParameter("UserID",java.sql.Types.INTEGER); /f<\#P$  
cmd.execute(); |hl&pF-xy  
int returnValue = cmd.getInt(1); =n<Tf  
int UserID = cmd.getInt("UserID"); 'huFu"3u-  
if(returnValue == 1) Rl<Ae|  
{ -Bvj8z  
out.print("＜li＞添加成功！"); u]Hl1"V  
out.print("＜li＞UserID = " + UserID); US+zZ  
out.print("＜li＞returnValue = " + returnValue); f@&xbHDCm  
} )Fx+fPYm  
else ?#ur\+  
{ +&T5?~F  
out.print("＜li＞添加失败！"); \||ymcH^k  
} a(D~`*'2)8  
f.close(); 4W ,^i`o  
} WU! $!  
catch(Exception ex) oaGAAT  
{ #K"F(&1IK  
out.print(ex.getLocalizedMessage()); Tt@(NH  
} ^=iSf>4  
finally B-k$h120  
{ #}VPf^A)  
try `.AK[  
{ qM"={C  
if(cmd != null) ][ /W,;l  
{#p#分页标题#e# z+a[}> J?  
cmd.close(); p:DF|`FhP  
cmd = null; >Htr;!p  
} dR^ +TDY  
if(cn != null) Ak.}+E  
{ vnS @&(  
cn.close(); #T}h:i_  
cn = null; "n6yv@.  
} /(uJ =E  
} =Dv k-{l  
catch(Exception e) <:Xa-  
{ qpCaZ  
e.printStackTrace(); `4J>tBP>t  
} z\: o_c  
} &'Lwlh+<x  
%＞ tv77+\!:  
＜/body＞ #P@JRxF@&V  
＜/html＞ <[l\(RR`