Question

ibatis 怎么返回oracle游标

Answer 1

问题总结：

1.index by表不能存储在数据库中的type中,故选择嵌套表。

2.ibatis不支持oracle的复合数据类型的返回。（个人理解）

3.替代方案：用返回oracle游标来代替复合数据类型。ibatis能接受oracle游标类型。

注意此处是ibatis2.3

部分代码：

1.java

复制代码
1 private Map<String,Object> userStateResult(Users users)throws Exception{
2 Map<String,Object> param = new HashMap<String,Object>();
3 param.put("PRM_USERID", users.getUserid().toString());
4 param.put("PRM_OBJECTS", null);
5 param.put("PRM_TAGS", null);
6 param.put("PRM_APPCODE", null);
7 param.put("PRM_ERRMSG", null);
8 getDao().queryForList("user.prc_user_index",param);
9 if(Constant.SUCCESS.equals(param.get("PRM_APPCODE"))){
10 return param;
11 }else{
12 return null;
13 }
14 }
复制代码
返回值（包括游标的返回值）都在param这个map中

2.ibatis代码：

复制代码
1 <parameterMap class="java.util.Map" id="UserIndexParam">
2 <parameter property="PRM_USERID" javaType="java.lang.String"
3 jdbcType="VARCHAR" mode="IN" />
4 <parameter property="PRM_OBJECTS" javaType="java.sql.ResultSet"
5 jdbcType="ORACLECURSOR" mode="OUT" resultMap="ref_object" />
6 <parameter property="PRM_TAGS" javaType="java.sql.ResultSet"
7 jdbcType="ORACLECURSOR" mode="OUT" resultMap="ref_tag" />
8 <parameter property="PRM_APPCODE" javaType="java.lang.String"
9 jdbcType="VARCHAR" mode="OUT" />
10 <parameter property="PRM_ERRMSG" javaType="java.lang.String"
11 jdbcType="VARCHAR" mode="OUT" />
12 </parameterMap>
13 ---------------------------------------------------------------------------------
14 <resultMap id="ref_tag" class="com.diy.tag.entity.Tag">
15 <result column="tagid" jdbcType="VARCHAR" property="tagid" />
16 <result column="tagname" jdbcType="VARCHAR" property="name" />
17 </resultMap>
18
19 <resultMap class="com.diy.comm.cursorHandler.ObjectHandler" id="ref_object">
20 <result column="OBJECTID" jdbcType="DECIMAL" property="objectid" />
21 <result column="OWNERID" jdbcType="DECIMAL" property="ownerid" />
22 <result column="DBUSID" jdbcType="DECIMAL" property="dbusid" />
23 <result column="DUSERSID" jdbcType="DECIMAL" property="dusersid" />
24 <result column="TAGID" jdbcType="VARCHAR" property="tagid" />
25 <result column="USERNAME" jdbcType="VARCHAR" property="username" />
26 <result column="OBJNAME" jdbcType="VARCHAR" property="objname" />
27 <result column="LOVENUM" jdbcType="DECIMAL" property="lovenum" />
28 <result column="INRUDUCTION" jdbcType="VARCHAR" property="inruduction" />
29 <result column="CATAGROY" jdbcType="DECIMAL" property="catagroy" />
30 <result column="IMAGEPATH" jdbcType="VARCHAR" property="imagepath" />
31 </resultMap>
32 ---------------------------------------------------------------------------
33 <procedure id="prc_user_index" parameterMap="UserIndexParam">
34 {call
35 PKG_USER.PRC_USER_INDEXVIEW(?,?,?,?,?)}
36 </procedure>
复制代码
有一篇文章写的很好：大家可以参考一下http://blog.sina.com.cn/s/blog_80c111410100vgsh.html

但是对于本问题没有用ibatis的TypeHandler。

因为存储过程调试可以返回游标数据，但是ibatis接受的到全部是null。不知道原因，有知道的朋友可以留言一下。

我个人猜测可能是ibatis版本问题。

3.存储过程代码（部分）：

复制代码
--对象类型
CREATE OR REPLACE TYPE TAGS_INFO IS object
(
TAGID number,
TAGNAME varchar2(200)
)
--嵌套表
CREATE OR REPLACE TYPE table_tag IS TABLE OF TAGS_INFO
注意对象和嵌套表都要放在全局的。不能定义在包体中
--兴趣游标
TYPE TAGCURSOR IS REF CURSOR;
--东西游标
TYPE OBJECTCURSOR IS REF CURSOR;--这个定义在包体中
------------------------------------------------------------------------
PROCEDURE PRC_USER_INDEXVIEW(PRM_USERID IN VARCHAR2,
PRM_OBJECTS OUT OBJECTCURSOR,
PRM_TAGS OUT TAGCURSOR,
PRM_APPCODE OUT VARCHAR2,
PRM_ERRMSG OUT VARCHAR2) IS
N_FLAG NUMBER;
VAR_FIRSTTAG VARCHAR2(100);
VAR_DUSERID VARCHAR2(100);
INDEX_TAGS TABLE_TAG;

--用户兴趣标签
CURSOR CUR_USERTAG IS
SELECT C.TAGID, C.NAME
FROM USERSDETIAL A, TAGRELATION B, TAG C
WHERE A.DUSERSID = B.DUSERSID
AND B.TAGID = C.TAGID
AND A.DUSERSID = VAR_DUSERID;
--公共兴趣标签
CURSOR CUR_USERPUB IS
SELECT T.*
FROM (SELECT ROWNUM AS RNUM,
COUNT(A.DUSERSID) AS CNUM,
B.TAGID,
B.NAME
FROM TAGRELATION A, TAG B
WHERE A.TAGID = B.TAGID
GROUP BY A.DUSERSID, B.TAGID, B.NAME, ROWNUM) T
WHERE RNUM <= 8
ORDER BY T.CNUM DESC;
--object
/*CURSOR CUR_OBJ(VAR_TAGID VARCHAR2) IS
SELECT ROWNUM AS RN, A.*
FROM OBJECT A
WHERE trim(A.TAGID) = VAR_TAGID
AND ROWNUM < 30;*/

REC_USERTAG CUR_USERTAG%ROWTYPE;
REC_USERPUB CUR_USERPUB%ROWTYPE;
--REC_OBJ OBJECT%ROWTYPE;
BEGIN
PRM_APPCODE := PKG_COMM.DEF_OK;
PRM_ERRMSG := '';

IF PRM_USERID IS NULL THEN
PRM_APPCODE := PKG_COMM.DEF_ERR;
PRM_ERRMSG := '参数未定义';
RETURN;
END IF;
--用户详细ID是否存在
SELECT B.DUSERSID
INTO VAR_DUSERID
FROM USERS A, USERSDETIAL B
WHERE A.USERID = B.USERSID
AND A.USERID = PRM_USERID;
IF VAR_DUSERID IS NULL THEN
PRM_APPCODE := PKG_COMM.DEF_ERR;
PRM_ERRMSG := '参数无效';
RETURN;
END IF;
--1.判断是否为有效用户
SELECT NVL(A.FLAG, 1)
INTO N_FLAG
FROM USERS A, USERSDETIAL B
WHERE A.USERID = B.USERSID
AND B.DUSERSID = VAR_DUSERID;

IF N_FLAG = 1 THEN
PRM_APPCODE := PKG_COMM.DEF_ERR;
PRM_ERRMSG := '用户已被禁止登录';
RETURN;
END IF;

--2.判断用户是否有兴趣tag

FOR REC_USERTAG IN CUR_USERTAG LOOP
INDEX_TAGS := TABLE_TAG();
IF CUR_USERTAG%ROWCOUNT = 0 THEN
--获取公共兴趣游标
FOR REC_USERPUB IN CUR_USERPUB LOOP
INDEX_TAGS.EXTEND;
IF CUR_USERPUB%ROWCOUNT = 1 THEN
VAR_FIRSTTAG := REC_USERPUB.TAGID;
END IF;
INDEX_TAGS(CUR_USERPUB%ROWCOUNT) := TAGS_INFO(REC_USERPUB.TAGID,
REC_USERPUB.NAME);
END LOOP;
ELSIF CUR_USERTAG%ROWCOUNT = 1 THEN
VAR_FIRSTTAG := REC_USERTAG.TAGID;
END IF;
INDEX_TAGS.EXTEND;
INDEX_TAGS(CUR_USERTAG%ROWCOUNT) := TAGS_INFO(REC_USERTAG.TAGID,
REC_USERTAG.NAME);
--index_tags(CUR_USERTAG%ROWCOUNT).TAGNAME := REC_USERTAG.NAME;

END LOOP;

IF INDEX_TAGS.COUNT <> 0 THEN
/* --3. 取出object
FOR REC_OBJ IN CUR_OBJ(VAR_FIRSTTAG) LOOP
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).OWNERID := REC_OBJ.OWNERID;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).OBJECTID := REC_OBJ.OBJECTID;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).DBUSID := REC_OBJ.DBUSID;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).DUSERSID := REC_OBJ.DUSERSID;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).TAGID := REC_OBJ.TAGID;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).LOVENUM := REC_OBJ.LOVENUM;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).INRUDUCTION := REC_OBJ.INRUDUCTION;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).CATAGROY := REC_OBJ.CATAGROY;
PRM_OBJECTS(CUR_OBJ%ROWCOUNT).Imagepath := REC_OBJ.Imagepath;

END LOOP;*/
--返回东西游标
OPEN PRM_OBJECTS FOR
SELECT ROWNUM AS RN, A.*, B.USERNAME
FROM OBJECT A, USERSDETIAL B
WHERE A.OWNERID = B.DUSERSID
AND TRIM(A.TAGID) = VAR_FIRSTTAG
AND ROWNUM < 30;

END IF;
--tag游标
OPEN PRM_TAGS FOR
SELECT * FROM TABLE(CAST(INDEX_TAGS AS TABLE_TAG));

EXCEPTION
WHEN OTHERS THEN
PRM_APPCODE := PKG_COMM.DEF_ERR;
PRM_ERRMSG := '获取主界面数据失败' || '错误原因：' || PRM_ERRMSG || '-' || SQLERRM ||
'错误行数：' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE();
END;
---注意：a.返回游标的用open for 方法，不用关心游标的关闭。它是自动关闭的。原因：调试把游标返回值点几下，你就回发现。
b.如果报错CURSOR IS CLOESD的话，说明游标里面没有数据。所以open for 必须保证有select中有数据
c.嵌套表这里要用类似与java的构造方法写，如上
如果写成类似于java中new对象后，用set方法给嵌套表赋值的，会报错未能未能初始化的结果集。

Answer 2

ibatis中返回oracle游标的写法：
sqlMap.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="KOMUNIKA_REPORT">
<resultMap id="BaseResultMap" class="javaapplication4.StockAreaAndWarehouse" >
<result column="PRODUCT_CODE" property="productCode" />
<result column="PRODUCT_NAME" property="productName" />
<result column="INCOMING" property="incoming" />
<result column="UNIT_SOLD" property="unitSold" />
<result column="TOTAL_STOCK" property="totalStock" />
</resultMap>
<parameterMap id="resultMap" class="java.util.Map">
<parameter property="result" javaType="java.sql.ResultSet" jdbcType="ORACLECURSOR" mode="OUT"/>
</parameterMap>
<procedure id="selectStockAreaAndWarehouse"
parameterMap="resultMap"
resultMap="BaseResultMap"
>
{ call KOMUNIKA.LP_STOCK_AREA_WAREHOUSE(?) }
</procedure>
</sqlMap>

应用中的调用：
package javaapplication4;

import com.ibatis.common.resources.Resources;
import com.ibatis.sqlmap.client.SqlMapClient;
import com.ibatis.sqlmap.client.SqlMapClientBuilder;
import java.io.Reader;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
*
* @author ifnu<mailto:ifnubima@gmail.com>
*/
public class Main {
public static void main(String[] args) throws Exception {
String resource;
Reader reader;
List list;
SqlMapClient sqlMap;
resource = "javaapplication4/ibatis.xml";
reader = Resources.getResourceAsReader (resource);
sqlMap = SqlMapClientBuilder.buildSqlMapClient(reader);
Map map = new HashMap();
// use queryForList because the procedure map defines a resultmap
// for the statement
list = sqlMap.queryForList("KOMUNIKA_REPORT.selectStockAreaAndWarehouse", map);
System.out.println(list);
}
}

Answer 3

ibatis和Mybatis对存储过程和函数函数的调用的配置Xml是不一样的，以下是针对Mybatis 3.2的环境进行操作的。
第一步配置Mapper的xml内容

<mapper namespace="com.rrtong.rrt.auto.dao.SelfStatisticDataDao">
<resultMap id="SelfStatisticData" type="SelfStatisticData">
<id column="name" property="name" />
<result column="count" property="count"/>
</resultMap>

<select id="getSelfStatisticData" parameterType="HashMap" statementType="CALLABLE" >
{#{result,mode=OUT,jdbcType=CURSOR, resultMap=SelfStatisticData} = call PKG_RRT_SelfStatics.Fn_GetSelfStatData(#{userCode,jdbcType=VARCHAR,mode=IN}) }
</select>
</mapper>
说明：1、result对应的是oracle自定义函数返回的游标； 2、映射对象resultMap对应的是SelfStaticData； 3、userCode为传入参数

JAVA中如何得到该游标的数据集呢？下面为调用实例，

@Service
public class SelfStatisticDataServiceImpl implements SelfStatisticDataService{
@Resource
SelfStatisticDataCache selfStatisticDataCache;

public List<?> getSelfStatisticData(String userCode){
List<?> selfStaticDataList = new ArrayList<Map<String,Object>>();
HashMap<String,Object> statMap = new HashMap<String,Object>();
statMap.put("userCode", userCode);
/*设定游标结果写入的变量*/
statMap.put("result", selfStaticDataList);

selfStatisticDataCache.getSelfStatisticData(statMap);
/*获取返回的游标结果集*/
selfStaticDataList = (List<?>) statMap.get("result");

return selfStaticDataList;
}
}