oracle 存储过程 返回复合数据类型（index by表） ibatis如何接受 oracle的复合数据类型
0
悬赏园豆：140
[待解决问题]
最近有个问题一直困扰着，希望各位能给予帮助。
先贴代码：
1 <parameterMap class="map" id="UserIndexParam"> 2 <parameter property="PRM_USERID" javaType="java.lang.Object" 3 jdbcType="Object" mode="IN" /> 4 <parameter property="PRM_OBJECTS" javaType="java.sql.ResultSet" 5 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.object.entity.ObjectTypeHandler" /> 6 <parameter property="PRM_TAGS" javaType="java.sql.ResultSet" 7 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.tag.entity.TagsTypeHandler" /> 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 <procedure id="prc_user_index" parameterMap="UserIndexParam"> 15 {call 16 PKG_USER.PRC_USER_INDEXVIEW(?,?,?,?,?)} 17 </procedure>
这个ibatis的配置文件.
在网上查了很久，说是用typeHandler和jdbcType来解决解决oracle复合类型，
1 package com.diy.tag.entity; 2 3 import java.sql.CallableStatement; 4 import java.sql.PreparedStatement; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.util.ArrayList; 8 import java.util.List; 9 10 import oracle.sql.Datum; 11 import oracle.sql.STRUCT; 12 13 import com.ibatis.sqlmap.engine.type.TypeHandler; 14 15 public class TagsTypeHandler implements TypeHandler { 16 17 /** 18 * @Description: 这个方法重点 19 * @param cs 20 * @param arg1 21 * @throws SQLException 22 */ 23 public java.lang.Object getResult(CallableStatement cs, int arg1) 24 throws SQLException { 25 List<Tag> list = new ArrayList<Tag>(); 26 ResultSet rs = cs.getArray(arg1).getResultSet(); 27 while (rs.next()) { 28 Datum[] data = ((STRUCT)rs.getObject(2)).getOracleAttributes(); 29 Tag tag = new Tag(); 30 if (data[0] != null) { 31 tag.setTagid(new Long(data[0].getBytes().toString())); 32 } 33 list.add(tag); 34 } 35 return list; 36 } 37 38 @Override 39 public boolean equals(java.lang.Object arg0, String arg1) { 40 // TODO Auto-generated method stub 41 return false; 42 } 43 44 @Override 45 public java.lang.Object getResult(ResultSet arg0, String arg1) 46 throws SQLException { 47 // TODO Auto-generated method stub 48 return null; 49 } 50 51 @Override 52 public java.lang.Object getResult(ResultSet arg0, int arg1) 53 throws SQLException { 54 // TODO Auto-generated method stub 55 return null; 56 } 57 58 59 60 @Override 61 public void setParameter(PreparedStatement arg0, int arg1, 62 java.lang.Object arg2, String arg3) throws SQLException { 63 // TODO Auto-generated method stub 64 65 } 66 67 @Override 68 public java.lang.Object valueOf(String arg0) { 69 // TODO Auto-generated method stub 70 return null; 71 } 72 73 }
这个是java代码，其中一个handler处理类
1 --定义 object表 对象 2 TYPE object_arr IS TABLE OF OBJECT%ROWTYPE INDEX BY BINARY_INTEGER; 3 4 --定义 tag index_by表 5 TYPE table_tag IS TABLE OF TAGS_INFO INDEX BY BINARY_INTEGER;
这个定义的oracle复合类型
1 PROCEDURE PRC_USER_INDEXVIEW(PRM_USERID IN VARCHAR2, 2 PRM_OBJECTS OUT PKG_COMM.OBJECT_ARR, 3 PRM_TAGS OUT PKG_COMM.table_tag, 4 PRM_APPCODE OUT VARCHAR2, 5 PRM_ERRMSG OUT VARCHAR2) IS 6 N_FLAG NUMBER; 7 VAR_FIRSTTAG VARCHAR2(100); 8 VAR_DUSERID VARCHAR2(100); 9 --用户兴趣标签 10 CURSOR CUR_USERTAG IS 11 SELECT C.TAGID, C.NAME 12 FROM USERSDETIAL A, TAGRELATION B, TAG C 13 WHERE A.DUSERSID = B.DUSERSID 14 AND B.TAGID = C.TAGID 15 AND A.DUSERSID = VAR_DUSERID; 16 --公共兴趣标签 17 CURSOR CUR_USERPUB IS 18 SELECT T.* 19 FROM (SELECT ROWNUM AS RNUM, 20 COUNT(A.DUSERSID) AS CNUM, 21 B.TAGID, 22 B.NAME 23 FROM TAGRELATION A, TAG B 24 WHERE A.TAGID = B.TAGID 25 GROUP BY A.TAGID) T 26 WHERE RNUM <= 8 27 ORDER BY T.CNUM DESC; 28 --object 29 CURSOR CUR_OBJ(VAR_TAGID VARCHAR2) IS 30 SELECT ROWNUM AS RN, A.* 31 FROM OBJECT A 32 WHERE trim(A.TAGID) = VAR_TAGID 33 AND ROWNUM < 30; 34 35 REC_USERTAG CUR_USERTAG%ROWTYPE; 36 REC_USERPUB CUR_USERPUB%ROWTYPE; 37 REC_OBJ OBJECT%ROWTYPE; 38 BEGIN 39 PRM_APPCODE := PKG_COMM.DEF_OK; 40 PRM_ERRMSG := ''; 41 42 IF PRM_USERID IS NULL THEN 43 PRM_APPCODE := PKG_COMM.DEF_ERR; 44 PRM_ERRMSG := '参数未定义'; 45 RETURN; 46 END IF; 47 --用户详细ID是否存在 48 SELECT B.DUSERSID 49 INTO VAR_DUSERID 50 FROM USERS A, USERSDETIAL B 51 WHERE A.USERID = B.USERSID 52 AND A.USERID = PRM_USERID; 53 IF VAR_DUSERID IS NULL THEN 54 PRM_APPCODE := PKG_COMM.DEF_ERR; 55 PRM_ERRMSG := '参数无效'; 56 RETURN; 57 END IF; 58 --1.判断是否为有效用户 59 SELECT NVL(A.FLAG, 1) 60 INTO N_FLAG 61 FROM USERS A, USERSDETIAL B 62 WHERE A.Userid = B.USERSID 63 AND B.DUSERSID = VAR_DUSERID; 64 65 IF N_FLAG = 1 THEN 66 PRM_APPCODE := PKG_COMM.DEF_ERR; 67 PRM_ERRMSG := '用户已被禁止登录'; 68 RETURN; 69 END IF; 70 71 --2.判断用户是否有兴趣tag 72 73 FOR REC_USERTAG IN CUR_USERTAG LOOP 74 75 IF CUR_USERTAG%ROWCOUNT = 0 THEN 76 --获取公共兴趣游标 77 FOR REC_USERPUB IN CUR_USERPUB LOOP 78 IF CUR_USERPUB%ROWCOUNT = 1 THEN 79 VAR_FIRSTTAG := REC_USERPUB.TAGID; 80 END IF; 81 PRM_TAGS(CUR_USERPUB%ROWCOUNT).TAGID := REC_USERPUB.TAGID; 82 PRM_TAGS(CUR_USERPUB%ROWCOUNT).TAGNAME := REC_USERPUB.NAME; 83 END LOOP; 84 ELSIF CUR_USERTAG%ROWCOUNT = 1 THEN 85 VAR_FIRSTTAG := REC_USERTAG.TAGID; 86 END IF; 87 PRM_TAGS(CUR_USERTAG%ROWCOUNT).TAGID := REC_USERTAG.TAGID; 88 PRM_TAGS(CUR_USERTAG%ROWCOUNT).TAGNAME := REC_USERTAG.NAME; 89 END LOOP; 90 91 IF PRM_TAGS.count <> 0 THEN 92 --3. 取出object 93 FOR REC_OBJ IN CUR_OBJ(VAR_FIRSTTAG) LOOP 94 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).OWNERID := REC_OBJ.OWNERID; 95 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).OBJECTID := REC_OBJ.OBJECTID; 96 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).DBUSID := REC_OBJ.DBUSID; 97 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).DUSERSID := REC_OBJ.DUSERSID; 98 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).TAGID := REC_OBJ.TAGID; 99 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).LOVENUM := REC_OBJ.LOVENUM; 100 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).INRUDUCTION := REC_OBJ.INRUDUCTION; 101 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).CATAGROY := REC_OBJ.CATAGROY; 102 PRM_OBJECTS(CUR_OBJ%ROWCOUNT).Imagepath := REC_OBJ.Imagepath; 103 104 END LOOP; 105 END IF; 106 107 EXCEPTION 108 WHEN OTHERS THEN 109 PRM_APPCODE := PKG_COMM.DEF_ERR; 110 PRM_ERRMSG := '获取主界面数据失败' || '错误原因：' || PRM_ERRMSG || '-' || SQLERRM || 111 '错误行数：' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE(); 112 END;
这个是存储过程的实现,过程没有问题，plsql调试正常！！！
现在我可以确定问题在 1.ibatis xml文件中jdbcType 和typeHandler的配置问题
1 <parameter property="PRM_OBJECTS" javaType="java.sql.ResultSet" 2 5 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.object.entity.ObjectTypeHandler" /> 3 6 <parameter property="PRM_TAGS" javaType="java.sql.ResultSet" 4 7 jdbcType="ARRAY" mode="OUT" typeHandler="com.diy.tag.entity.TagsTypeHandler" />
2.java typeHandler类的返回值
还有一个附带的问题：index by 表不能在数据库中存储，而嵌套表可以存储在数据库中。是不是说index by 表 像java 方法中声明的变量，方法结束，其变量的生命周期就结束了？？？
谢谢！！！
所有回答(1)
0
自己解决问题了
问题总结：
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方法给嵌套表赋值的，会报错未能未能初始化的结果集。
注意点基本上是我在编写代码过程中遇到的问题。希望对大家有帮助。
转载请注明出处，不费我写了这么长时间。
另外在搜索问题的过程中发现：
一个问题，总是相同的解决方法，相同的代码。或者说资料很少。
希望大家也把遇到问题的解决方法都贴出来。
更希望大家把印象笔记，有道笔记里面的自己收藏，总结的好东西都能贴出来。
程序站在巨人肩膀上，编程经验技巧更要分享。才能共同进步。
第一次发帖，也是第一次回帖，第一次结贴。ouyeah!
