Home > Developer > SQL Server
JSP calls an instance of a SQL Server stored procedure:
To create a table:
CREATE TABLE [Bookuser] (
[UserID] [int] IDENTITY (1, 1) not NULL,
[UserName] [varchar] (a) COLLATE chinese_prc_ci_as not NULL,
[Title] [nvarchar] (m) COLLATE chinese_prc_ci_as not NUL L,
[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] (m) COLLATE Chinese_prc_ci _as null 
CONSTRAINT [df_bookuser_other] Default (' Default value '),
CONSTRAINT [Pk_bookuser] PRIMARY key clustered
(
[UserID]
) on [Primary]
) on [PRIMARY] textimage_on [PRIMARY]
Go
To create a stored procedure:
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
JSP Code:
<%@ 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" &NBSP;
" Http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd ""
<body>
<%
//NOTE: The following connection method uses JDBC from SQL Server, and downloads the server driver first.
Class.forName ("Com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url= "jdbc:sqlserver://localhost:1433;databasename=book;user=sa;password=";
String sql =" {? = Call Insertuser (?,?,?,?,?,?,?,?)} "; &NBSP;
Connection cn = NULL;
CallableStatement cmd = null;
Try
{
cn = drivermanager.getconnection (URL);
cmd = Cn.divparecall (SQL);
&NBSP; 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"); &NBSP;
/Note modifications here, the stored procedure validates the uniqueness of the username.
cmd.setstring ("Title", "Mengxian");
&nb
sp; Cmd.setstring ("Guid", guid.tostring ());
cmd.setstring ("Birthdate", "2007-9-9");
cmd.setdate ("Birthdate", RightNow);
cmd.setstring ("Description", "" Mencius e chapter ");
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> Add success!" ");
out.print ("<li>userid =" + UserID);
out.print ("<li>returnvalue =" + returnvalue);
}
Else
{
Out.print ("<li> add failed!") ");
}
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>
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
