Java method for calling stored procedures

I. How does Java call stored procedures:
A: Without Output Parameters
--------------- ---------------------------------------- Without Output Parameters ----------------------------------
Create procedure getsum
@ N Int = 0 <-- here is the parameter -->
As
Declare @ sum int <-- defines the variable -->
Declare @ I int
Set @ sum = 0
Set @ I = 0
While @ I <= @ n begin
Set @ sum = @ sum + @ I
Set @ I = @ I + 1
End
Print 'the sum is '+ ltrim (rtrim (STR (@ sum )))

-------------- Run: ---------------------- in SQL :--------------------
Exec getsum 100

------------ Called in Java :---------------------
Java can be called, but the results of the stored procedure cannot be displayed in the Java program.
The Int transmission mode of the process parameter type is in (by value ).
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");

// Create a stored procedure object
Callablestatement c = conn. preparecall ("{call getsum (?)} ");

// Set the parameter value for the stored procedure
C. setint (1,100); // set the value of the first parameter to 100

// Execute the Stored Procedure
C.exe cute ();
Conn. Close ();
}
}

B: With output parameters
1: Return int
------------------------- -------------- With output parameters ----------------
Alter procedure getsum
@ N Int = 0,
@ Result int output
As
Declare @ sum int
Declare @ I int
Set @ sum = 0
Set @ I = 0
While @ I <= @ n begin
Set @ sum = @ sum + @ I
Set @ I = @ I + 1
End
Set @ result = @ sum
------------------- Execute ------------ in the query Analyzer ------------
Declare @ myresult int
Exec getsum100, @ myresult output
Print @ myresult

------------ Calling ----------------------- in Java ---------------------
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");

// Create a stored procedure object
Callablestatement c = conn. preparecall ("{call getsum (?,?)} ");

// Set the value of the first parameter of the stored procedure
C. setint (1,100 );

// Register the second parameter of the stored procedure
C. registeroutparameter (2, java. SQL. types. integer );

// Execute the Stored Procedure
C.exe cute ();

// Obtain the output parameter value of the stored procedure
System. Out. println (C. getint (2 ));
Conn. Close ();
}
}
2: varchar is returned.
---------------- Stored procedure with cursor ----------------
--- Use the cursor to traverse orderid without stopping in the Stored Procedure
Create procedure cursorintoprocedure
@ Pname varchar (8000) Output
As
-- Define a cursor
Declare cur cursor for select orderid from orders
-- Define a variable to receive the cursor Value
Declare @ v varchar (5)
-- Open the cursor
Open cur
Set @ pname = ''-- give @ pname the Initial Value
-- Extract the cursor Value
Fetch next from cur into @ v
While @ fetch_status = 0
Begin

Set @ pname = @ pname + ';' + @ v
Fetch next from cur into @ v
End
Print @ pname
-- Close the cursor
Close cur
-- Destroy the cursor
Deallocate cur

------------ Execute the Stored Procedure --------------
Exec cursorrentprocedure''

-------------- Java call ------------------
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");
Callablestatement c = conn. preparecall ("{call cursorintoprocedure (?)} ");


C. registeroutparameter (1, java. SQL. types. varchar );

C.exe cute ();

System. Out. println (C. getstring (1 ));
Conn. Close ();
}
}
C: stored procedure for data deletion
------------------ Stored Procedure --------------------------

Drop table student basic info table
Create Table student basic info table
(
Stuid int primary key,
Stuname varchar (10 ),
Stuaddress varchar (20)
)
Insert into student basic information table values (1, 'san Mao ', 'wuhan ')
Insert into student basic information table values (2, 'san Mao ', 'wuhan ')
Create Table student orders table
(
Stuid int,
Chinese int,
Pyhsics int
Foreign key (stuid) References student basic information table (stuid)
On Delete Cascade
On update Cascade
)
Insert into student values table values (100)
Insert into student values table values (2, 99, 100)

-- Create a stored procedure
Create procedure delepro
@ Stuid int
As
Delete from student basic info table where stuid = @ stuid
-- Creation complete
Exec delepro 1 -- execute the Stored Procedure
-- Create a stored procedure
Create procedure selepro
As
Select * from student basic info table
-- Creation complete
Exec selepro -- execute the Stored Procedure
------------------ Call ---------------- in Java ----------------
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");

// Create a stored procedure object
Callablestatement c = conn. preparecall ("{call delepro (?)} ");

C. setint (1, 1 );

C.exe cute ();

C = conn. preparecall ("{call selepro }");
Resultset rs=c.exe cutequery ();

While (Rs. Next ())
{
String Stu = Rs. getstring ("stuid ");
String name = Rs. getstring ("stuname ");
String add = Rs. getstring ("stuaddress ");

System. Out. println ("student ID:" + "" + "name:" + "" + "Address ");
System. Out. println (STU + "" + name + "" + Add );
}
C. Close ();
}
}
D: Modify the data stored procedure
------------------- Create a stored procedure ---------------------
Create procedure modpro
@ Stuid int,
@ Stuname varchar (10)
As
Update basic information table for students set stuname = @ stuname where stuid = @ stuid

------------- Execute the Stored Procedure -------------------------
Exec modpro 2, 'four hairs'
--------------- Java call the Stored Procedure --------------------
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");

// Create a stored procedure object
Callablestatement c = conn. preparecall ("{call modpro (?,?)} ");

C. setint (1, 2 );
C. setstring (2, "beauty ");

C.exe cute ();

C = conn. preparecall ("{call selepro }");
Resultset rs=c.exe cutequery ();

While (Rs. Next ())
{
String Stu = Rs. getstring ("stuid ");
String name = Rs. getstring ("stuname ");
String add = Rs. getstring ("stuaddress ");

System. Out. println ("student ID:" + "" + "name:" + "" + "Address ");
System. Out. println (STU + "" + name + "" + Add );
}
C. Close ();
}
}
E: data storage process (fuzzy query)
----------------- Stored Procedure ---------------------
Create procedure findcusts
@ Cust varchar (10)
As
Select customerid from orders where customerid
Like '%' + @ Cust + '%'
--------------- Run ---------------------------
Execute findcusts 'alfki'
------------- Call ---------------------------- in Java --------------------------
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");

// Create a stored procedure object
Callablestatement c = conn. preparecall ("{call findcusts (?)} ");
C. setstring (1, "Tom ");

Resultset rs=c.exe cutequery ();

While (Rs. Next ())
{
String Cust = Rs. getstring ("customerid ");
System. Out. println (Cust );
}
C. Close ();
}
}
F: add data storage process

------------ Stored Procedure --------------------
Create procedure insertpro
@ Stuid int,
@ Stuname varchar (10 ),
@ Stuaddress varchar (20)
As
Insert into student basic information table values (@ stuid, @ stuname, @ stuaddress)

----------- Call the Stored Procedure ---------------
Exec insertpro 5, '20140901', '20160901'
----------- Execute ------------- in Java -------------
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");

// Create a stored procedure object
Callablestatement c = conn. preparecall ("{call insertpro (?,?,?)} ");
C. setint (1, 6 );
C. setstring (2, "Liu ");
C. setstring (3, "Wuhan ");

C.exe cute ();

C = conn. preparecall ("{call selepro }");
Resultset rs=c.exe cutequery ();

While (Rs. Next ())
{
String stuid = Rs. getstring ("stuid ");
String name = Rs. getstring ("stuname ");
String address = Rs. getstring ("stuaddress ");
System. Out. println (stuid + "" + name + "" + address );
}
C. Close ();
}
}

G: Create a stored procedure in Java and call it directly in Java
Import java. SQL .*;
Public class proceduretest
{
Public static void main (string ARGs []) throws exception
{
// Load the driver
Drivermanager. registerdriver (new sun. JDBC. ODBC. jdbcodbcdriver ());
// Obtain the connection
Connection conn = drivermanager. getconnection ("JDBC: ODBC: mydata", "sa ","");


Statement stmt = conn. createstatement ();
// Create a stored procedure in Java
Stmt.exe cuteupdate ("create procedure OOP as select * from student orders table ");


Callablestatement c = conn. preparecall ("{call OOP }");

Resultset rs=c.exe cutequery ();
While (Rs. Next ())
{
String Chinese = Rs. getstring ("Chinese ");

System. Out. println (Chinese );
}
Conn. Close ();

}
}