> 引言日常開發中，使用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:gerald.chen.hz@gmail.com">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);
}
}
posted on 2011-09-23 16:17
jadmin 閱讀(1274)
評論(1) 編輯 收藏