Result set for spring to call the Oracle Stored Procedure

Http://blog.csdn.net/sunsnow8/archive/2005/01/10/246588.aspx

Oracle is always different from others 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 .)

Lob object. Fortunately, I can use long raw to replace it.ProgramNo specific encoding is required, but for Stored Procedures

(I mean the stored procedure of the returned result set), I have no way to use a common program to process oracle. Too many

Teaching material orArticleWhen talking about the calling of a stored procedure, it simply executes the stored procedure with no meaning or no 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 ();

For a stored procedure, if the returned results are known in advance, the stored procedure can be defined in order.

Processing (in fact not like this), but how to define a general process for processing complex multi-result sets?

Let's take a look at what JDBC itself can provide for us?

After a stored procedure is executed, a boolean type is returned:

Boolean flag = callablestatement.exe cute ();

If the flag is true, a result set (resultset) type is returned. You can use getresultset () to obtain the current row.

If the returned result is flase, what is the description?

If you do not perform the processing, nothing can be explained. It only indicates that the current pointer is not a resultset and may be an updatecount)

There may be no anti-cause.

So what if the current pointer is flase? We should first getupdatecount (); If-1 is returned, neither the result set nor

No update count is returned. If getupdatecount () returns 0 or greater than 0, the current pointer is the update count (

When the value is 0, it may be a DDL command). Whether it is a returned result set or an update count, other returned results may continue.

The pointer getresultset () = NULL & getupdatecount () =-1 indicates that no more results are returned.

The returned results of the stored procedure are similar to those of the resultset. The returned results of each processing procedure are equivalent to the row of the resultset, except that the row of the stored procedure

First in the first row instead of the first row to the first row as the resultset, the stored procedure moves down a row using getmoreresults (),

In the next () of the resultset, it returns the same boolean as the flag above, but it only indicates whether the current row is a resultset.

Flase, you still need to determine whether it is updatecount. In each row, you must first determine whether it is resultset or updatecount, as shown in

If one of them is to continue getmoreresults (), if it is neither resultset nor updatecount, it means no result is returned,

Then obtain the output parameters.

Do you understand?

Let's write a general process according to the above rules:

First, we need to determine when the result set is not mentioned:

If (CS. getresultset () = NULL & CS. getupdatecount () =-1)

Now let's make a loop:

Resultset rs = NULL;

Int updatecount =-1;

Flag = cs.exe cute ();

Do {

Updatecount = cs. getupdatecount ();

If (updatecount! =-1) {// indicates that the current row is an update count.

// Process.

CS. getmoreresults ();

Continue; // update count. After processing, move it to the next row.

// No longer judge whether it is a resultset

}

Rs = cs. getresultset ();

If (RS! = NULL) {// If so, updatecount =-1

// Process rs

CS. getmoreresults ();

Continue;

// Is the result set. After processing, move it to the next row.

}

// If so, it means updatecount =-1 & rs = NULL, and nothing else



} While (! (Updatecount =-1 & rs = NULL ));

CS. getxxx (INT); // get output parameters

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);
// run 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.

Copyright Disclaimer: csdn is the hosting service provider of this blog. If this article involves copyright issues, csdn does not assume relevant responsibilities. Ask the copyright owner to contact the author directly.