Using Jorm to process Oracle stored procedures, multiple cursors are returned

> Introduction
In daily development, Oracle stored procedures are used to handle the case that multiple cursors are returned. The following describes how to use the Jorm framework to handle this situation.

> Database preparation
1. Table
Create table 't_ user '(
'Id' int (11) not null,
'Name' varchar (50) default null,
'Sex 'char (4) default null,
'Age' int (11) default null,
'Career' varchar (100) default null,
Primary key ('id ')
) ENGINE = InnoDB default charset = utf8;
2. Stored Procedure
-- Define a stored procedure
Create or replace procedure pro_query_users
(
-- The IN parameter indicates the input parameter, and the OUT parameter indicates the input parameter. The type can be of any legal type IN Oracle.
In_id in number,
Out_cursor_one OUT package_one.cursor_one,
Out_cursor_two OUT package_two.cursor_two
)
AS
-- Define variables
Vs_id_value NUMBER; -- Variable
 
BEGIN
-- Use input parameters to assign initial values to variables.
Vs_id_value: = in_id;

OPEN out_cursor_one for select * FROM t_user WHERE id> vs_id_value;
 
OPEN out_cursor_two for select * FROM t_user WHERE name LIKE '% a % ';
 
-- Error handling section. OTHERS indicates any errors except declarations. SQLERRM is a built-in variable that saves detailed information about the current error.
Exception
When others Then
ROLLBACK;
Return;
End pro_query_users;

> Code
1. entity class User. java

Import org. javaclub. jorm. annotation. Entity;
Import org. javaclub. jorm. annotation. Id;
Import org. javaclub. jorm. annotation. NoColumn;
Import org. javaclub. jorm. annotation. PK;

@ Entity (table = "t_user", lazy = true)
@ PK (value = "id ")
Public class User {

@ Id
Private int id;

Private String name;

Private String sex;

Private Integer age;

Private String career;

@ NoColumn
Private int kvalue;

Public User (){
Super ();
}

Public User (String name, String sex, Integer age, String [] career ){
Super ();
This. name = name;
This. sex = sex;
This. age = age;
This. career = career;
}

Public int getId (){
Return id;
}

Public void setId (int id ){
This. id = id;
}

Public String getName (){
Return name;
}

Public void setName (String name ){
This. name = name;
}

Public String getSex (){
Return sex;
}

Public void setSex (String sex ){
This. sex = sex;
}

Public Integer getAge (){
Return age;
}

Public void setAge (Integer age ){
This. age = age;
}

Public String [] getCareer (){
Return career;
}

Public void setCareer (String [] career ){
This. career = career;
}

Public int getKvalue (){
Return kvalue;
}

Public void setKvalue (int kvalue ){
This. kvalue = kvalue;
}

Public String toString (){
StringBuffer sb = new StringBuffer ();
Sb. append ("[" + id + "," + name + "," + sex + "," + age + "," + career + "]");
Return sb. toString ();
}
}

2. Test

Import java. SQL. CallableStatement;
Import java. SQL. ResultSet;
Import java. SQL. SQLException;
Import java. SQL. Types;
Import java. util. ArrayList;
Import java. util. List;

Import org. javaclub. jorm. Jorm;
Import org. javaclub. jorm. Session;
Import org. javaclub. jorm. common. Numbers;
Import org. javaclub. jorm. common. Strings;
Import org. javaclub. jorm. demos. entity. User;
Import org. javaclub. jorm. jdbc. callable. ProcedureCaller;
Import org. javaclub. jorm. jdbc. SQL. SqlParams;
Import org. junit. AfterClass;
Import org. junit. Assert;
Import org. junit. BeforeClass;
Import org. junit. Test;

/**
* ProcedureTest
*
* @ Author <a href = "mailto: gerald.chen.hz@gmail.com"> Gerald Chen </a>
* @ Version $ Id: ProcedureTest. java 06:18:17 Exp $
*/
Public class ProcedureTest {

Static Session session;

@ BeforeClass
Public static void setUpBeforeClass () throws Exception {
Session = Jorm. getSession ();
}

@ AfterClass
Public static void destroyAfterClass (){
Jorm. free ();
}

@ Test
Public void save_user (){
Session. clean (User. class );
User user = null;
For (int I = 0; I <1600; I ++ ){
String sex = (I % 2 = 0? "Male": "female ");
User = new User (Strings. fixed (5), sex, Numbers. random (98), Strings. random (8 ));
Session. save (user );
}
}

@ Test
Public void oracle_load_two_cursor (){

Save_user ();

Final String pro = "{call pro_query_users (?, ?, ?)} ";
Final List <User> gtIdUsers = new ArrayList <User> ();
Final List <User> likeNameUsers = new ArrayList <User> ();

Session. call (new ProcedureCaller (){

Public CallableStatement prepare () throws SQLException {
CallableStatement cs = this. getSession (). getConnection (). prepareCall (pro );
Cs. setInt (1, 20 );
Cs. registerOutParameter (2, oracle. jdbc. OracleTypes. CURSOR );
Cs. registerOutParameter (3, oracle. jdbc. OracleTypes. CURSOR );
Return cs;
}

Public String callback (CallableStatement cs) throws SQLException {
Cs.exe cute ();
ResultSet rsOne = (ResultSet) cs. getObject (2); // The first cursor is returned.
ResultSet rsTwo = (ResultSet) cs. getObject (3); // returns the second cursor
While (rsOne! = Null & rsOne. next ()){
GtIdUsers. add (session. getPersister (). toBean (rsOne, User. class ));
}
While (rsTwo! = Null & rsTwo. next ()){
LikeNameUsers. add (session. getPersister (). toBean (rsTwo, User. class ));
}
Return null;
}
});

Assert. assertTrue (gtIdUsers. size ()> 0 );
System. out. println (gtIdUsers. size () + "=>" + gtIdUsers );

Assert. assertTrue (likeNameUsers. size ()> 0 );
System. out. println (likeNameUsers. size () + "=>" + likeNameUsers );
}
}

The author is "Wind, wind, and Yi"