IBatis调用ORACLE的存储过程

原文来自：http://taink.iteye.com/blog/485375
IBatis调用ORACLE的存储过程、函数的返回结果集例子
文章分类:Java编程
/********************************************************************************

包中存储过程及函数 (database Oracle 10G)
********************************************************************************/

----包声明

CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE proc_findResult(u_cursor OUT myrctype, u_id NUMBER);
FUNCTION fun_findResult(u_id NUMBER) return myrctype;
END pkg_test;

----包主体的声明

CREATE OR REPLACE PACKAGE BODY pkg_test
AS
PROCEDURE proc_findResult(u_cursor OUT myrctype ,u_id NUMBER)
IS
sqlString VARCHAR2 (500);
BEGIN
IF u_id = 0 THEN
OPEN u_cursor FOR SELECT userId, userName, password, groupName FROM user_account ORDER BY userId;
ELSE
sqlString :='SELECT userId, userName, password, groupName FROM user_account where userId=:w_id';
OPEN u_cursor FOR sqlString USING u_id;
END IF;
END proc_findResult;

function fun_findResult(u_id NUMBER) RETURN myrctype
IS
u_cursor myrctype;
sqlString VARCHAR2 (500);
BEGIN
IF u_id = 0 THEN
OPEN u_cursor FOR SELECT userId, userName, password, groupName FROM user_account ORDER BY userId;
ELSE
sqlString :='SELECT userId, userName, password, groupName FROM user_account where userId=:w_id';
OPEN u_cursor FOR sqlString USING u_id;
END IF;
return u_cursor;
END fun_findResult;

END pkg_test;
/********************************************************************************

IBatis SQLMap 中的配置
********************************************************************************/

<resultMap id="userAccount" class="org.taink.entity.UserAccount" >
<result property="userId" column="userId" columnIndex="1"/>
<result property="userName" column="userName" columnIndex="2"/>
<result property="password" column="password" columnIndex="3"/>
<result property="groupName" column="groupName" columnIndex="4"/>
</resultMap>
<!-- call procedure parameter
<parameterMap id="parameterMap" class="java.util.HashMap">
<parameter property="u_cursor" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" />
<parameter property="u_id" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/>
</parameterMap>
-->

<!-- call procedure
<procedure id="test_cursor" parameterMap="parameterMap" resultMap="userAccount">
{call pkg_test.proc_findResult(?,?)}
</procedure>
-->


<!-- call function parameter -->
<parameterMap id="parameterMap" class="java.util.HashMap">
<parameter property="u_cursor" jdbcType="ORACLECURSOR"
javaType="java.sql.ResultSet" mode="OUT" />
<parameter property="u_id" jdbcType="NUMBER" javaType="java.lang.Integer" mode="IN"/>
</parameterMap>

<!-- call function -->
<procedure id="test_cursor" parameterMap="parameterMap" resultMap="userAccount">
{? = call pkg_test.fun_findResult(?)}
</procedure>



/********************************************************************************


Dao layer java code


********************************************************************************/



@SuppressWarnings("unchecked")
@Override
public List findAllUserAccountByProcOrFun(Map parameterMap) {
List result = null;
try {
result = super.sqlMapClient.queryForList("test_cursor",parameterMap);
} catch (SQLException e) {
System.out.println("-------------error-------------");
e.printStackTrace();
}
return result;
}



/********************************************************************************


java Test class


********************************************************************************/

public class TestProcOrFun {

public static void main(String[] args) {

IUserAccountDao userAccountDao = new UserAccountDaoImpl();



// find all object by procedure or function operation
Map parameterMap = new HashMap();
parameterMap.put("u_id", 15);
List accountList =
userAccountDao.findAllUserAccountByProcOrFun(parameterMap);
if (accountList.size()>0 ) {
for (Object temp : accountList) {
if (temp != null) {
System.out.println(((UserAccount)temp).toString());
}
}
} else {
System.out.println("result is null");
}

}

}