Http://blog.9cbs.net/sunsnow8/archive/2005/01/10/246588.ASPX Oracle is always different from the high characteristics (I tried this, if you use its products, you must be on this product. Perform specific programming, this is why I never optimistic about WebLogic's platform), big object access one to set its own LOB object, fortunately, I can still replace it through long Raw. So that I can make the program no need Specific encoding. But for the stored procedure (I said the stored procedure for returning the result set), I have nothing to handle with a universal program to handle Oracle. Too many textbooks or articles are talking about the memory process. The responsibility is simple to perform some of the memory procedures that have not been returned, so that most readers don't know how to call the result set of stored procedures. In Spring, they do not really introduce the result set of stored procedures. Treatment, let alone the result set processing of the "special" stored procedure of Oracle.
Let's take a brief look at how we handle the result set of stored procedures in JDBC:
1. Get the CallableStatement statement:
Callablestatement cs = conn.preparecall ("{call spname (?,?,?)}");
2. Pass input parameters and register output parameters
CS.SETXXX (INDEX, VALUE); // Enter parameter cs.registeroutparameter (index, type); // output parameter
3. Execute the stored procedure:
cs.execute ();
For a process, if the result returned is what we know, then it can be processed according to the order defined by the stored procedure (in fact, it is not like this), but how to define a general purpose for complex multi-result sets Process?
Let's take a look at what JDBC itself provides us? One stored procedure is executed after the Boolean type: boolean flag = callablestatement.execute (); if Flag is True, then the description returns a result set (Resultset) type, you You can use getResultSet () to get the result of the current line, and if you return to flash, what is it?
If you don't work, you can't explain, you can only explain that the current pointer is not ResultSet, it is possible to update the count (UpdateCount) may also be nothing.
So how do you handle when the current pointer is FLASE? We should get getUpdateCount (); if you return -1, it is neither a result set, not an update count. Description did not return. And if getUpdateCount () returns 0 or more than 0, The current pointer is a update count (possibly the DDL instruction when 0). Whether it is a return result set or an update count, then there may be other returns. Only in the current pointer getResultSet () == null && getupdateCount () == -1 Table not to return more.
The return of the stored procedure is similar. The return result of each process is equivalent to the ROW of the ResultSet, but the ROW of the stored procedure is first in the first line instead of the results, and the next is first until the first line, the stored procedure moves down One line with getMoreresults (), equivalent to the result () of ResultSet (). Similarly it returns to Boolean and the above Flag, just means that the current line is a resultset, if it is flash, you still want to judge whether it is UpdateCount, in each line, At the same time, it is judged whether it is RESULTSET or UpdateCount. If one is one of them, it is necessary to continue getMoreResults (). When not ResultSet is not UpdateCount, the description does not return the result, then acquire the output parameter.
do you understand?
Then we write a general process according to the rules above:
First of all, we have to make sure did not say the result set: if (cs.getResultSet () == null && cs.getupdateCount () == -1) Now we 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 // process. cs.getMoreResults (); continue; / / Already the update count, should be moved to the next line after processing, no longer judge whether it is resultset} RS = cs.getResultSet (); if (rs! = Null) {// If it is here, explain UpdateCount == -1 // Handle rs cs.getMoreResults (); Continue; // is the result set, should be moved to the next line after processing is completed} // If it is here, it means that UpdateCount == -1 && == Null, nothing } While (! (! (== null)); cs.getxxx (int); // Get the output parameter or more is the processing of the result set returned for the universal stored procedure, and Oracle, it Can't return a result set, you can only return a CURSOR in the output parameter, so you get any results in the general process:
Package pk_area_public IS TYPE SERARCH_RESULT IS REF CURSOR; Procedure Area_Search (vtarget_in in varcha2, cur_result_out out series_result); end pk_area_public;
Package body pk_area_public is procedure area_search (vtarget_in in varcha2, cur_result_out out serrch_result) is sqlstr varcha2 (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 have to accept the output parameter as a result set process. The registration should be registered as:
CS.RegisterOutparameter (2, Oracle.jdbc.Oracletypes.cursor); // Output parameters
Thus, after the stored procedure is executed, obtain the output number and shape to resumption:
ResultSet RS = (ResultSet) cs.getObject (2);
If there are multiple result sets, multiple output parameters are used.
Understand the particularity of Oracle, let's take a look at the result set in Spring if it handles its stored procedures: Spring is in handling complex objects, mostly uses the callback method to request the programmer to implement the interface method. That is to provide The parameters of the program are required to process these parameters. For JDBCTemplate, it provides the ResultSet parameter supply service. In the Spring documentation, the general process is provided, that is, from the stored procedure execution result. Get the result set routine: map out = execute (new hashmap ()); actually it is the default implementation of the above JDBC universal flow to the resuleset to the MAP package. For Oracle, we must implement the output parameters Resultset's callback:
Public class SpringstoredProcedure Extends StoredProcedure {Public ArrayList
Private Map INPARAM; // Input Parameter Private RowMapper RM = New RowMapper () {Public Object MapRow (ResultSet RS, INT ROWNUM) THROWS SQLEXCEPTION {Return NULL; // No need to obtain results from the stored procedure itself}}}}
private RowMapperResultReader callback = new RowMapperResultReader (rm) {public void processRow (ResultSet rs) // callback handler throws SQLException {int count = rs.getMetaData () getColumnCount ();. String [] header = new String [count]; for ( INT i = 0; i public void setOutParameter (String column, int type) {declareParameter (new SqlOutParameter (column, type, callback)); // use the output parameters registered callback handler} 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 process: DrivermanagerDataSource DS = ....... SpringStoredProcedure SP = New SpringStoredProcedure (DS, "PK_Area_Public.Area_Search"); // Register the parameter type, input parameter, and output parameters simultaneously register, otherwise the stored procedure sp.SETPARERETER ("vtarget_in", java.sql.types.varchar ); Sp.setOutParameter ("cur_result_out", oracle.jdbc.oracletypes.cursor); sp.Compile (); // Pass input parameter value map in = new hashmap (); in. Put ("vtarget_in", "one Content "); sp.setinparam (in); // Perform stored procedure sp.execute (); Map m = sp.SET.GET (0); // ReultSet's first record // set defines the properties of SpringStRocedure to receive data // if there are multiple output parameters, should be in each output parameter The arraylist corresponding to the output // parameter is generated in the callback method, and then add it to a data structure of a member variable. Itrator i = m.keyset (). Iterator (); while (I.hasNext ()) {string key = i.next (). Tostring (); system.out.println (key => " m.get (key));} In short, the above method solves the call to the Oracle stored procedure in Spring, but I don't recommend this complex processing.
