使用Jorm处理Oracle存储过程返回多个游标

> 引言
日常开发中，使用Oracle存储过程，有时候会处理返回多个游标的情况，下面介绍使用 Jorm 框架来处理这一情况

> 数据库准备
1.表

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.存储过程

-- 定义存储过程
CREATE OR REPLACE PROCEDURE pro_query_users
(
-- 参数IN表示输入参数，OUT表示输入参数，类型可以使用任意Oracle中的合法类型。
in_id IN NUMBER ,
out_cursor_one OUT package_one.cursor_one,
out_cursor_two OUT package_two.cursor_two
)
AS
-- 定义变量
vs_id_value NUMBER ; -- 变量

BEGIN
-- 用输入参数给变量赋初值。
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% ' ;

-- 错误处理部分。OTHERS表示除了声明外的任意错误。SQLERRM是系统内置变量保存了当前错误的详细信息。
Exception
WHEN OTHERS Then
ROLLBACK ;
Return ;
End pro_query_users;

> 代码

1.实体类 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.测试

 

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:[e-mail]">Gerald Chen</a>
* @version $Id: ProcedureTest.java 2011-8-25 下午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 ? " 男 " : " 女 " );
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.execute();
ResultSet rsOne = (ResultSet) cs.getObject( 2 ); // 返回第一个游标
ResultSet rsTwo = (ResultSet) cs.getObject( 3 ); // 返回第二个游标
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);
}
}