java调用SQL存储过程

//SQL存储过程

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

 

 

 

//java调用SQL存储过程

//注意：下面的连接方法采用最新的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("") + "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("＜li＞添加成功！");
　　out.print("＜li＞UserID = " + UserID);
　　out.print("＜li＞returnValue = " + returnValue);
　}
　else
　
　f.close();
}
catch(Exception ex)

finally
{
　try
　{
　　if(cmd != null)　
　　{
　　　cmd.close();
　　　cmd = null;
　　}
　　if(cn != null)
　　{
　　　cn.close();
　　　cn = null;
　　}
　}
　catch(Exception e)
　
}