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

原文：http://www.myexception.cn/software-architecture-design/613196.html

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

Ibatis 存储过程  游标 sys_refcursor oracle 10g



ibTest.sql

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;

sqlMapConfig.xml     

<?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>

SqlMap.properties

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

Account.java

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;
	}

}

Account.xml

<?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>

Main.java

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();
		}
	}

}

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

End