IBatis调用ORACLE的存储过程、函数的返回结果集例子
关键字: ibatis调用oracle的存储过程、函数的返回结果集例子/********************************************************************************
包中存储过程及函数 (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");
}
}
}