ibatis 调用存储过程返回游标sys_refcursor

Ibatis 存储过程 游标 sys_refcursor oracle 10g

[img]D:\ib.bmp[/img]

ibTest.sql
[code]
create table user_account
(
userid number not null ,
username varchar2(20) not null,
password varchar2(20) not null,
grouname varchar2(20) ,
primary key (userid)
);
insert into user_account values(1,'qq','qq','qqq');
insert into user_account values(2,'aa','aa','bb');
commit;
select * from user_account;

create or replace procedure p_getuser
(
cur_user out sys_refcursor
)is
begin
open cur_user for
select userid,username,password,grouname from user_account;
end;
[/code]

sqlMapConfig.xml
[code]
<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMapConfig
PUBLIC "-//ibatis.apache.org//DTD SQL Map Config 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-config-2.dtd">

<sqlMapConfig>

<properties resource="conf/SqlMap.properties"/>
<transactionManager type="JDBC" commitRequired="false">
<dataSource type="SIMPLE">
<property name="JDBC.Driver" value="${driver}"/>
<property name="JDBC.ConnectionURL" value="${url}"/>
<property name="JDBC.Username" value="${username}"/>
<property name="JDBC.Password" value="${password}"/>
</dataSource>
</transactionManager>

<sqlMap resource="com/ib/model/Account.xml"/>

</sqlMapConfig>

[/code]
SqlMap.properties

[b]driver=oracle.jdbc.driver.OracleDriver[/b]
[b]url=jdbc:oracle:thin:@localhost:1521:orcl[/b]
[b]username=scott[/b]
[b]password=tiger[/b]

Account.java
[code]
package com.ib.model;

public class Account {

private int userid;
private String username;
private String password;
private String grouname;

public int getUserid() {
return userid;
}

public void setUserid(int userid) {
this.userid = userid;
}

public String getUsername() {
return username;
}

public void setUsername(String username) {
this.username = username;
}

public String getPassword() {
return password;
}

public void setPassword(String password) {
this.password = password;
}

public String getGrouname() {
return grouname;
}

public void setGrouname(String grouname) {
this.grouname = grouname;
}

}
[/code]
Account.xml
[code]
<?xml version="1.0" encoding="UTF-8" ?>

<!DOCTYPE sqlMap
PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
"http://ibatis.apache.org/dtd/sql-map-2.dtd">

<sqlMap namespace="Account">

<select id="getAllUsers" parameterClass="string"
resultClass="hashmap">
select * from USER_ACCOUNT where grouname = #grouname#
</select>

<resultMap id="cur-map" class="com.ib.model.Account">
<result property="userid" column="userid" />
<result property="username" column="username" />
<result property="password" column="password" />
<result property="grouname" column="grouname" />
</resultMap>

<parameterMap id="p_getuser_map" class="java.util.HashMap">
<parameter property="result" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" resultMap="cur-map" />

</parameterMap>

<procedure id="p_getuser" parameterMap="p_getuser_map">
{call p_getuser(?)}
</procedure>

</sqlMap>
[/code]
Main.java
[code]
package com.ib;

import java.io.IOException;
import java.io.Reader;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.ib.model.Account;
import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;

public class Main {

public static void main(String[] args) {
Reader reader = null;
SqlMapClient sqlMapper = null;
try {
reader = Resources.getResourceAsReader("conf/SqlMapConfig.xml");
sqlMapper = SqlMapClientBuilder.buildSqlMapClient(reader);
List list = sqlMapper.queryForList("getAllUsers", "qqq");
System.out.println(list);

Map m = new HashMap();

sqlMapper.queryForList("p_getuser", m);

//m.get("cur_user");
List<Account> al = (List<Account>) (m.get("result"));
for (Account a : al) {
System.out.println(a.getUserid() + " " + a.getUsername() + " "
+ a.getPassword() + " " + a.getGrouname());
}

reader.close();
} catch (IOException ie) {
// Fail fast.
ie.printStackTrace();
} catch (SQLException se) {
// TODO Auto-generated catch block
se.printStackTrace();
}
}

}
[/code]
可能出现的异常：
严重: SimpleDataSource: Error while loading properties. Cause: java.lang.ClassNotFoundException: oracle.jdbc.driver.OracleDriver
（去掉属性文件中每一行的最后的空格）

End