Spring invokes the result set of an Oracle stored procedure

oracle| Stored Procedures

Oracle is always different for advanced features (I hate this, if it's a product that needs to be programmed specifically for this product, and that's why I never looked at a platform like WebLogic), the big object access is set to use its own LOB objects, but luckily I can still pass long Raw to replace. So that the program does not need a specific encoding. But for the stored procedure (I mean the stored procedure that returns the result set), I don't have any way to handle Oracle with a generic program. Too many textbooks or articles in the call stored procedures are simply irresponsible to perform some of the number of Han or no results of the return of stored procedures, so that most readers do not know exactly how to adjust WA ⒐ phlegm mu Mao ? and in Spring, There is no real complete introduction to the processing of the result set of the stored procedure, let alone the result set processing of the "special" stored procedure such as Oracle.

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

1. Get the CallableStatement statement:

CallableStatement cs = Conn.preparecall ("{call Spname (?,?,?)}");

2. Incoming input parameters and registration output parameters

Cs.setxxx (Index,value);//Input parameters
Cs.registeroutparameter (Index,type);//Output parameters

3. Execute the stored procedure:

Cs.execute ();

For a stored procedure, if the result we know is known beforehand, it can be handled according to the order in which the stored procedure is defined (in fact generally not), but how do you define a generic process for handling complex, multiple result sets?

Let's take a look at what JDBC itself can provide for us. A stored procedure returns a Boolean after execution:
Boolean flag = Callablestatement.execute ();
If flag is true, then the description returns a result set (ResultSet) type, and you can use Getresultset () to get the result of the current row, and if returned to Flase, what does it say?

If you do not process, nothing can be explained, only that the current pointer is not resultset, there may be update count (Updatecount) may also have nothing to do.

So what if the current pointer is flase? We should first Getupdatecount (); If return-1, it is not a result set, it is not an update count. Description not returned. And if Getupdatecount () returns 0 or greater than 0, Indicates that the current pointer is an update count (possibly a DDL directive at 0). If you return a result set or update count, you may also continue to have other returns. Only at the current point pointer Getresultset () ==null && Getupdatecount ( = = 1 To show no more returns.

The return of the stored procedure is similar to the ResultSet, and the return result of each processing is equivalent to the ResultSet row, except that the row of the stored procedure is first in the first row, not like ResultSet, before the first row, The stored procedure moves down one line with Getmoreresults (), which is equivalent to ResultSet next (). Likewise it returns a Boolean and the flag above, just to indicate whether the current line is resultset, and if it is flase, You still have to judge is not updatecount, in each line, must first judge whether for ResultSet or updatecount, if it is one of the following getmoreresults (), When it is not resultset or updatecount, the description does not return the result, and then gets the output parameter.

Do you understand me?

So let's write a generic process based on the rules above:

First, we want to determine when we didn't say the result set:
if (cs.getresultset () = = null && cs.getupdatecount () = = 1)
Now let's do a loop:
ResultSet rs = null;
int updatecount =-1;
Flag = Cs.execute ();
do{
Updatecount = Cs.getupdatecount ();
if (Updatecount!=-1) {//Description The current line is an update count
Processing.
Cs.getmoreresults ();
continue;//is already an update count and should be moved to the next line after processing is complete
No longer judge whether it is resultset
}
rs = Cs.getresultset ();
if (Rs!= null) {//If here, explain updatecount = = 1
Processing RS
Cs.getmoreresults ();
Continue
Is the result set and should be moved to the next line after processing is complete
}
If we get here, Updatecount = = 1 && rs = null, nothing.

}while (!) ( Updatecount = =-1 && rs = null));
cs.getxxx (int),//Get output parameters

The above is the processing of the result set returned by a common stored procedure, and Oracle, which cannot return a result set, can only return a cursor in the output parameter, so you do not get any results in the generic process:

Package Pk_area_public is
TYPE Serarch_result is REF CURSOR;
PROCEDURE Area_search (vtarget_in in VARCHAR2, cur_result_out out Serarch_result);
End Pk_area_public;

Package Body Pk_area_public is
PROCEDURE Area_search (vtarget_in in VARCHAR2, cur_result_out 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, an output parameter, we want to accept the output parameter as the result set processing. So register should be registered as:

Cs.registeroutparameter (2,oracle.jdbc.oracletypes.cursor);//Output parameters

In this way, after the stored procedure is executed, get the output number styling as ResultSet can be processed:

ResultSet rs = (ResultSet) cs.getobject (2);

If you have more than one result set, use more than one output parameter.

Having understood the specificity of Oracle, let's look at the result set of the stored procedure in spring if it is processed:
Spring, when dealing with complex objects, mostly uses a callback method that requires the programmer to implement the interface method on its own. That is, it provides the run-time parameters of the program, requiring you to handle these parameters yourself. For JdbcTemplate, It provides resultset parameters for programmers to handle in many places. In the spring document, you can get a result set for a common process, that is, from the results of a stored procedure execution.
Routines:

Map out = Execute (new HashMap ());
In fact, it is the default implementation of the above JDBC general process to the Resuleset to the map of the package. For Oracle, we have to implement a callback to the resultset in the output parameter in our own hands:

public class Springstoredprocedure
Extends StoredProcedure {
Public arraylistDeclares a data structure to receive the result set, where the elements are row, and the map stores

Private Map inparam;//Input parameters
Private RowMapper rm = new RowMapper () {
Public Object Maprow (ResultSet rs,int rownum) throws sqlexception{
Return null;//do not need to get 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 a callback handle for output parameters
Public Springstoredprocedure (DataSource ds, String SQL) {
Setdatasource (DS);
SetSQL (SQL);
}

public void Setoutparameter (String column,int type) {
Declareparameter (New Sqloutparameter (column, type,callback));
Registering output parameters with callback handles
}
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 calling procedure:

Drivermanagerdatasource ds = ...;

Springstoredprocedure sp = new Springstoredprocedure (ds, "Pk_area_public.area_search");

Registers the parameter type, the input parameter and the output parameter simultaneously registers, otherwise cannot compile the stored procedure correctly
Sp.setparameter ("vtarget_in", Java.sql.Types.VARCHAR);
Sp.setoutparameter ("Cur_result_out", Oracle.jdbc.OracleTypes.CURSOR);
Sp.compile ();

Incoming input parameter values
Map in = new HashMap ();
In.put ("vtarget_in", "one content");
Sp. Setinparam (in);

Executing stored procedures
Sp.execute ();

Map m = sp.set.get (0);//reultset first record
Set defined as Springstoredprocedure property used to receive callback data
If you have more than one output parameter, you should generate the output in the callback method for each output parameter
The ArrayList of the parameter, and then added 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));
}

All in all, the above approach solves the call to Oracle stored procedures in spring, but I strongly recommend this complex processing