Oracle stored procedures and Java calls

Oracle stored procedures and Java calls

I found many bugs for the first time, so I paid a lot of attention. It took me three days to get rid of all the bugs. I will list all the points of attention, and there may be drops, please correct and discuss with each other.

First, the stored procedure code is successfully tested: (the code in it may not be useful to you. For reference, I will post it all for future reference)

The jar package uses ojdbc14.jar. You can use either class12.jar or ojdbc6.jar.

Let me talk about my requirements so that you can better understand my code. My requirements are: the stored procedure receives two parameters, userid and topicid, from the Java end ), in the stored procedure, the system cyclically queries the likes of the current user on the current topic. If a record exists, the record is set to 1. If no record exists, it is set to 0. Finally, a result set is returned, is the relational table corresponding to user => topic => record.

Now, let's get started with code and learning, so it's inevitable that your mood is fluctuating and nagging, and you want to describe it in the most straightforward way.

------------ Create a type in the database, corresponding to the Object structure to be passed in by JAVA: create or replace type tp_arr3 as Object (userid nvarchar2 (40 ), -- change varchar2 to nvarchar2 to match the String type in Java. topicid nvarchar2 (40), record nvarchar2 (4 ))

--- Multiple test creation may cause the error "ORA-02303: cannot use type or table relevance to delete or replace a type, in this case, you only need to change the TYPE name and CREATE a new package ---------- create or replace type tp_arr_tbl3 as table of tp_arr3, create a cursor type to put the output parameter create or replace package testpkg as type testcur is ref cursor; end testpkg; ------ create a stored procedure, define two parameters, one input parameter, is an array of object types (this type should meet most of the complex requirements), an output parameter is to store 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 used to store the corresponding record BEGIN sql2: = 'drop table tb_temp '; sql3: = 'create TABLE tb_temp (userid varchar2 (40), topicid varchar2 (40) 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; ------------ run the Stored Procedure declare ta tp_arr_tbl3: = tp_arr_tbl3 (); -- Object declaration t tp_arr3: = tp_arr3 ('0', '0', '0'); -- declare and assign initial values (required 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; -------------------- 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. It is useless later. You 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 Scott, which was initially used by the user. If he finds that he does not have the execution permission, he still cannot grant the dba permission to the user. Then he uses the system later;

2. multiple test creation may result in the "ORA-02303: cannot use type or table relevance to delete or replace a type" error, as long as you create another type name;

3. Because my tb_temp table has unique field constraints, each time a stored procedure comes in, it deletes the table, creates the table, and inserts data;

4. Remember not to miss the place where the semicolon should be typed, not to miss more places;

5. When executing the stored procedure, you must declare and assign the initial values first. Otherwise, an error will be reported;

6. Note that when the stored procedure test value is assigned in pl/SQL, ta. extend should not be less;

6. Pay attention to a wide range of custom types, such as nvarchar2 and JavaString;

7. to put it simply, the <span style = "font-family: Arial, Helvetica, sans-serif;"> tp_arr3 type indicates a record, </span> <span style = "font-family: Arial, Helvetica, sans-serif;"> tp_arr_tbl3 indicates multiple records. </span>

-- Like relational table create table tb_praise_rel (id varchar2 (40) primary key, userid varchar2 (40), -- User id topicid varchar2 (40), -- Topic id remarks1 varchar2 (3000 ), -- remarks2 varchar2 (3000), remarks3 varchar2 (3000 ));

Then use the Java call 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 online users are stuck in obtaining con, which was originally used by me, what is the jar package problem? Delete class12? What is the conflict between oracle and apache connection pool? But I did not write the correct stored procedure, finally, I used this method to test and passed the test, without adding (org. apache. commons. dbcp. poolableConnection) conn ). getInnermostDel Egate () Connection con = DriverManager. getConnection (url, "system", "a"); // PreparedStatement pstmt = null; CallableStatement cs = null; ResultSet rs = null; list <ProcedureBean> list = new ArrayList <ProcedureBean> (); for (int I = 1; I <= 12; 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 of the as object type, java calls this step, which is a ing of the tp_arr3 type declared in pl/SQL, indicates to match your custom type in pl/SQL. // you must also specify uppercase letters. Otherwise, "invalid name mode" StructDescriptor recDesc = StructDescriptor may be reported. createDescriptor ("TP_ARR3", con); // This step converts your custom type to your oracle type, that is, STRUCT, which is equivalent to an Object class, because oracle developers do not know what type of name you will define, they only provide one rule, finally, we all 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); pstruct. add (item) ;}// this is the second step of ing. It maps to the tp_arr_tbl3 type customized in oracle. Note that it should also be capitalized. The package name should also be added online, not the same user needs to add a user prefix or something. I did not add it, and the test passed. It may not be an extreme situation. oracle. SQL. arrayDescriptor desc = oracle. SQL. arrayDescriptor. createDescriptor ("TP_ARR_TBL3", con); oracle. SQL. ARRAY array = new L E. SQL. ARRAY (desc, con, pstruct. toArray (); // You can also add cs = con. prepareCall ("{call findRecord (?,?)} "); // Set the parameters here. 1 and 2 respectively correspond to the stored procedure findRecord (?,?). SetArray (1, array); cs. registerOutParameter (2, OracleTypes. CURSOR); cs.exe cute (); rs = (ResultSet) cs. getObject (2); // The while (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: basically, the notes are written in the code in the form of comments, and may not be thought of. Many bug information cannot be copied in time because the test is successful. Do not import the wrong package

2. error Message "Message file 'oracle. jdbc. driver. messages 'is missing. ", it may be that you wrote an error or the type does not match in oracle. do not find any jar package or other resources. There is not much information on the Internet. I 've been around for a long time, check the package name, Case sensitivity, and conversion type mentioned above;

There are other bugs that you don't think of or haven't encountered. You can only combine them with the error information to guess and perform a manual test. I believe they will be able to solve the problem.

Test javaBean:

package com.lofter.bean;import java.io.Serializable;public class ProcedureBean implements Serializable {private static final long serialVersionUID = 809894604693791308L;private String userid;private String topicid;private String record;public ProcedureBean() {super();}public ProcedureBean(String userid, String topicid, String record) {super();this.userid = userid;this.topicid = topicid;this.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 + "]";}}

I have been studying this for several days. I mainly want to study the object array type, but I am not clear about the syntax. I can only refer to other people's code that can understand it, by the end of the test, I had a tear. I learned how many times to press the Enter key and how many times to run it at four o'clock in the morning, it may also be a blow to all the previous bug Debugging Processes), because many complex types of parameters are not clearly stated on the Internet, and there are many bugs, so they cannot be used. During the test, the bug continued constantly, Baidu, and I also flipped through the teaching materials that I did not carefully read at ordinary times. Finally, I finally made a call from PL/SQL to Java. In fact, the bug was most likely found in Java calls, different types of data do not match. It can only be said that the search engine is really powerful, the Internet is really powerful, and big data is really powerful. Finally, I want to say: "People are afraid of being serious ".