Java call methods for stored procedures

Blog Category:

• Java

Java stored Procedure SQL

One: How Java implements calls to stored procedures:
A: Without an output parameter
-------------------------------------------------with no output parameters
CREATE PROCEDURE Getsum
@n int =0<--Here is the parameter--
As
Declare @sum int<--define variables--
DECLARE @i int
Set @sum =0
Set @i=0
While @i<[email protected] begin
Set @[email Protected][email protected]
Set @[email protected]+1
End
print ' The sum is ' +ltrim (RTrim (str (@sum)))

--------------executed in sql:--------------------
EXEC getsum 100

------------called in Java:---------------------
Java can be called but not in Java programs to display the results of the stored procedure because the storage above
The parameter type of the procedure int is passed in (by value) mode
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");

Object that created the stored procedure
CallableStatement C=conn.preparecall ("{Call Getsum (?)}");

Setting values for the parameters of a stored procedure
C.setint (1,100); Set the value of the first parameter to 100

Executing stored procedures
C.execute ();
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<[email protected] begin
Set @[email Protected][email protected]
Set @[email protected]+1
End
Set @[email protected]
-------------------execute------------in Query Analyzer
DECLARE @myResult int
exec getsum, @myResult output
Print @myResult

------------called in Java---------------------
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");

Object that created the stored procedure
CallableStatement C=conn.preparecall ("{Call Getsum (?,?)}");

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

Registering the second parameter of a stored procedure
C.registeroutparameter (2,java.sql.types.integer);

Executing stored procedures
C.execute ();

Get the output parameter value of the stored procedure
System.out.println (C.getint (2));
Conn.close ();
}
}
2: Return varchar
----------------stored procedure with cursors----------------
---Use cursors with cursors to traverse OrderID in a stored procedure without stopping
CREATE PROCEDURE Cursorintoprocedure
@pname varchar (8000) output
As
--Defining cursors
Declare cur cursor FOR SELECT OrderID from Orders
--Define a variable to receive the value of the cursor
DECLARE @v varchar (5)
--Open cursor
Open cur
Set @pname = "--Give @pname initial value
--Extract the value of the cursor
FETCH NEXT from cur to @v
While @ @fetch_status =0
Begin

Set @[email protected]+ '; ' [Email protected]
FETCH NEXT from cur to @v
End
Print @pname
--Close cursor
Close cur
--Destroying cursors
Deallocate cur

------------execute the stored procedure--------------
EXEC cursorintoprocedure '

--------------Java Call------------------
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");
CallableStatement C=conn.preparecall ("{Call Cursorintoprocedure (?)}");


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

C.execute ();

System.out.println (c.getstring (1));
Conn.close ();
}
}
C: Stored procedures for deleting data
------------------Stored Procedure--------------------------

DROP table Student Basic information table
CREATE TABLE student basic information Table
(
Stuid int primary KEY,
Stuname varchar (10) ,
stuaddress varchar
)
Insert into   Student basic Information table values (1, ' Sanmao ', ' Wuhan ')
Insert into    Student basic information table values (2, ' Sanmao ', ' Wuhan ')
CREATE TABLE Student score 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 score Table VALUES (1,99,100)
Insert into   Student score Table values (2,99,100)

--Create a stored procedure
CREATE PROCEDURE Delepro
@StuID int
As
Delete from Student basic information table where [email protected]
--Creation completed
EXEC Delepro 1--Execute stored procedure
--Create a stored procedure
CREATE PROCEDURE Selepro
As
SELECT * FROM student basic information Form
--Creation completed
EXEC selepro-Executing stored procedures
------------------called in Java----------------
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");

Object that created the stored procedure
CallableStatement C=conn.preparecall ("{Call Delepro (?)}");

C.setint (a);

C.execute ();

C=conn.preparecall ("{Call Selepro}");
ResultSet Rs=c.executequery ();

while (Rs.next ())
{
String stu=rs.getstring ("Stuid");
String name=rs.getstring ("Stuname");
String add=rs.getstring ("stuaddress");

System.out.println ("School Number:" + "+" Name: "+" + "address");
System.out.println (stu+ "" +name+ "" +add ");
}
C.close ();
}
}
D: Stored procedures for modifying data
---------------------Create a stored procedure---------------------
CREATE PROCEDURE Modpro
@StuID int,
@StuName varchar (10)
As
Update Student basic information table set [email protected] where [email protected]

-------------execute the stored procedure-------------------------
EXEC Modpro 2, ' four hairs '
---------------Java calls a stored procedure--------------------
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");

Object that created the stored procedure
CallableStatement C=conn.preparecall ("{Call Modpro (?,?)}");

(C.setint);
C.setstring (2, "Beauty");

C.execute ();

C=conn.preparecall ("{Call Selepro}");
ResultSet Rs=c.executequery ();

while (Rs.next ())
{
String stu=rs.getstring ("Stuid");
String name=rs.getstring ("Stuname");
String add=rs.getstring ("stuaddress");

System.out.println ("School Number:" + "+" Name: "+" + "address");
System.out.println (stu+ "" +name+ "" +add ");
}
C.close ();
}
}
E: Stored procedure for querying data (fuzzy query)
-----------------Stored Procedure---------------------
CREATE PROCEDURE Findcusts
@cust varchar (10)
As
Select CustomerID from Orders where CustomerID
Like '% ' [email protected]+ '% '
---------------Execution---------------------------
Execute findcusts ' ALFKI '
-------------called in Java--------------------------
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");

Object that created the stored procedure
CallableStatement C=conn.preparecall ("{Call Findcusts (?)}");
C.setstring (1, "Tom");

ResultSet Rs=c.executequery ();

while (Rs.next ())
{
String cust=rs.getstring ("CustomerID");
SYSTEM.OUT.PRINTLN (Cust);
}
C.close ();
}
}
F: Adding data to the stored procedure

------------stored procedure--------------------
CREATE PROCEDURE Insertpro
@StuID int,
@ Stuname varchar,
@StuAddress varchar
as
INSERT into Student basic information table values (@StuID, @StuName, @StuAddress)

-----------call a stored procedure---------------
EXEC Insertpro 5, ' 555 ', ' 555 '
-----------Execute-------------in Java
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");

Object that created the stored procedure
CallableStatement C=conn.preparecall ("{Call Insertpro (?,?,?)}");
C.setint (1,6);
C.setstring (2, "Liu");
C.setstring (3, "Wuhan");

C.execute ();

C=conn.preparecall ("{Call Selepro}");
ResultSet Rs=c.executequery ();

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 invoke it directly in Java
Import java.sql.*;
public class Proceduretest
{
public static void Main (String args[]) throws Exception
{
Load Driver
Drivermanager.registerdriver (New Sun.jdbc.odbc.JdbcOdbcDriver ());
Get Connected
Connection conn=drivermanager.getconnection ("Jdbc:odbc:mydata", "sa", "");


Statement stmt=conn.createstatement ();
Create a stored procedure in Java
Stmt.executeupdate ("CREATE PROCEDURE OOP as SELECT * FROM Student score table");


CallableStatement C=conn.preparecall ("{Call OOP}");

ResultSet Rs=c.executequery ();
while (Rs.next ())
{
String chinese=rs.getstring ("Chinese");

System.out.println (Chinese);
}
Conn.close ();

}
}

Java call methods for stored procedures