The Java call stored procedure returns an array

Java To call a stored procedure:

combined with SQL operations and stored procedures

Create PROCEDURE Set_death_age (poet VARCHAR2, poet_age number)

POET_ID number;

Begin SELECT ID into poet_id from poets WHERE name = poet;

INSERT into deaths (mort_id, age) VALUES (poet_id, poet_age);

End Set_death_age;

Here is the Java code that calls the stored procedure above:

public static void Setdeathage (poet Dyingbard, int.) throws sqlexception{

Connection con = null;

callablestatement proc = null;

try {

con = connectionpool.getconnection ();

proc = Con.preparecall ("{Call Set_death_age (?,?)}");

Proc.setstring (1, Dyingbard.getname ());

Proc.setint (2, age);

Proc.execute ();

}

finally {

try {proc.close ();}

catch (SQLException e) {}

Con.close ();

}

}

Functions

Stored procedures can have return values, so the CallableStatement class has a method like Getresultset to get the return value. When a stored procedure returns a value, you must use the Registeroutparameter method to tell the JDBC drive what the SQL type of the value is. You must also adjust the stored procedure tune to indicate that the procedure returns a value.

Following is the example above. This time we inquire about the age of Dylan Thomas's death. This time the stored procedure uses:

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 = $;

--get and return the age.

SELECT age to Poet_age from deaths WHERE mort_id = poet_id;

return age;

End

Here is the Java code that calls this stored procedure:

Connection.setautocommit (FALSE);

callablestatement proc = Connection.preparecall ("{? = Call Snuffed_it_when (?)} "); Proc.registeroutparameter (1, Types.integer);

Proc.setstring (2, poetname); Cs.execute ();

int age = Proc.getint (2);

In the use of stored procedures, we sometimes need to pass a mutable array, there are two cases where the stored procedure has input or output parameters for a custom mutable array. In Java code, how to correctly invoke a custom mutable array type for an Oracle stored procedure, here is a sample explanation.

Java calls the custom type of the Oracle stored procedure:

Plsql defines a string and a numeric variable group:

A To define a global type:

CREATE OR REPLACE TYPE Userseqid_array is Varray (50000) of number (9)

CREATE OR REPLACE TYPE Username_array as Varray (+) of varchar (32)

CREATE OR REPLACE TYPE Userpwd_array as Varray (50000) of varchar (60)

Two Java calls the stored procedure whose output parameter is a custom array:

2.1 Output parameters are stored procedure Make_logincard_pro for custom arrays:

Procedure Make_logincard_pro (

P_cardsuitcode in varchar,

P_userseqidarr out Userseqid_array,

P_usernamearr out Username_array

)

Is

V_addedtime date:= sysdate;

BEGIN

For II in 1.. Ten loops

IF P_userseqidarr is NULL then

P_userseqidarr: = Userseqid_array (ii);

ELSE

P_userseqidarr.extend; --exceeding the array definition size (50000) throws an exception

P_userseqidarr (ii): = II;

END IF;

IF P_usernamearr is NULL then

P_usernamearr: = Userseqid_array (ii | | ' TT ');

ELSE

P_usernamearr.extend; --exceeding the array definition size (32) throws an exception

P_usernamearr (ii): = II | | ' TT ';

END IF;

END LOOP

END Make_logincard_pro;

2.2JAVA Calling stored procedure Make_logincard_pro:

Code Snippets

Connection con = session.connection ();

java.sql.CallableStatement CST = con

Preparecall ("Call Cnbt.test_pro (?,?,?)");

Cst.setstring (1, Cardsuitcode);

Cst.registeroutparameter (2, Oracletypes.array, "Userseqid_array");

Cst.registeroutparameter (3, Oracletypes.array, "Username_array");

Java.sql.Array Userseqidarr = Cst.getarray (2);

Java.sql.Array Usernamearr = Cst.getarray (3);

if (Userseqidarr! = null) ... {

BigDecimal userseqidlist[] = (bigdecimal[]) Userseqidarr.getarray ();//The number of the database is mapped to BigDecimal

。。。。。。

}

if (Usernamearr! = null) ... {

String usernamelist[] = (string[]) Usernamearr.getarray ();

。。。。。。

}

--------------------------------------------------------------------------------

Three. Java calls the stored procedure for which the input parameter is a custom array:

3.1 The input parameter is a custom array of stored procedure update_logincard_pwd:

/**//**********************************************

* UPDATE_LOGINCARD_PWD *

* Function Description: update password stored procedure *

* Input Parameters: *

* Output Parameters: *

*hanjiong *

***********************************************/

Procedure Update_logincard_pwd (

P_userseqidlist in Userseqid_array,

P_userpwdlist in Userpwd_array,

P_resultcode out number

);

3.2 Java calls stored procedure update_logincard_pwd:

Code Snippets

..........................

Connection con = session.connection ();//WebLogic Data source used

Oracle.jdbc.OracleCallableStatement cst2 = (oracle.jdbc.OracleCallableStatement) con

. Preparecall (

"Call Cnbt.update_logincard_pwd (?,?,?)");

Weblogic.jdbc.wrapper.Connection weblogicconn = (weblogic.jdbc.wrapper.Connection) con;

Oracle.jdbc.OracleConnection oracleconn = (oracle.jdbc.OracleConnection) weblogicconn.getvendorconnection ();// Conversion connection

Oracle.sql.ArrayDescriptor Des_userseqid_array =

Oracle.sql.ArrayDescriptor.createDescriptor ("Userseqid_array", oracleconn);

Oracle.sql.ArrayDescriptor Des_userpwd_array =

Oracle.sql.ArrayDescriptor.createDescriptor ("Userpwd_array", oracleconn);

Oracle.sql.ARRAY ora_array1 = new Oracle.sql.ARRAY (Des_userseqid_array, Oracleconn, Useraccseqidarr);

Oracle.sql.ARRAY ora_array2 = new Oracle.sql.ARRAY (Des_userpwd_array, Oracleconn, userpwdlist);

Cst2.setarray (1, ora_array1);

Cst2.setarray (2, ora_array2);

Cst2.registeroutparameter (3, Java.sql.Types.INTEGER);

Cst2.execute ();

Updatecode = Cst2.getint (3);

.....................................

....................................

Because I'm using a WebLogic configured data source, you need to be aware of the connection objects that you get, The Connection object obtained through the data source is weblogic.jdbc.wrapper.Connection, so it cannot be converted directly to oracle.jdbc.OracleConnection, otherwise it will appear java.lang.ClassCastExcep tion exception, so we're going through weblogic.jdbc.wrapper.Connection.getVendorConnection () Obtained java.sql.Connection, in the forced conversion to oracle.jdbc.OracleConnection.

--------------------------------------------------------------------------------

In both cases, you can use Zid in Oracle stored procedures

The Java call stored procedure returns an array