Oracle stored procedures and Java calls

The first time the bug is a lot of places, so pay attention to a lot, it took me three days to do all the bugs, I will pay attention to the points are listed, there may be a place to fall, but also please correct, mutual discussion.

First on the final test of the successful version of Stored procedure Code: (Inside the code may not be useful for you, for reference, I put it all out for me after good search)

Say the jar is Ojdbc14.jar, as to what Class12.jar, Ojdbc6.jar ah should all, as long as one can.

Talk about my needs, so that people can read my code more smoothly, my need is: stored procedures from the Java side to receive two parameters UserID (user) and TopicID (topic), in the stored procedure to cycle through the current user to the current topic like record, if there is a record, The record is set to 1, not 0, and finally returns a result set, which is the relational table that responds to the user's topic =>record.

Ok no nonsense, on the code bar, learning stage, so it is inevitable emotional fluctuations and wordy, but also hope to be the most straightforward to understand the way out.

------------establish a type in the database that corresponds to the structure of the object to be passed in to the Java side:  Create or replace type Tp_arr3 as Object    (      userid   NVARCHAR2 (+),    --this is changed from VARCHAR2 to NVARCHAR2 type in order to match Java string on    topicid nvarchar2 (+),  record NVARCHAR2 (4 )    )

---Multiple test creation the error "ORA-02303: Could not be used to delete or replace a type with a type or table dependency" could occur when a type name is re-created----------Create or replace type Tp_arr_ Tbl3 as TABLE of TP_ARR3--------------Create a package, create a cursor type to put output parameters  create or replace package testpkg as type testcur is ref Cursor End testpkg;------Create a stored procedure, define two parameters, an entry parameter, an array of object types (this type should satisfy most of the complex requirements), an out parameter, a cursor to hold the query value   CREATE OR REPLACE procedure   FindRecord (Type_obj in Tp_arr_tbl3,result out testpkg.testcur) as T tp_arr3;  SQL2 VARCHAR2 (500);   Sql3 VARCHAR2 (500);     V_count VARCHAR2 (4);     -Temporary intermediate variable for storing the corresponding record  BEGIN sql2: = ' drop table tb_temp ';       Sql3: = ' CREATE TABLE tb_temp (userid varchar2 (+), TopicID varchar2 (+) primary key, record VARCHAR2 (4)) ';       Execute immediate sql2;              Execute immediate sql3; For I in Type_obj.first ():       Type_obj.last () LOOP t:= type_obj (i);       Select COUNT (*) into V_count from Scott.tb_praise_rel where Userid=t.userid and topicid=t.topicid; Dbms_output.put_line (T.userid | | ' = ' | | T. topicid | | ' = ' | |       V_count);      INSERT into tb_temp values (T.userid,t.topicid, v_count);      END LOOP;            COMMIT; Open result for select * from Tb_temp;      END;--------------execute stored procedure declare TA tp_arr_tbl3:=tp_arr_tbl3 ();    --the declaration of the object  t tp_arr3:=tp_arr3 (' 0 ', ' 0 ', ' 0 ');  --Declaration and assignment of initial value (necessary steps) beginfor I in 1..12 loopta.extend;  t.userid:= ' 1 ';  T.topicid:=i;  t.record:= ' 0 ';  Ta (i): =t;  FindRecord (TA);  End Loop; End FindRecord;  ----------------------Table Query Test Section SELECT * from Tb_temp;  SELECT * from Scott.tb_praise_rel; Select UserID from Scott.tb_praise_rel where userid= ' 1 ' and topicid= ' 1 ';-----------------------cursor test, later useless, can skip cursor   Testcur is select Userid,topicid from Scott.tb_praise_rel;     Cur testcur%rowtype;    Open testcur;     Loop fetch testcur into cur;     Exit when Testcur%notfound; Dbms_output.put_line (' userid: ' | | cur.userid | | ', TopicID: ' | |     CUR.TOPICID);     Update tb_temp set record= ' 1 ' where Userid=cur.userid and topicid=cur.topicid; End Loop;     Dbms_output.put_line ('----------------------');      Close testcur; COMMIT;

Note: 1. Test the user's initial use of Scott, found that there is no execution permission, to give DBA authority or not, and subsequently used system;

2. Multiple test creation may occur "ORA-02303: cannot use type or table dependencies to delete or replace a type" error, this time just change a type name to create it again;

3. Since my tb_temp table has a unique field constraint, each time the stored procedure comes in, it deletes the table, builds the table, and then inserts the data;

4. Remember the number of places do not miss, should not hit the place not much;

5. When executing the stored procedure, declare and assign the initial value first, or else the error will be given;

6. Note that in PL/SQL, the ta.extend of the stored procedure test assignment is not limited;

6. There are many places to be aware of custom types, such as definitions of NVARCHAR2 and javastring types;

7. Simply say <span style= "font-family:arial, Helvetica, Sans-serif;" >TP_ARR3 type refers to a record, </span><span style= "font-family:arial, Helvetica, Sans-serif;" >TP_ARR_TBL3 refers to multiple records;</span>

--like the relational table create table Tb_praise_rel (       ID varchar2 (+) primary key,       userid varchar2 (+),             --User ID        topicid  varchar2 (+),            --topic ID        remarks1 varchar2,        --alternate field        remarks2 varchar2,       REMARKS3 VARCHAR2 (3000));

Then the Java calling code:

Package Com.lofter.svntesr;import Java.sql.array;import Java.sql.callablestatement;import java.sql.Connection; Import Java.sql.drivermanager;import Java.sql.preparedstatement;import Java.sql.resultset;import Java.sql.sqlexception;import Java.util.arraylist;import Java.util.list;import Java.util.Map;import Oracle.jdbc.oracletypes;import Oracle.sql.struct;import Oracle.sql.structdescriptor;import  Com.lofter.bean.procedurebean;public class ProcedureTest3 {/** * @param args */public static void main (string[] args) {try                {Class.forName ("oracle.jdbc.driver.OracleDriver");                String url = "Jdbc:oracle:thin: @localhost: 1521:ORCL"; Many cards on the net to get con this place, I originally also, say is what jar package problem, delete class12 Ah, what Oracle and Apache Connection pool conflict Ah, but I actually is the stored procedure did not write right, finally around a circle back or use this method test pass, and did not add ( ORG.APACHE.COMMONS.DBCP.POOLABLECONNECTION) conn). Getinnermostdelegate () Connection con = drivermanager.getconn          Ection (URL, "system", "a");              PreparedStatement pstmt = null; CallablEstatement cs = null;                        ResultSet Rs=null;    list<procedurebean> list = new arraylist<procedurebean> ();    for (int i = 1; i <=; i++) {String r = i + "";    List.add (New Procedurebean ("1", R, "0"));        }//list.add (New Procedurebean ("1", "5f60b0f0-03d9-4671-b945-936fe821fe19", "0")); If the stored procedure is using an object array like mine as Object type, then Java calls this step is necessary, which is a mapping of the TP_ARR3 type declared in PL/SQL before, indicating that you can match your custom type in PL/SQL//and pay attention to uppercase, otherwise it may be reported "    Invalid name pattern "Structdescriptor Recdesc = Structdescriptor.createdescriptor (" Tp_arr3 ", con); This step is to convert your custom type to Oracle's own type, the struct, which is equivalent to an object class, because the Oracle developer doesn't know what type of name you're going to define, anyway, just provide a rule, In the end, all of us follow this rule to convert arraylist<struct> pstruct = new arraylist<struct> (); for (Procedurebean pb:list) { System.out.println (PB); object[] Objs = new Object[3];objs[0] = Pb.getuserid (); objs[1] = Pb.gettopicid (); objs[2] = Pb.getrecord (); struct item = new struct (Recdesc, con, OBJS);p Struct.add (item);} This is the second-step mapping, which maps the type of TP_ARR_TBL3 I've customized in Oracle, as well as uppercase, and onlineSay to add the name of the package, not the same user to add a user prefix or something, I did not add, test also passed, may not be extreme situation it oracle.sql.ArrayDescriptor desc =                Oracle.sql.ArrayDescriptor.createDescriptor ("Tp_arr_tbl3", con);                         Oracle.sql.ARRAY ARRAY = new Oracle.sql.ARRAY (desc, con, Pstruct.toarray ());                        There are also said to call the time to add the package name of CS = Con.preparecall ("{Call FindRecord (?,?)}"); Setup parameters here, 1, 2 corresponds to the stored procedure FindRecord (?,?)            In the position of the parameter, note the position not wrong Cs.setarray (1, array);            Cs.registeroutparameter (2, oracletypes.cursor);            Cs.execute (); Rs= (ResultSet) Cs.getobject (2);//fetching data is also based on the corresponding parameter position (Rs.next ()) {System.out.println (            "Result: \ t" + rs.getstring (1) + "\ T" +rs.getstring (2) + "\ T" +rs.getstring (3));        } con.commit ();            } catch (Exception e) {e.printstacktrace (); }        }    }

Java Call Note: The basic notes are written in the code in the form of comments, it may not be recalled, a lot of bug information due to the test through, not in time to copy down. Be careful not to lead the wrong bag

2. Error message "message file ' Oracle.jdbc.driver.Messages ' is missing.", maybe you wrote the wrong or the type does not match the Oracle, do not look for what jar package ah what, online information is not much, I have been around for a long time, more check the above mentioned the name of the package, the case, the type of rotation or something;

There are other not think up or hit the bug only the pro-many combination of error information speculation, multi-hands measurement, I believe it will be solved.

JavaBean of the test:

package Com.lofter.bean;import Java.io.serializable;public class Procedurebean Implements Serializable {private static final long Serialversionuid = 809894604693791308l;private String userid;private S Tring topicid;private String record;public Procedurebean () {super ();} Public Procedurebean (String userid, String topicid, string record) {super (); This.userid = Userid;this.topicid = Topicid;t His.record = record;} Public String GetUserid () {return userid;} public void Setuserid (String userid) {this.userid = userid;} Public String Gettopicid () {return topicid;} public void Settopicid (String topicid) {this.topicid = TopicID;} Public String Getrecord () {return record;} public void Setrecord (String record) {This.record = record;}  @Overridepublic String toString () {return "Procedurebean [userid=" + userid + ", topicid=" + topicid+ ", record=" + record + "]";}}

Toss me for a few days to study this thing, mainly to study the object array type, from the grammar are not clear, can only refer to the others can read the code speculation to write, to the final test through, a bitter tear ah, two days of research to four o'clock in the morning, knocked not know how many times enter, point the number of times run (in fact, not much, may also be a vent to the previous debugging of all bug processes, because the complex type of parameters on the Internet is not clear, there are many bugs, so there is no way to use. During the test, the bug constantly, has been Baidu, also turned down in peacetime not seriously read the textbook, finally get through the call from PL/SQL to Java calls, in fact, the most bugs in Java calls, various types do not match, can only say that search engine is really powerful, the internet is really powerful, big data is really powerful. Finally, I would like to say: "People are afraid of serious."

Oracle stored procedures and Java calls