Recently I made a small Java project and searched the internet for half a day. I found a better example of calling the stored procedure, and setXXX (int parameterIndex, XXX x. This form is not intuitive. The following is a complete compilation method using setXXX (String parameterName, XXX x. Create a data table and complete the stored procedure code.
Create a table:
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 (DEFAULT ),
CONSTRAINT [PK_BookUser] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
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" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<Html xmlns = "http://www.w3.org/1999/xhtml">
<Head>
</Head>
<Body>
<%
// Note: The following connection method uses the latest SQL Server JDBC,
// Download from 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 ("") + "estlogo.gif ";
Java. io. FileInputStream f = new java. io. FileInputStream (FilePath );
Date rightNow = Date. valueOf ("2007-9-9 ");
Cmd. setString ("UserName", "mengxianhui"); // note that the Stored Procedure verifies the uniqueness of UserName.
Cmd. setString ("Title", "Meng xianhui ");
Cmd. setString ("Guid", Guid. toString ());
Cmd. setString ("BirthDate", "2007-9-9 ");
Cmd. setDate ("BirthDate", rightNow );
Cmd. setString ("Description", "Chapter E of Mencius ]");
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.exe cute ();
Int returnValue = cmd. getInt (1 );
Int UserID = cmd. getInt ("UserID ");
If (returnValue = 1)
{
Out. print ("<li> added successfully! ");
Out. print ("<li> UserID =" + UserID );
Out. print ("<li> returnValue =" + returnValue );
}
Else
{
Out. print ("<li> failed to add! ");
}
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>
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
