Result of spring calling the Oracle Stored Procedure

Result of spring calling the Oracle Stored Procedure

Oracle is always different from each other for advanced features (I hate this very much. If you use it, you need
For specific programming, this is why I never look good at Weblogic and other platforms .)
Fortunately, I can use long raw to replace the lob object so that the program does not need specific encoding.
(I mean the stored procedure of the returned result set), I have no way to use a common program to process oracle. Too many
The calling of stored procedures in teaching materials or articles simply executes some stored procedures with no meaning or result returned,
So that most readers do not know how to call the result set of the stored procedure. In spring, it is not completely
This section describes how to process the result set of a stored procedure, not to mention the processing of a "special" stored procedure like Oracle.

Let's take a brief look at how we can process the general process of the result set of the stored procedure in JDBC:

1. Obtain the callablestatement statement:

Callablestatement cs = conn. preparecall ("{call spname (?,?,?)} ");

2. input parameters and register output parameters

CS. setxxx (index, value); // enter the Parameter
CS. registeroutparameter (index, type); // output parameters

3. Execute the stored procedure:

Cs.exe cute ();

4. process the result set. For more information, see the previous article.

The above is the processing of the result set returned by a general stored procedure. In Oracle, it cannot return the result set, but can only be in the output parameter.
A cursor is returned, so you cannot get any results in a general process:

Package pk_area_public is
Type serarch_result is ref cursor;
Procedure area_search (vtarget_in varchar2, cur_result_out serarch_result );
End pk_area_public;

Package body pk_area_public is
Procedure area_search (vtarget_in varchar2, cur_result_out serarch_result)
Is
Sqlstr varchar2 (1000 );
Begin
Sqlstr: = 'select .................................';
Open cur_result_out for sqlstr using vtarget_in;
End area_search;
End pk_area_public;

For the above example, the stored procedure has an input parameter and an output parameter. We need to accept the output parameter as the result set for processing.
When registering, you should register:

CS. registeroutparameter (2, Oracle. JDBC. oracletypes. cursor); // output parameters

In this way, after the stored procedure is executed, you can obtain the output number and shape it as resultset:

Resultset rs = (resultset) CS. GetObject (2 );

If multiple result sets exist, multiple output parameters are used.

To understand the particularity of Oracle, let's look at the result set for processing its stored procedure in Spring:
When processing complex objects, spring mostly uses the callback method and requires the programmer to implement the interface method by themselves. That is, it provides
You must process the parameters when running the program. For jdbctemplate, it provides the resultset in many places.
Parameters are provided for programmers to process. In the spring document, a general process is provided, that is, the result set is obtained from the execution results of the stored procedure.
Routine:

Map out = execute (New hashmap ());
In fact, it implements the encapsulation of resuleset to map in the general JDBC process above by default. For Oracle, We must manually
Implement the callback of the resultset in the output parameter:

Public class springstoredprocedure
Extends storedprocedure {
Public arraylist // Declare a Data Structure for receiving result sets. The elements in the structure are row, which is stored by map.

Private map inparam; // input parameters
Private rowmapper Rm = new rowmapper (){
Public object maprow (resultset RS, int rownum) throws sqlexception {
Return NULL; // you do not need to obtain results from the stored procedure itself.
}
};

Private rowmapperresultreader callback = new rowmapperresultreader (RM ){
Public void processrow (resultset RS) // callback Processing
Throws sqlexception {
Int COUNT = Rs. getmetadata (). getcolumncount ();
String [] header = new string [count];
For (INT I = 0; I <count; I ++)
Header [I] = Rs. getmetadata (). getcolumnname (I + 1 );
While (Rs. Next ()){
Hashmap <string, string> ROW = new hashmap (count + 7 );
For (INT I = 0; I <count; I ++)
Row. Put (header [I], Rs. getstring (I + 1 ));
Set. Add (ROW );
}
}
}; // Rowmapperresultreader as the callback handle of the output parameter
Public springstoredprocedure (datasource ds, string SQL ){
Setdatasource (DS );
Setsql (SQL );
}

Public void setoutparameter (string column, int type ){
Declareparameter (New sqloutparameter (column, type, callback ));
// Use the callback handle to register output parameters
}
Public void setparameter (string column, int type ){
Declareparameter (New sqlparameter (column, type ));
}

Public void setinparam (MAP inparam ){
This. inparam = inparam;
}

Public map execute (){
Compile ();
Return execute (this. inparam );
}
}

Let's take a look at the call process:

Drivermanagerdatasource DS = .......;

Springstoredprocedure sp = new springstoredprocedure (DS, "pk_area_public.area_search ");

// Register the parameter type. The input and output parameters are both registered. Otherwise, the stored procedure cannot be correctly compiled.
Sp. setparameter ("vtarget_in", java. SQL. types. varchar );
Sp. setoutparameter ("cur_result_out", Oracle. JDBC. oracletypes. cursor );
Sp. Compile ();

// Input parameter value
Map in = new hashmap ();
In. Put ("vtarget_in", "one content ");
Sp. setinparam (in );

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

Map M = sp. Set. Get (0); // The first record of the reultset
// Set is defined as the springstoredprocedure attribute used to receive data during callback.
// If multiple output parameters exist, the output should be generated in the callback method of each output parameter
// Arraylist corresponding to the parameter, and then add it to the data structure of a member variable.
Iterator I = M. keyset (). iterator ();
While (I. hasnext ()){
String key = I. Next (). tostring ();
System. Out. println (Key + "=>" + M. Get (key ));
}

In short, although the above method solves the call to the Oracle stored procedure in spring, I strongly do not recommend this complicated process.