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 on JAVA:
Create or replace type tp_arr3 as Object
(
Userid nvarchar2 (40), -- change from varchar2 to nvarchar2 to match the String type of Java.
Topicid nvarchar2 (40 ),
Record nvarchar2 (4)
)

--- Multiple test creation may result in the error "ORA-02303: cannot use type or table relevance to delete or replace a type", then you only need to create a new type name.
----------
Create or replace type tp_arr_tbl3 as table of tp_arr3
-------------- Create a package and create a cursor type to put the output parameter & nbsp;
Create or replace package testpkg
Type testcur is ref cursor;
End testpkg;
------ Create a stored procedure and define two parameters. One input parameter is an array of object types (this type should meet most complex requirements) and one output parameter, is to store the query value with a cursor & nbsp;
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 & nbsp;
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;
-------------- Execute the Stored Procedure

Declare
Ta tp_arr_tbl3: = tp_arr_tbl3 (); -- Object Declaration & nbsp;
T tp_arr3: = tp_arr3 ('0', '0', '0'); -- Declaration and Initial Value assignment (required steps)
Begin
For I in 1 .. 12 loop
Ta. extend;
T. userid: = '1 ';
T. topicid: = I;
T. record: = '0 ';
Ta (I): = t;
FindRecord (ta );
End loop;
End findRecord;

---------------------- Test part of Table query
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 and can be skipped.
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. the test user initially used Scott and found that the execution permission was not available, but the dba permission was still not granted to the user, and then the system was used; 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 a stored procedure, you must first declare and assign the initial value. Otherwise, an error will be reported. 6. note that when the stored procedure test value is assigned in pl/SQL. extend cannot be less; 6. there are many precautions for custom types, such as nvarchar2 and JavaString; 7. in short, the tp_arr3 type refers to one record, and the tp_arr_tbl3 type refers to multiple records;

-- 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), -- backup Field
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 ";

// There are a lot of cards on the Internet that are stuck in obtaining con. I also initially said that it was a jar package problem, deleted class12, And what oracle and apache connection pool conflicts, however, I did not write the correct stored procedure. I finally came back from the circle and used this method to pass the test. (org. apache. commons. dbcp. poolableConnection) conn ). getInnermostDelegate ()
Connection con = DriverManager. getConnection (url, "system", "");

// 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 uses the object array as object type like mine, java calls this step essential, which is a ing of the tp_arr3 type declared in pl/SQL, indicates matching your custom type in pl/SQL
// Note that uppercase letters are required. Otherwise, "invalid name mode" may be reported"
StructDescriptor recDesc = StructDescriptor. 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. In any case, only one rule is provided. In the end, everyone will follow this rule to convert it.
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, and 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 oracle. SQL. ARRAY (desc, con, pstruct. toArray ());

// You may also need to add the package name during the call.
Cs = con. prepareCall ("{call findRecord (?,?)} ");

// Set the parameters here. values 1 and 2 correspond to the stored procedure findRecord (?,?) The location of the parameter in. Note that the location is correct.
Cs. setArray (1, array );
Cs. registerOutParameter (2, OracleTypes. CURSOR );
Cs.exe cute ();
Rs = (ResultSet) cs. getObject (2); // The data is retrieved based on the corresponding parameter location.

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 = 809894604691091308l;
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;
}

@ Override
Public 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 ".