Two Methods for Java to call stored procedures

Creat proc proc_select
@ PID varchar (20)
@ Address varchar (20) Output
As
Select @ address = address from userinfo where pid = @ PID
Go

Use Java to call:
Class. forname (/"Sun. JDBC. ODBC. jdbcodbcdriver/"); // load the driver
Connection con = drivermanager. getconnection (/"JDBC: ODBC: Test/",/"sa/",/"/"); // get the connection
String call =/"{call proc_select (?,?)}; /"// CALL statement
Callablestatement proc = con. preparecall (CALL); // call a stored procedure
Proc. setstring (1,/"12345678/"); // pass the value to the input parameter
Proc. registeroutparameter (2, type. varchar); // declare the type of the output parameter
Proc.exe cute (); // Execute
String address = Proc. getstring (2); // obtain the output parameter

Java calls Stored Procedure 2

Stored Procedures can return values, so the callablestatement class has methods like getresultset to obtain the returned values. When a stored procedure returns a value, you must use the registeroutparameter method to tell the JDBC driver what the SQL type of the value is. You must also adjust the stored procedure call to indicate that the process returns a value.
The following is an example. This time, we will query the age of Dylan Thomas when he died. This stored procedure uses PostgreSQL's PL/pgsql:
Create Function snuffed_it_when (varchar) returns integer 'declare
Poet_id number;
Poet_age number;
Begin
-- First get the ID associated with the poet.
Select ID into poet_id from poets where name = $1;
-- Get and return the age.
Select age into poet_age from deaths where mort_id = poet_id; [Page]
Return age;
End; 'language' PL/pgsql ';
In addition, note that PL/pgsql parameter names are referenced using the $ n Syntax of UNIX and DOS scripts. At the same time, pay attention to embedded comments, which is another advantage over Java code. Writing such annotations in Java is certainly acceptable, but it looks messy and out of line with SQL statements and must be embedded into Java strings.

The following is the Java code that calls the stored procedure:
Connection. setautocommit (false );
Callablestatement proc = connection. preparecall (/"{? = Call snuffed_it_when (?) }/");
Proc. registeroutparameter (1, types. integer );
Proc. setstring (2, poetname );
Cs.exe cute ();
Int age = Proc. getint (2 );

What if an error return value type is specified? Then, a runtimeexception will be thrown when a stored procedure is called, as you encounter when using an error type in the resultset operation.

Complex return values

Many people are familiar with the stored procedure. If this is all the functions of a stored procedure, the stored procedure is not a replacement for other remote execution mechanisms. Stored Procedure functions are much more powerful than this.
When you execute an SQL query, DBMS creates a database object called a cursor (cursor) to iterate each row in the returned results. Resultset is a cursor representation of the current time point. This is why you can only move forward in the resultset without caching or specific database support.
Some DBMS allow returning a reference to a cursor from a stored procedure. JDBC does not support this function, but the JDBC drivers of Oracle, PostgreSQL, and DB2 support opening the pointer to the cursor on the resultset ).
Imagine listing all poets who have not yet lived to retirement. The following is the Stored Procedure for completing this function, returning an open cursor, also using PostgreSQL's PL/pgsql language:
Create procedure list_early_deaths () return refcursor as 'descare
Toesup refcursor;
Begin
Open toesup for select poets. Name, deaths. Age from poets, deaths
-- All entries in deaths are for poets. -- but the table might become generic.
Where poets. ID = deaths. mort_id and deaths. age <60;
Return toesup;
End; 'language 'plpgsql ';

The following is the Java method that calls the stored procedure and outputs the result to printwriter:
Printwriter:
Static void sendearlydeaths (printwriter out ){
Connection con = NULL;